初探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

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

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

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

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

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

初探data pump export（一）

相關文章