1、 Create Directory
1. Create a logical directory: (The directory must exist, or an error will be reported, and it is finally created with system)
create directory dpdata as '/u01/app/oracle/oracledata/dump';
Or create or replace directory dpdata as'/u01/app/oracle/oracledata/dump ';
2. View the manager directory
select * from dba_directories;
3. Unlock scott user
sqlplus /nolog
Administrator login: conn sys/password as sysdba
alter user scott identified by tiger;
Unlock the alter user scott account unlock;
Verification: conn scott/tiger
3. Give the Scott user the operation permission in the specified directory, preferably given by the system administrator
grant read,write on directory dpdata to scott;
2、 Export Data
1) By User Guide
expdp scott/ tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata;
2) Parallel
expdp scott/ tiger@orcl directory=dpdata dumpfile=scott3.dmp parallel=40 job_name=scott3
3) Guide by table name
expdp scott/ tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata;
4) Export by Query Criteria
expdp scott/ tiger@orcl directory=dpdata dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5) Import by tablespace
expdp system/manager DIRECTORY=dpdata DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6) Import the entire database
expdp system/manager DIRECTORY=dpdata DUMPFILE=full.dmp FULL=y;
If you need to override the added parameter table_exists_action=replace
{skip is to skip and process the next object if the table already exists; Append is to add data to the table; Truncate is to truncate the table and then add new data to it; Replace is to delete the existing table, rebuild the table and append data}
3、 Restore Data
1) Import to the specified user
impdp scott/tiger DIRECTORY=dpdata DUMPFILE=expdp.dmp SCHEMAS=scott;
2) Change the owner of the table
impdp system/manager DIRECTORY=dpdata DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
3) Import tablespace
impdp system/manager DIRECTORY=dpdata DUMPFILE=tablespace.dmp TABLESPACES=example;
4) Import Database
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5) Append Data
impdp system/manager DIRECTORY=dpdata DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;