啟動資料泵匯入和匯出作業時，在伺服器上會初始化一個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

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工具查詢該表能看到多如牛毛的資訊資訊，截圖只是九毛中的一毛。

全文完

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

相關文章