ITPub博客

首页 > 数据库 > Oracle > Oracle10g New Feature -- 6. Oracle Data Pump

Oracle10g New Feature -- 6. Oracle Data Pump

原创 Oracle 作者:zhyuh 时间:2004-09-21 09:40:37 0 删除 编辑

Oracle10g提供了expdp/impdp工具,其速度远快于以前的exp/imp。

并且用户能进入交互界面,也能从数据库视图,alert_SID.log文件察看任务状态。

由于expdp/impdp的dump文件只能在服务器端,故前提需要建一个directory。

[@more@]

1.    Oracle Data Pump

Enter Oracle Data Pump, 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

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

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

 9:57:40.99>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-778752/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008617