初始化工作
首先建立目录:
$ 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.