Oracle EXPDP/IMPDP Common Sample Grooming

Source: Internet
Author: User
Tags create directory

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.