Oracle导数工具：EXPDP

Datapump是Oracle提供的导数工具包含两个命令行工具expdp和impdp和一个PL/SQL包DBMS_DATAPUMP。

Datapump导数权限

普通用户如果要导入导出全库数据或者其他用户的数据当前用户必须具有datapump_imp_full_database和datapump_exp_full_database权限否则就只能导入导出自己的数据。

SQL> grant datapump_exp_full_database to miguel;
Grant succeeded.

SQL> grant datapump_imp_full_database to miguel;
Grant succeeded.

SQL> select grantee,granted_role from dba_role_privs where grantee='MIGUEL';

GRANTEE      GRANTED_ROLE
------------ ------------------------------
MIGUEL       DATAPUMP_EXP_FULL_DATABASE
MIGUEL       RESOURCE
MIGUEL       CONNECT
MIGUEL       DATAPUMP_IMP_FULL_DATABASE

SQL> select grantee,granted_role from dba_role_privs where grantee='pablo';

GRANTEE      GRANTED_ROLE
------------ ------------------------------
pablo        CONNECT
pablo        RESOURCE

Directory创建及授权

创建Directory对象来存储导出的数据必须有CREATE ANY DIRECTORY权限。

SQL> create directory dumpdir1 as '/oradata/dumpdir';
Directory created.

SQL> select owner,directory_name,directory_path from dba_directories where directory_name='DUMPDIR1';

OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS
DUMPDIR1
/oradata/dumpdir

将Directory的读写权限授予要导数的用户

SQL> grant read,write on directory dumpdir1 to MIGUEL;
Grant succeeded.

SQL> grant read,write on directory dumpdir1 to "pablo";
Grant succeeded.

使用expdp导出数据

EXPDP命令常用的参数有

• directory存储导出数据的Directory名称
• dumpfile导出的dump文件命名
• filesize导出的每个dump文件的最大容量默认值为0表示最大限制为16TB
• logfile导出任务的日志名称默认为export.log
• exclude用于过滤无需导出的对象多个对象用逗号分隔
• parallel导出任务的并行度默认为1
• compression是否对导出数据进行压缩默认为METADATA_ONLY。可选值还有ALL、DATA_ONLY、NONE
• full是否进行全库导出默认值为NO
• schemas要导出的SCHEMA集合多个schema用逗号分隔。默认为当前用户schema
• tables要导出的表集合多个表用逗号分隔。

不指定full、schemas和tables时默认导出当前用户Schema。

管理用户导出数据

管理用户导出全库数据

expdp \'/ as sysdba\' directory=dumpdir1 \
dumpfile=dumpfull_${ORACLE_SID}_`date +%F`.log \
exclude=STATISTICS parallel=2 compression=all full=yes

管理用户导出指定Schema的数据

expdp \'/ as sysdba\' directory=dumpdir1 \
dumpfile=dump_${ORACLE_SID}_`date +%F`.log \
exclude=STATISTICS parallel=2 compression=all schemas=MIGUEL

普通用户导出数据

普通用户导出全库数据需要有datapump_exp_full_database权限

expdp miguel/Xqc\$689 directory=dumpdir1 \
dumpfile=dump_${ORACLE_SID}_`date +%F`.log \
exclude=STATISTICS parallel=2 compression=all full=yes

普通用户导出自己的数据注意对密码中的字符$转义

[oracle@oracledb dumpdir]$ expdp miguel/Xqc\$689 directory=dumpdir1 \
> dumpfile=dump_${ORACLE_SID}_`date +%F`.log \
> exclude=STATISTICS parallel=2 compression=all

Export: Release 19.0.0.0.0 - Production on Sun Jan 15 19:26:43 2023
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "MIGUEL.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-01950: no privileges on tablespace 'OMF_TBS1'
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

如果出现上述报错需要调整用户在自己默认表空间中的Quota:

SQL> select default_tablespace from dba_users where username='MIGUEL';
DEFAULT_TABLESPACE
------------------------------
OMF_TBS1

SQL> alter user miguel quota unlimited on omf_tbs1;
User altered.

重新执行上面的expdp语句即可。

如果用户名是小写在终端执行expdp命令时需要采用'\"<username>\"/<password>'转义

expdp '\"pablo\"/Milf377' directory=dumpdir1 \
dumpfile=dump_${ORACLE_SID}_`date +%F`.log \
exclude=STATISTICS parallel=2 compression=all

References
【1】https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-data-pump-export-utility.html#GUID-5F7380CE-A619-4042-8D13-1F7DDE429991
【2】https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-data-pump-overview.html#GUID-EEB32B50-8A00-40B0-8787-CC2C8BA05DC5