Brief description of oracle expdp impdp

Source: Internet
Author: User

With the data pump expdp, impdp can import and export data on the server, with high efficiency.
You do not need to set the environment variable NLS_LANG. By default, it will go to the character set of the database.


Create directory backup as '/backup ';
Grant read, write on directory backup to scott;
1. Database-based export and import
 
Database a full Database Export
Expdp system/root directory = backup dumpfile = full. dmp logfile = full. log full = y

B. Import the entire database
Impdp system/root directory = backup dumpfile = full. dmp logfile = full2.log full = y

# Import data of a user, specified by schemas
Impdp system/root directory = backup dumpfile = full. dmp logfile = full2.log schemas = scott

Impdp system/root directory = backup dumpfile = full. dmp logfile = full2.log schemas = scott, gpecnew, hsj1

2. Export and Import based on user (schema) (take scott user as an example)

A. Export scott user data
Grant read, write on directory backup to scott;
Expdp scott/root directory = backup dumpfile = scott. dmp logfile = scott. log schemas = scott
Or
Expdp system/root directory = backup dumpfile = scott. dmp logfile = scott. log schemas = scott

B. It is easier to import data with imp. You no longer need to create users, but you only need to create tablespaces.
Impdp system/root directory = backup dumpfile = scott. dmp logfile = scott2.log schemas = scott
Or
Impdp system/root directory = backup dumpfile = scott. dmp logfile = scott2.log full = y;
Or
Impdp system/root directory = backup dumpfile = scott. dmp logfile = scott2.log

3. Only one solution table data can be exported at a time for table-based export and import.
A. Export the data table dept and emp.

Expdp system/root directory = backup dumpfile = tables. dmp logfile = tables. log TABLES = scott. dept, scott. emp

Or

Expdp scott/root directory = backup dumpfile = tables. dmp logfile = tables. log TABLES = dept, emp

B. Import the data file
Impdp scott/root directory = backup dumpfile = tables. dmp logfile = tables2.log (full = y );

4. Exclude all tables starting with V and starting with Z based on the user export table, and exclude t_bd_person and t_bd_users


A Export
Expdp hsj/hsj directory = backup dumpfile = hsj. dmp logfile = hsj. log schemas = hsj
Exclude = table: \ "like \ 'v % \ '\", table: \ "like \ 'z % \", table: \ "in \ (\'t _ BD_PERSON ', \'t _ BD_USERS '\)\"

Alternatively, use the parfile parameter file hsj. par.
Userid = hsj/hsj
Directory = backup
Dumpfile = hsj. dmp
Logfile = hsj. log
Schemas = hsj
Exclude = table: "like 'v % '", table: "like 'z %", table: "in ('t _ BD_PERSON','t _ BD_USERS ')"

Expdp parfile = hsj. par

B Import
Impdp hsj/hsj directory = backup dumpfile = hsj. dmp logfile = hsj2.log
 
NOTE: For the include parameter, it is not possible to export conditions for tables starting with V or starting with Z. conditions can only be and or
For expdp and impdp, we need to set directory parameters. refer to the following two statements:
Create directory backup as '/backup ';
Grant read, write on directory backup to scott;

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.