Oracle10g New Feature -- 6. Oracle Data Pump-CSDN博客

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 9:30

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

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>

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

转载于:http://blog.itpub.net/207/viewspace-778752/