ablog

不器用で落着きのない技術者のメモ

Data Pump でエクスポート中に並列度を変える

Oracle 
$ nohup expdp system/manager directory=pump_dir dumpfile=scott.dmp schemas=scott flashback_time="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')" &

こんな感じでえくすぽってる途中で、並列度を上げたいときは、
ジョブ名を確認して、

$ sqlplus / as sysdba
SQL> set linesize 200
SQL> col owner_name for a10
SQL> col job_name for a20
SQL> col operation for a10
SQL> col job_mode for a10
SQL> col state for a12
SQL> col degree for 999
SQL> col attached_sessions for 999
SQL> col datapump_sessions for 999
SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- -------------------- ---------- ---------- ------------ ------ ----------------- -----------------
SYSTEM     SYS_EXPORT_SCHEMA_01 EXPORT     SCHEMA     EXECUTING         3                 2                 6

SQL> exit

ジョブに接続して、

$ expdp system/manager attach=SYS_EXPORT_SCHEMA_01

Export: Release 10.2.0.3.0 - Production on , 04 6, 2010 3:41:10

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Job: SYS_EXPORT_SCHEMA_01
  Owner: SYSTEM                         
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: 8823EE77B6707105E0401DAC1903609F
  Start Time: , 04 6, 2010 2:56:53
  Mode: SCHEMA                         
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=pump_dir dumpfile=SCOTT.dmp schemas=SCOTT flashback_time="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')" 
     FLASHBACK_TIME        10-6 -04 02:56:53                 
  State: EXECUTING                      
  Bytes Processed: 4,553,084,352
  Percent Done: 1
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /opt/oracle/db/d02/dpdump/SCOTT.dmp
    bytes written: 4,555,358,208
  
Worker 1 Status:
  State: EXECUTING                      
  Object Schema: SCOTT
  Object Name: TOTAL
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 3
  Total Objects: 357
  Completed Rows: 9,261,974
  Worker Parallelism: 1

並列度を3に上げて、

Export> PARALLEL=3

確認してみる。

Export> STATUS    

Job: SYS_EXPORT_SCHEMA_01
  Operation: EXPORT                         
  Mode: SCHEMA                         
  State: EXECUTING                      
  Bytes Processed: 13,879,867,192
  Percent Done: 3
  Current Parallelism: 3
  Job Error Count: 0
  Dump File: /opt/oracle/db/d02/dpdump/SCOTT.dmp
    bytes written: 13,882,146,816
  
Worker 1 Status:
  State: EXECUTING                      
  Object Schema: SCOTT
  Object Name: TOTAL
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 7
  Total Objects: 357
  Completed Rows: 9,300,568
  Worker Parallelism: 1
  
Worker 2 Status:
  State: EXECUTING                      
  Object Schema: SCOTT
  Object Name: TOTAL
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 357
  Worker Parallelism: 1
  
Worker 3 Status:
  State: EXECUTING                      
  Object Schema: SCOTT
  Object Name: TOTAL
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 357
  Worker Parallelism: 1

並列度が3になった。

ジョブから切断して、

Export> EXIT_CLIENT

プロセスを確認してみると、

$ ps -U oracle uxw|grep dw
oracle   24738 41.5 13.9 1734152 1157940 ?   Ss   02:56  24:40 ora_dw01_orcl
oracle   28706 15.2  1.0 1680512 84268 ?     Rs   03:46   1:34 ora_dw02_orcl
oracle   28708 10.5  0.8 1680516 70576 ?     Ss   03:46   1:05 ora_dw03_orcl

並列度3でやってくれてるみたい。

関連