12c 資料泵作業啟動後相關程式和表

snowdba發表於2015-02-25
啟動資料泵匯入和匯出作業時，在伺服器上會初始化一個OS主程式ora_dmNN_和一個expdp/impdp進城，當作業結束，上述程式消失。可以使用ps命令來檢視這些變化為我們監控作業提供一些資訊。如果開啟並行模式parallel，則會出現ora_dwNN_程式。

另外在匯出匯入過程中還會建立資料庫狀態表，該表的的owner為啟動該作業的使用者。如果使用expdp \'/ as sysdba\’啟動作業owner為SYS，如果使用 expdp hr/hr啟動作業owner為hr。該表有80多個列記錄了大量詳細資訊，成功匯出和匯入作業後資料泵會刪除該表。如果在作業當中使用stop_job互動命令停止作業，可以檢視該表保留的資訊，如果採用kill_job互動式命令刪除該作業那麼該表也隨之被刪除。 

建立一個目錄物件dp_dir,指向OS目錄/home/oracle
[oracle@snow ~]$ sqlplus / as sysdba
SYS@ora12c >create directory dp_dir as '/home/oracle';

解鎖示例使用者hr
SYS@ora12c >alter user hr identified by hr account unlock;
SYS@ora12c >exit

此時OS中沒有ora_dm進城
[root@snow ~]# ps -ef|grep ora_dm
root 75899 74610 0 06:34 pts/1 00:00:00 grep ora_dm

使用sys使用者匯出hr下所有內容
[oracle@snow ~]$ expdp \'/ as sysdba\' dumpfile=hr.dmp logfile=hr.log directory=dp_dir schemas=hr

Export: Release 12.1.0.1.0 - Production on Mon Feb 9 06:29:43 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=hr.dmp logfile=hr.log directory=dp_dir schemas=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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "HR"."COUNTRIES" 6.437 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.101 KB 27 rows
. . exported "HR"."EMPLOYEES" 17.06 KB 107 rows
. . exported "HR"."JOBS" 7.085 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.171 KB 10 rows
. . exported "HR"."LOCATIONS" 8.414 KB 23 rows
. . exported "HR"."REGIONS" 5.523 KB 4 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hr.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 9 06:30:23 2015 elapsed 0 00:00:38

在expdp執行過程中會出現ora_dmNN_SID程式以及expdp程式
[root@snow ~]# ps -ef|grep -v grep |grep ora_dm
oracle 76052 1 39 06:42 ? 00:00:01 ora_dm00_ora12c

[root@snow ~]# ps -ef|grep -v grep |grep expdp
oracle 76047 74500 0 06:42 pts/0 00:00:00 expdp as sysdba' dumpfile=hr.dmp logfile=hr.log directory=dp_dir schemas=hr

如果開啟並行模式parallel，則會出現ora_dwNN_程式。
在下面的例子中開啟並行度5，在作業執行過程中會出現5個工程式來做具體的操作
[oracle@snow ~]$ expdp \'/ as sysdba\' dumpfile=hr_%u.dmp parallel=5 logfile=hr.log directory=dp_dir schemas=hr

[oracle@snow ~]$ ps -ef|grep -v grep|grep ora_dm
oracle 77433 1 22 08:40 ? 00:00:01 ora_dm00_ora12c

[oracle@snow ~]$ ps -ef|grep -v grep|grep ora_dw
oracle 77435 1 91 08:40 ? 00:00:10 ora_dw00_ora12c
oracle 77437 1 3 08:40 ? 00:00:00 ora_dw01_ora12c
oracle 77439 1 0 08:40 ? 00:00:00 ora_dw02_ora12c
oracle 77441 1 0 08:40 ? 00:00:00 ora_dw03_ora12c
oracle 77443 1 0 08:40 ? 00:00:00 ora_dw04_ora12c


除了作業期間的這些程式還會出現資料庫狀態表，該表只有作業執行期間會出現。
SYS@ora12c >select owner,table_name,tablespace_name from dba_tables where table_name like 'SYS_EXPORT%'

OWNER TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------ ------------------------------
SYS SYS_EXPORT_SCHEMA_01 SYSTEM


通過toad工具查詢該表能看到多如牛毛的資訊資訊，截圖只是九毛中的一毛。


全文完

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1441080/，如需轉載，請註明出處，否則將追究法律責任。

相關文章