初探data pump export（一）
普通用户使用data pump export 前需要获得一个有读写权限的目录对象。
SQL> grant read,write on directory dump_test_dir to hr;
Grant succeeded.
我再给hr 用户授权两个强大的系统角色，以便hr 使用data pump 工具。
先确定下hr 当前获得的角色.再给hr 授权.
SQL> select * from dba_role_privs
2 where grantee='HR';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
HR RESOURCE NO YES
SQL> grant datapump_imp_full_database to hr;
Grant succeeded.
SQL> grant datapump_exp_full_database to hr;
Grant succeeded.
SQL> select * from dba_role_privs
2 where grantee='HR';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
HR RESOURCE NO YES
HR DATAPUMP_IMP_FULL_DATABASE NO YES
HR DATAPUMP_EXP_FULL_DATABASE NO YES
现在hr 可以执行各种data pump 操作了。这两个角色可以允许hr 执行如下的操作：
1、在自己的schema 外执行data pump 操作。
2、监视由其他用户启动的data pump 作业。
3、导入导出对象，未经授权的用户不能干预。
这两个角色是很强大的，所有DBA在授权的时候要谨慎，遵循组织规范与最小特权原则。
使用table mode export hr 下的 employees,jobs 表。不产生日志文件。
[oracle@zeng ~]$ expdp hr tables=employees,jobs dumpfile=dump_test_dir:table.dmp nologfile=yes
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 12:54:48 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** tables=employees,jobs dumpfile=dump_test_dir:table.dmp nologfile=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/opt/oracle11g/admin/oracl/table.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 12:55:14
文件名为table.dmp 的dumpfile 已经存在于操作系统中。
如果你想覆盖掉以前泵出的文件，可以使用reuse_dumpfiles=yes 参数。
[oracle@zeng ~]$ ll /opt/oracle11g/admin/oracl/table.dmp
-rw-r-----. 1 oracle oinstall 176128 Mar 18 12:55 /opt/oracle11g/admin/oracl/table.dmp
[oracle@zeng ~]$ expdp hr tables=employees,jobs dumpfile=dump_test_dir:table.dmp nologfile=yes reuse_dumpfiles=yes
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 20:46:36 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** tables=employees,jobs dumpfile=dump_test_dir:table.dmp nologfile=yes reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/opt/oracle11g/admin/oracl/table.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 20:47:02
很多时候你希望查看data pump data 的日志以便确定该export 作业的情况。
[oracle@zeng ~]$ expdp hr tables=employees,jobs dumpfile=dump_test_dir:table.dmp logfile=dump_test_dir:log reuse_dumpfiles=yes
执行上面的命令会在目录对象关联的目录中产生一个log.log的日志文件。其实里面的内容就是
执行上述语句的产生的输出。就是下面的内容：
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 20:51:31 2012
.
.
.
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 20:51:54
该日志文件对应当前来说可能是无所谓的，因为在终端的屏幕上会输出同日志文件一样的信息，但是也许将来你需要知道过去一次export 做了什么或者出了什么问题。data pump export 默认的job name 是：SYS_EXPORT__NN 比如上面看到的SYS_EXPORT_TABLE_01 其中mode 表示export 的导出模式，NN是一个是从01 起始的一个编号.你可以在命令行中通过job_name 指定作用的名称，便于记忆。
使用Ctrl+C data pump export 的交互模式。
[oracle@zeng ~]$ expdp
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 21:31:34 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: hr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
^C
Export> status
Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle11g/admin/oracl/dpdump/expdat.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: HR
Object Name: EMP_DETAILS_VIEW
Object Type: SCHEMA_EXPORT/VIEW/VIEW
Completed Objects: 1
Total Objects: 1
Worker Parallelism: 1
Export>
在交互模式下使用status查看当前工作的状态信息。同时我们注意到data pump export 的灵活性，我没有提供任何参数，他却自己把hr schema 给导出来了.并且默认的dumpfile 名是expdat.dmp，日志文件名是export.log
如果想要查看 data pump job 的状态.可以查看dba_datapump_jobs ，user_datapump_jobs
dba_datapump_sessions。
例如我在一个工作出现问题停止的时候查询dba_datadump_jobs获得如下的信息。
SQL> select owner_name,job_name,job_mode,state from dba_datapump_jobs;
OWNER_NAME JOB_NAME JOB_MODE STATE
---------- ------------------------------ ---------- ------------------------------
SYSTEM SYS_EXPORT_SCHEMA_03 SCHEMA NOT RUNNING
HR EXP_HR_JOB SCHEMA NOT RUNNING
SYSTEM SYS_EXPORT_SCHEMA_01 SCHEMA NOT RUNNING
OWNER_NAME JOB_NAME JOB_MODE STATE
---------- ------------------------------ ---------- ------------------------------
SYSTEM SYS_EXPORT_SCHEMA_04 SCHEMA NOT RUNNING
SYSTEM SYS_EXPORT_SCHEMA_02 SCHEMA NOT RUNNING
data pump export 提供了很多可供使用的命令。下面我粗糙的介绍几个常用的命令。详细的情况
建议查看oracle 对应的官方文档。
job_name 指定data pump export job 的名称模式是SYS_EXPORT__NN
dumpfile 指定data pump export 的dump 文件名,默认是expdat.dmp。
logfile 指定data pump export 的日志文件名.默认是export.log。
directory 指定使用的目录对象，对应sys,system 默认是data_pump_dir。
filesize 指定每个dumpfiles 的大小.默认值是0,也就是等于16 terabyte。最小是4kB。
如果filesize 不够报错.
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "HR"."EXP_HR_JOB" stopped due to fatal error at 22:28:12
并且job 已经停止了,但是没有终止，挂起在后台了。我们来解决一下吧。
[oracle@zeng ~]$ expdp attach=exp_hr_job
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 22:39:32 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: hr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: EXP_HR_JOB
Owner: HR
Operation: EXPORT
Creator Privs: TRUE
GUID: BB863A58EB8E8063E040000A0F021079
Start Time: Sunday, 18 March, 2012 22:39:41
Mode: SCHEMA
Instance: oracl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND hr/******** dumpfile=hrdump01.dmp job_name=exp_hr_job filesize=100KB
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle11g/admin/oracl/dpdump/hrdump01.dmp
size: 102,400
bytes written: 98,304
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: HR
Object Name: JOBS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 4
Total Objects: 7
Worker Parallelism: 1
Export> add_file=adddump.dmp
Export> status
Job: EXP_HR_JOB
Operation: EXPORT
Mode: SCHEMA
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle11g/admin/oracl/dpdump/hrdump01.dmp
size: 102,400
bytes written: 98,304
Dump File: /opt/oracle11g/admin/oracl/dpdump/adddump.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: HR
Object Name: JOBS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 4
Total Objects: 7
Worker Parallelism: 1
Export> start_job
Export> status
Job: EXP_HR_JOB
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle11g/admin/oracl/dpdump/hrdump01.dmp
size: 102,400
bytes written: 102,400
Dump File: /opt/oracle11g/admin/oracl/dpdump/adddump.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Export> exit_client
我使用attavh 联系上我的作业EXP_HR_JOB.然后给他添加一个dumpfile，添加的dumpfile大小
等于作业EXP_HR_JOB 中指定的filesize.然后重新启动作业，然后退出export客户端，让作业
在后台执行。等到作业执行完以后，我们查看一下日志确定EXP_HR_JOB 这个作业的完成情况。
Dump file set for HR.EXP_HR_JOB is:
/opt/oracle11g/admin/oracl/dpdump/hrdump01.dmp
/opt/oracle11g/admin/oracl/dpdump/adddump.dmp
Job "HR"."EXP_HR_JOB" completed with 1 error(s) at 22:43:42
这里报的一个error 就是前面那个ORA-39095。
如果在一个目录对象关联的目录中已经存在一个同名的dumpfile 会报如下的错误。我没有指定输出的日志文件名，data pump export 会使用默认的export.log覆盖已经存在的export.log而不会报错。
[oracle@zeng ~]$ expdp hr dumpfile=hrdump.dmp job_name=exp_hr_job filesize=500KB
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 22:21:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/opt/oracle11g/admin/oracl/dpdump/hrdump.dmp"
ORA-27038: created file already exists
Additional information: 1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26110315/viewspace-718918/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
使用data pump前的设置
请登录后发表评论
登录
全部评论
