Oracle Datapump实验

初始化工作

首先建立目录：

$ sudo mkdir /u01/dumpdir
$ sudo chown oracle:oinstall /u01/dumpdir

PDB数据库中创建directory:

SQL> alter session set container=orclpdb1;

Session altered.

SQL> create directory dp_dir as '/u01/dumpdir';

Directory created.

SQL> set pagesize 0
SQL> select owner, directory_name, directory_path from dba_directories;
SYS      DP_DIR                   /u01/dumpdir
SYS      XMLDIR                   /u01/oracle/product/12.2.0.1/dbhome_1/rdbms/xml
SYS      XSDDIR                   /u01/oracle/product/12.2.0.1/dbhome_1/rdbms/xml/schema
SYS      ORA_DBMS_FCP_LOGDIR      /u01/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs
SYS      ORA_DBMS_FCP_ADMINDIR    /u01/oracle/product/12.2.0.1/dbhome_1/rdbms/admin
SYS      OPATCH_INST_DIR          /u01/oracle/product/12.2.0.1/dbhome_1/OPatch
SYS      ORACLE_OCM_CONFIG_DIR    /u01/oracle/product/12.2.0.1/dbhome_1/ccr/state
SYS      DATA_PUMP_DIR            /u01/oracle/admin/ORCLCDB/dpdump/878E05CEE9E952CAE0530100007F118F
SYS      ORACLE_OCM_CONFIG_DIR2   /u01/oracle/product/12.2.0.1/dbhome_1/ccr/state
SYS      OPATCH_SCRIPT_DIR        /u01/oracle/product/12.2.0.1/dbhome_1/QOpatch
SYS      OPATCH_LOG_DIR           /u01/oracle/product/12.2.0.1/dbhome_1/rdbms/log
SYS      ORACLE_BASE              /u01/oracle
SYS      ORACLE_HOME              /u01/oracle/product/12.2.0.1/dbhome_1

13 rows selected.

创建用户dpuser，并赋予权限：

SQL> show con_name;
ORCLPDB1
SQL> create user dpuser identified by "foo";

User created.

SQL> grant create session, create table to dpuser;

Grant succeeded.

SQL> grant read, write on directory dp_dir to dpuser;

Grant succeeded.
SQL> alter user dpuser quota 100m on users;

User altered.

使用dpuser创建一些表：

$ sqlplus dpuser/foo@orclpdb1

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 28 16:35:32 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table foo(a int);

Table created.
SQL> insert into foo values(1);

1 row created.

导出测试：

$ expdp dpuser/foo@orclpdb1 directory=dp_dir tables=foo dumpfile=exp.dmp logfile=exp.log

Export: Release 12.2.0.1.0 - Production on Sun Apr 28 16:40:19 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DPUSER"."SYS_EXPORT_TABLE_01":  dpuser/********@orclpdb1 directory=dp_dir tables=foo dumpfile=exp.dmp logfile=exp.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DPUSER"."FOO"                              5.062 KB       1 rows
Master table "DPUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPUSER.SYS_EXPORT_TABLE_01 is:
  /u01/dumpdir/exp.dmp
Job "DPUSER"."SYS_EXPORT_TABLE_01" successfully completed at Sun Apr 28 16:40:58 2019 elapsed 0 00:00:33

查看：

$ cd /u01/dumpdir/
$ ll
total 188
-rw-r-----. 1 oracle oinstall 188416 Apr 28 16:40 exp.dmp
-rw-r--r--. 1 oracle oinstall   1016 Apr 28 16:40 exp.log

导入测试：

先删除表：

[oracle@oracle-12201-vagrant-dev ~]$ rlwrap sqlplus dpuser/foo@orclpdb1

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 4 18:23:40 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 28 2019 16:40:19 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from foo;

         A
----------
         1

SQL> drop table foo purge;

Table dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

导入并验证：

[oracle@oracle-12201-vagrant-dev ~]$ impdp dpuser/foo@orclpdb1 directory=dp_dir dumpfile=exp.dmp logfile=imp.log

Import: Release 12.2.0.1.0 - Production on Sat May 4 18:26:14 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "DPUSER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DPUSER"."SYS_IMPORT_FULL_01":  dpuser/********@orclpdb1 directory=dp_dir dumpfile=exp.dmp logfile=imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DPUSER"."FOO"                              5.062 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "DPUSER"."SYS_IMPORT_FULL_01" successfully completed at Sat May 4 18:26:52 2019 elapsed 0 00:00:33

[oracle@oracle-12201-vagrant-dev ~]$ rlwrap sqlplus dpuser/foo@orclpdb1

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 4 18:30:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat May 04 2019 18:26:15 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from foo;

         A
----------
         1

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

使用参数文件

注意用户名userid的写法：

[oracle@oracle-12201-vagrant-dev ~]$ cat exp.par
userid=dpuser/foo@orclpdb1
directory=dp_dir
dumpfile=exp.dmp
logfile=exp.log
tables=foo
reuse_dumpfiles=y
[oracle@oracle-12201-vagrant-dev ~]$ expdp parfile=exp.par

Export: Release 12.2.0.1.0 - Production on Sat May 4 18:47:39 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DPUSER"."SYS_EXPORT_TABLE_01":  dpuser/********@orclpdb1 parfile=exp.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DPUSER"."FOO"                              5.062 KB       1 rows
Master table "DPUSER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPUSER.SYS_EXPORT_TABLE_01 is:
  /u01/dumpdir/exp.dmp
Job "DPUSER"."SYS_EXPORT_TABLE_01" successfully completed at Sat May 4 18:48:03 2019 elapsed 0 00:00:22

更重要的一点是，即使执行多次，结果都是一样的，并非追加数据。

[oracle@oracle-12201-vagrant-dev ~]$ rlwrap sqlplus dpuser/foo@orclpdb1

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 4 18:49:03 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat May 04 2019 18:47:39 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from foo;

         A
----------
         1

使用参数文件的一个好处是转义会简单些，例如使用SYS用户的命令行为：

$ impdp 'sys/Abcdef_123456 as sysdba'

而使用参数文件则写为：

$ cat expsys.par
userid="sys/Abcdef_123456 as sysdba"

整库导出

参数文件为：

[oracle@oracle-12201-vagrant-dev ~]$ cat exp.par
userid="sys/Abcdef_123456 as sysdba"
directory=dp_dir
dumpfile=exp.dmp
logfile=exp.log
reuse_dumpfiles=y
full=y

导出，第一次失败是由于没有为SYS用户创建dump目录。

[oracle@oracle-12201-vagrant-dev ~]$ expdp parfile=exp.par

Export: Release 12.2.0.1.0 - Production on Sat May 4 19:30:49 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DP_DIR is invalid


[oracle@oracle-12201-vagrant-dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 4 19:31:08 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create directory dp_dir as '/u01/dumpdir';

Directory created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle-12201-vagrant-dev ~]$ ls -l /u01/dumpdir/
total 192
-rw-r-----. 1 oracle oinstall 188416 May  4 18:49 exp.dmp
-rw-r--r--. 1 oracle oinstall   1348 May  4 18:49 exp.log
-rw-r--r--. 1 oracle oinstall    849 May  4 18:26 imp.log
[oracle@oracle-12201-vagrant-dev ~]$ rm /u01/dumpdir/
rm: cannot remove ‘/u01/dumpdir/’: Is a directory
[oracle@oracle-12201-vagrant-dev ~]$ rm /u01/dumpdir/*
[oracle@oracle-12201-vagrant-dev ~]$ expdp parfile=exp.par

Export: Release 12.2.0.1.0 - Production on Sat May 4 19:32:07 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_FULL_01":  sys/******** AS SYSDBA parfile=exp.par
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.070 KB      36 rows
. . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows
. . exported "ORDDATA"."ORDDCM_DOCS"                     252.9 KB       9 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows
. . exported "WMSYS"."WM$HINT_TABLE$"                    9.984 KB      97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS"               6.960 KB       2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.078 KB      11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.375 KB       1 rows
. . exported "LBACSYS"."OLS$PROPS"                       6.234 KB       5 rows
. . exported "WMSYS"."WM$ENV_VARS$"                      6.015 KB       3 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.812 KB      12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.757 KB       8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.539 KB       2 rows
. . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows
. . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows
. . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows
. . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows
. . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows
. . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows
. . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows
. . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows
. . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
. . exported "MDSYS"."RDF_PARAM$"                        6.515 KB       3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP"                       0 KB       0 rows
. . exported "SYS"."SQL$_DATAPUMP"                           0 KB       0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"                 0 KB       0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                    0 KB       0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP"                        0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.671 KB       4 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           10.29 KB      23 rows
. . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows
. . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/dumpdir/exp.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Sat May 4 19:42:30 2019 elapsed 0 00:10:22

Schema导出

导出，会导出创建用户的语句：

[oracle@xyprim ssb-dbgen]$ expdp system@orclpdb1 schemas=ssb dumpfile=exp.dmp

Export: Release 19.0.0.0.0 - Production on Wed Aug 2 09:46:01 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orclpdb1 schemas=ssb dumpfile=exp.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SSB"."CUSTOMER"                            25.40 MB  240000 rows
. . exported "SSB"."DATE_DIM"                            270.8 KB    2556 rows
. . exported "SSB"."LINEORDER"                           1.107 GB 11997996 rows
. . exported "SSB"."PART"                                85.89 MB 1000000 rows
. . exported "SSB"."SUPPLIER"                            1.629 MB   16000 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/log/01ABBB95C89742ADE063AE00000A3CE8/exp.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 2 09:46:56 2023 elapsed 0 00:00:47

删除用户：

SQL> drop user ssb cascade;

User dropped.

导入，如果用户不存在，会自动创建用户：

$ impdp system@orclpdb1 schemas=ssb dumpfile=exp.dmp

Import: Release 19.0.0.0.0 - Production on Wed Aug 2 09:51:26 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@orclpdb1 schemas=ssb dumpfile=exp.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SSB"."CUSTOMER"                            25.40 MB  240000 rows
. . imported "SSB"."DATE_DIM"                            270.8 KB    2556 rows

. . imported "SSB"."LINEORDER"                           1.107 GB 11997996 rows
. . imported "SSB"."PART"                                85.89 MB 1000000 rows
. . imported "SSB"."SUPPLIER"                            1.629 MB   16000 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Aug 2 09:53:15 2023 elapsed 0 00:01:36

获取帮助：

$ expdp help=y

Export: Release 12.2.0.1.0 - Production on Sat May 4 19:14:35 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [NO].

HELP
Display Help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].

LOGFILE
Specify log file name [export.log].

LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

METRICS
Report additional job information to the export log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

SAMPLE
Percentage of data to be exported.

SCHEMAS
List of schemas to export [login schema].

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

STOP_WORKER
Stops a hung or stuck worker.

TRACE
Set trace/debug flags for the current job.