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;