Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to import and export data in the database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to import and export data in the database.

Syntax: create or replace directory as' backup path'

Create or replace directory file_path as'C:\ software\ database file'

-- assign permissions to smsdb users:

Grant read,write on directory file_path to smsdb

1.3backing up the database (executed in CMD)

Syntax: expdp user name / password @ database connection identifier directory= path name dumpfile= backup file name logfile= output log name schemas= database user name to back up

Expdp smsdb/123456@oracle11 directory=file_path dumpfile=new.dmp logfile=new.log schemas=smsdb

Question:

ORA-39151: Table "SMSDB". "T_PROVIDER_MOBILE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Analysis:

The default value is skip, but if CONTENT=DATA_ONLY is set, the default value is APPEND, not SKIP.

TABLE_EXISTS_ACTION= [skip | APPEND | TRUNCATE | REPLACE]

SKIP: skip this table and move on to the next object. You cannot use SKIP if the DATA_ONLY parameter is set by CONTENT.

APPEND: the data is loaded into the object, but the existing rows are not affected.

TRUNCATE: delete the existing rows and then load all the data.

REPLACE:drop an existing table, then create and load the data. If DATA_ONLY is set for CONTENT, you cannot use REPLACE

Solution:

Add table_exists_action=truncate after imp

-- Export tabl

Expdp smsdb/123456@oracle11 tables=t_smsgateway_mt_his directory=file_path dumpfile=2017.5.12.dmp

4.5 Database Import

Syntax: Impdp user name / password @ database connection identifier directory= path name dumpfile= backup file name logfile= output log name schemas= database user name to back up

Create path: (delete path: DROP DIRECTORY directory_NAME)

Create directory file_path as'C:\ software\ database file'

Assign permissions to smsdb users:

Grant read,write on directory file_path to smsdb

Data import (under cmd command):

Impdp smsdb/123456@oracle11 directory=file_path dumpfile=new.dmp logfile=new.log schemas=smsdb ignore=y

At this point, I believe you have a deeper understanding of "how to import and export data in the database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.