Oracle EXPDP/IMPDP Common Sample Grooming

EXPDP does not rely on and reference Nls_lang settings, but completely in accordance with the database itself to convert the character set, usually the data is not small when the Exp/imp Guide database has been used, to the large amount of data expdp/impdp see the effect, One exp export of 28G DMP files took 3 hours, using EXPDP for 20 minutes.

1. Create test data

sql> Create tablespace DATA datafile '/u01/app/oracle/oradata/orcl/data.dbf ' size 10m autoextend on next 10m;
Sql> create user ABC identified by ABC default tablespace DATA;
Sql> Grant Connect,resource to ABC;
Sql> Conn ABC/ABC
Sql> CREATE TABLE T as select * from All_objects;
Sql> select * from tab;
Tname Tabtype Clusterid
------------------------------ ------- ----------
T TABLE
Sql>

2. Configure directory directory and user access rights

Sql> Conn/as sysdba;
Sql> Create directory Dump_dir as '/home/oracle ';
Sql> Col owner format A5
sql> Col directory_name format A25;
sql> Col directory_path format A50;
Sql> select * from Dba_directories;

OWNER Directory_name Directory_path
----- ------------------------- --------------------------------------------------
SYS dump_dir/home/oracle
Sql>
Sql> Grant Read,write on the directory Dump_dir to public;
Sql>

3. Export Import full library (1) Full library export

$ EXPDP \ '/as Sysdba\ ' Directory=dump_dir full=y dumpfile=fulldb.dmp parallel=2;

(2) Full library Import

$ IMPDP \ '/as Sysdba\ ' Directory=dump_dir full=y dumpfile=fulldb.dmp parallel=2 table_exists_action=replace;

4. Export Import User (1) Import and export with user

$ EXPDP abc/abc Directory=dump_dir dumpfile=abc.dmp

The import example is as follows: You need to create an ABC user

$ IMPDP abc/abc Directory=dump_dir dumpfile=abc.dmp

(2) Import and export of different users, ABC users under the data into the Tom user

$ EXPDP abc/abc directory=dump_dir dumpfile=abc.dmp logfile=data.log

The import example is as follows: If you do not create a Tom user in advance, IMPDP will automatically create a Tom user, need to use a privileged account, this example is the use of system administrator rights.

$ IMPDP \ '/as Sysdba\ ' Directory=dump_dir dumpfile=abc.dmp remap_schema=abc:tom

Create a way to import Tom's users first

$ IMPDP tom/tom directory=dump_dir dumpfile=abc.dmp remap_schema=abc:tom

(3) Export all the tables of two users at the same time, import them separately, or import them together.

EXPDP \ '/as Sysdba\ ' Directory=dump_dir dumpfile=db_abc_tom.dmp schemas=abc,tom

(4) Export table

EXPDP abc/abc Directory=dump_dir dumpfile=abc_tables.dmp tables=emp,dept

(5) Parallel export, can join speed

EXPDP abc/abc Directory=dump_dir dumpfile=full.dmp parallel=40

5. Cross-Version import issues

A low version is not able to import a high version of DMP and requires a version number to be exported when a high version of EXPDP is exported. Low version IMPDP No need to specify a version.

Example: 11.2.0.4 Import to 10.2.0.5

EXPDP abc/abc Directory=dump_dir dumpfile=abc_tables.dmp version=10.2.0.5.0

This article is from the "Koumm Linux Technology blog" blog, be sure to keep this source http://koumm.blog.51cto.com/703525/1574472

Oracle EXPDP/IMPDP Common Sample Grooming