Oracle10g New Feature -- 6. Oracle Data Pump

Oracle10g提供了expdp/impdp工具，其速度遠快於以前的exp/imp。

並且使用者能進入互動介面，也能從資料庫檢視，alert_SID.log檔案察看任務狀態。

由於expdp/impdp的dump檔案只能在伺服器端，故前提需要建一個directory。

[@more@]

1.    Oracle Data Pump

Enter , the newer and faster sibling of the export/import toolkit in Oracle Database 10g, designed to speed up the process many times over

1.     Prepare

Data Pump uses file manipulation on the server side to create and read files; hence, directories are used as locations

SQL> create directory dmp_test as 'c:test';

Directory created.

SQL> grant read,write on directory dmp_test to zhyuh;

Grant succeeded.

2.     Export data

C:test>prompt $T$G

9:29:48.82>

9:29:48.82>expdp zhyuh/zhyuh tables=emp directory=dmp_test dumpfile=expEMP.dmp job_name=emp_expdp

Export: Release 10.1.0.2.0 - Production on Monday, 13 September, 2004 9:30

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "ZHYUH"."EMP_EXPDP":  zhyuh/******** tables=emp directory=dmp_test dump

file=expEMP.dmp job_name=emp_expdp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

dmp_test:  specifies the location of dump file

job_name: All Data Pump work is done though jobs. Data Pump jobs, unlike DBMS jobs, are merely server processes that process the data on behalf of the main process. The main process, known as a master control process, coordinates this effort via Advanced Queuing; it does so through a special table created at runtime known as a master table. In this example, the master table is ZHYUH.EMP_EXPDP, corresponding to the parameter job_name.

3.     Export Monitoring

While Data Pump Export (DPE) is running, press Control-C to shift into interactive mode:

Export> status

Job: EMP_EXPDP

  Operation: EXPORT

  Mode: TABLE

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: C:TESTEXPEMP.DMP

    bytes written: 4,096

Worker 1 Status:

  State: EXECUTING

  Object Schema: ZHYUH

  Object Name: EMP

  Object Type: TABLE_EXPORT/TABLE/TABLE

  Completed Objects: 1

Use command CONTINUE_CLIENT to continue to see the messages on the screen

Export> CONTINUE_CLIENT

Total estimation using BLOCKS method: 2.693 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

4.     Database Monitoring

DBA_DATAPUMP_JOBS: how many worker processes (column DEGREE) are working on the job.

SQL> select * from dba_datapump_jobs;

OWNER_NAME   JOB_NAME    OPERATION   JOB_MODE   STATE              DEGREE   ATTACHED_SESSIONS

---------------------   -----------------   -----------------   ---------------   ------------------   -----------    ------------------------------------

ZHYUH                 EMP_EXPDP   EXPORT           TABLE          EXECUTING    1                 1

DBA_DATAPUMP_SESSIONS:  joined V$SESSION gives the SID of the session of the main foreground process

SQL> select sid, serial#

  2  from v$session s, dba_datapump_sessions d

  3  where s.saddr = d.saddr;

       SID    SERIAL#

---------- ----------

      131                  75

SQL> select * from dba_datapump_sessions;

OWNER_NAME                     JOB_NAME                       SADDR

------------------------------ ------------------------------ --------

ZHYUH                          EMP_EXPDP                      6C5AE3BC

Additional useful information can be obtained from the view V$SESSION_LONGOPS

SQL> select sid, serial#, OPNAME,sofar, totalwork

  2  from v$session_longops

  3  where  sofar != totalwork;

       SID    SERIAL#    OPNAME                    SOFAR    TOTALWORK

---------------------------------------------------------------- ---------- ----------

       130         60            Rowid Range Scan      90282       344816

       127        367           EMP_EXPDP               0              2758

When the expdp process starts up, the MCP and the worker processes are shown in the Alert_orcl.log

 as follows:

 Mon Sep 13 09:51:49 2004

The value (30) of MAXTRANS parameter ignored.

kupprdp: master process DM00 started with pid=33, OS id=2012

         to execute - SYS.KUPM$MCP.MAIN('EMP_EXPDP', 'ZHYUH');

kupprdp: worker process DW01 started with worker id=1, pid=35, OS id=1628

         to execute - SYS.KUPW$WORKER.MAIN('EMP_EXPDP', 'ZHYUH');

SQL> select sid, program from v$session where paddr in

  2   (select addr from v$process where pid in (33,35));

       SID PROGRAM

---------- -------------------------------------------------

       136 ORACLE.EXE (DW01)

       137 ORACLE.EXE (DM00)

If always in client mode to see the messages on the screen

9:51:41.11>expdp zhyuh/zhyuh tables=emp directory=dmp_test dumpfile=expEMP.dmp

job_name=emp_expdp

Export: Release 10.1.0.2.0 - Production on Monday, 13 September, 2004 9:51

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Starting "ZHYUH"."EMP_EXPDP":  zhyuh/******** tables=emp directory=dmp_test dump

file=expEMP.dmp job_name=emp_expdp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2.693 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "ZHYUH"."EMP"                               2.269 GB 58720256 rows

Master table "ZHYUH"."EMP_EXPDP" successfully loaded/unloaded

******************************************************************************

Dump file set for ZHYUH.EMP_EXPDP is:

  C:TESTEXPEMP.DMP

Job "ZHYUH"."EMP_EXPDP" successfully completed at 09:57

 9:57:40.99>