EXPDP\IMPDP database Data Transmission ___ Database

Use the EXPDP\IMPDP and EXP\IMP commands provided by Oracle to implement export \ Import. The last exported table is incomplete because the Exp\imp command automatically ignores the row_num=0 table. So we chose to use the EXPDP\IMPDP command.

The tasks we want to accomplish:

1, export the. dmp file format from the Airtest user (optionally, the format is. sql, but the SQL table cannot have large characters (Blob,clob,long), and if so, the execution of the SQL file will be an error. ）

2, the exported a.dmp file is dumped into the airtest user, which is equivalent to backup.

3, the exported a.dmp file to the dev user, the equivalent of data transmission

Sqlplus/nolog

Connect/as SYSDBA

To execute a command in Plsql or sqlplus:

Create directory Dump_test as ' F:\dump '

(Create ' F:\dump ' corresponding file on the hard disk ahead of time)

--Check to see if the directory exists

SELECT * from Dba_directories;

--authorize permission to operate the user's dump directory

Grant read, write on directory dump_test to Airtest;

Grant read, write on directory dump_test to Dev;

Exit SQL, execute the following command under CMD

Task 1: Export data using local user airtest to the directory specified in Dump_test. That is, ' F:\dump '

Format: EXPDP local user/user password @oracle SID, directory parameter specify export to local directory dumpfile specify the file name of the export file

EXPDP AIRTEST/AIRTEST@ORCL directory=dump_test dumpfile=a.dmp logfile=a.log

Task 2: Import data into Airtest

IMPDP airtest/airtest@orcl directory=dump_test dumpfile =a.dmp logfile=b.log;

Task 3: Import data into dev

IMPDP dev/dev@orcl directory=dump_test dumpfile=a.dmp logfile=b.log remap_schema=airtest:dev (end without semicolon ...) )

Resources:

http://blog.163.com/shexinyang@126/blog/static/1367393122013612105445369/

Http://www.cnblogs.com/lanzi/archive/2011/01/06/1927731.html

In the actual operation when IMPDP and EXPDP have the following error:

Ora-39002:invalid operation

Ora-39070:unable to open the log file.

Ora-29283:invalid file operation

Ora-06512:at "SYS. Utl_file ", line 536

Ora-29283:invalid file operation

There are usually two reasons why this error occurs:

1. There is no permission for the path where the DMP file or log file is stored.

2. No permissions on the DMP file.

Repeated investigation or did not find out the reason. Alternative: Using Oracle's own directory

SELECT * from Dba_directories;

Select a directory where data_pump_dir,path:/opt/oracle/admin/orcl/dpdump/is selected.

And move shadow files from root/downloads to this directory;

MV SHADOW. dmp/opt/oracle/admin/orcl/dpdump/

Execute under BASH:

IMPDP SHADOW/SHADOW@ORCL Directory=data_pump_dir Dumfile=shadwo. DMP Logfile=2.log Tabale_exists_action=replace;

Tabale_exists_action: When a table repeats, the action is replaced. The default is skip. There is only a substitution operation for the duplicate table, but the sequence also complains when repeated:

You can erase sequence and then IMPDP.

Drop sequence hibernate_sequence;

Add that if you create a new database, import data from scratch, such as creating a new airback, and then executing IMPD, you receive the following error:

Connect to: Oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and real application testing options

ORA-39006: Internal Error

ORA-39068: Invalid primary table data in process_order=-4 row

ORA-01950: No permissions on tablespace ' USERS '

ORA-39097: An unexpected error occurred in the data pump operation-1950

Solution:

Grant unlimited tablespace to Airback;

Reason:

39068 error is caused by the lack of table space share of the chain reaction

Remap_model Insufficient permissions:

Grant Import export permissions to the database grant Exp_full_database

(Grant Imp_full_database to user/grant exp_full_database to user)