|
ORACLE DATA PUMP in Oracle 10g
Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk
data and metadata movement between Oracle databases. Oracle Data Pump provides
new high-speed, parallel Export and Import utilities (expdp and impdp) as well
as a Web-based Oracle Enterprise Manager interface.
EXPORT USING DATAPUMP.
USER SCOTT for this example..password TIGER.
By default dumps and logs created in $ORACLE_HOME/rdbms/log area.
To avoid using the disk on which the ORACLE_HOME resides...
1. Create 2 directories on SERVER...example mkdir /u01/ORACLE/bozo/datapump and
/u01/ORACLE/bozo/pumplogs
2. Create directories with same path in database.( The physical directories on
server must exist for the 2 commands below to work.)
sql> create directory dump_dir as '/u01/ORACLE/bozo/datapump'; ...All dumps are
sent to this area.
sql> create directory log_dir as '/u01/ORACLE/bozo/pumplogs'; ...All logs are
sent to this area.
sql> grant read,write on directory dump_dir to SCOTT; ---user exporting needs
write priv and user importing needs read priv.
Grant succeeded.
sql> grant read,write on directory log_dir to SCOTT;
Grant succeeded.
ESTIMATE SIZE OF EXPORT-------NO EXPORT OF DATA,ONLY ESTIMATES SIZE OF DUMP:
test10:/opt/oracle>expdp scott/tiger logfile=log_dir:full1.log estimate_only=y
Export: Release 10.2.0.1.0 - Production on Monday, 17 October, 2005 9:10:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": SCOTT/******** logfile=log_dir:full1.log
estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "SCOTT"."DEPT" 64 KB
. estimated "SCOTT"."DEPTBKUP" 64 KB
. estimated "SCOTT"."EMPBKUP" 64 KB
Total estimation using BLOCKS method: 384 KB
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:10:28
Above command does not export data..only estimates size of export dump.
SCHEMA EXPORT: EXPORTS DATA.
test10:/opt/oracle>expdp scott/tiger logfile=log_dir:full1i.log dumpfile=dump_dir:schema.dmp
parallel=2 filesize=2G
EXPORT OVER DATABASE LINK
test11:/u02/ORACLE/test10/pumplogs>expdp scott/tiger@test10 full=y directory=dump_dir
NETWORK_LINK=bozo dumpfile=dump_dir:full.dmp logfile=log_dir:full.log
FULL DATABASE EXPORT
expdp system/temp full=y dumpfile=dump_dir:full.dmp logfile=log_dir:full3.log
parallel=2 filesize=2G
TO REMOVE JOB FROM ANOTHER SESSION..
sql> select * from dba_datapump_jobs;
expdp scott/tiger attach=SYS_EXPORT_FULL_01
Export: Release 10.1.0.2.0 - Production on Thursday, 30 December, 2004 17:29
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Job: SYS_EXPORT_FULL_01
Owner: BH
Operation: EXPORT
Creator Privs: FALSE
GUID: 45DD2D9C04D8457C874C4AF0ADC93E6E
Start Time: Thursday, 30 December, 2004 17:29
Mode: SCHEMA
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND bh/******** DIRECTORY=MYDIR DUMPFILE=e.dmp LOGFILE=e.log JOB_NAME=BHEXP
DATA_ACCESS_METHOD AUTOMATIC
ESTIMATE BLOCKS
INCLUDE_METADATA 1
LOG_FILE_DIRECTORY MYDIR
LOG_FILE_NAME e.log
TABLE_CONSISTENCY 0
USER_METADATA 1
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: D:\DP\e.dmp
bytes written: 4,096
Worker 1 Status:
State: UNDEFINED
Export> kill_job
Are you sure you wish to stop this job ([y]/n): y
D:\>
sql> select * from dba_datapump_jobs;
no rows selected
If you do not have the dump file or the dump file is corrupted then the only way
left is to drop the master table of the job from the schema.
SQL> conn scott/tiger
Connected.
SQL> drop table SCOTT.SYS_EXPORT_FULL_01 PURGE;
SQL> select * from dba_datapump_jobs;
no rows selected
|
