You are here: Home Administration Datapump
19 | 04 | 2024
Main Menu
Online database
Resources
Latest Articles
Popular Articles

Administration

Datapump

  • Print
User Rating:  / 0
PoorBest 
Details
Parent Category: Articles
Created on Wednesday, 17 March 2010 22:59
Last Updated on Wednesday, 24 April 2013 15:51
Published on Wednesday, 17 March 2010 22:59
Written by Guy Lambregts
Hits: 6319

Datapump

 

See also

Datapump : Enhanced granularity with "EXCLUDE" and "INCLUDE"

Datapump Consistent

Attach to and manage running datapump jobs

Datapump new features in Oracle 11G

 

I will give some examples of how we can use the datapump of Oracle 10G. In previous releases we could use the export and import utilities in order to make a dump from the database or from a part of the database. In order to know the attributes of both export utility and import utility we could type exp help=yes or imp help=yes. We could export and import the entire database, a user and the objects owned by this user or we could export/import at the table level. We could choose whether to export the tabledata yes or no (rows=y or rows=n) the indexes (indexes=y or indexes=n) the constraints (constraints=y or constraints=n) , the grants (grants=y or grants=n). In 10G the traditional export and import still exists. In stead of further improvement of both export and import utility Oracle 10G comes with Datapump. We can invoke the datapump with expdp and impdp, and similar to the traditional export/import expdp help=yes either impdp help=yes can be used in order to list the attributes and options. Note

1. ) a traditional dumpfile we got with exp cannot be imported by the impdp of 10G.

2. ) expdp and impdp use direct path whenever possible as such it is running faster compared with the traditional exp & imp.


Example 1 : Some wonderfull options

 

I use 3 interesting options. The remap_schema will import the objects from the original schema owner cw4ora towards a newly created user test.The remap_tablespace feature will import the segments in another tablespace (in stead of the source tablespace). With the exclude option I do not import the grants. Please note we can use the exclude and include option at the segment level. This can be interesting in order to skip some segments, in order to pick up only a part of them.

First of all I create new tablespaces test_data and test_indx similar to the cw4ora_data and cw4ora_indx in which the segments reside.

 

SQL> connect system
SQL> spool c:\temp\create_tablespaces.sql

SQL> set linesize 1000

SQL> set long 20000
SQL> select dbms_metadata.get_ddl('TABLESPACE','CW4ORA_DATA') from dual;

CREATE TABLESPACE "CW4ORA_DATA" DATAFILE
'C:\ORACLE\ORADATA\ORA10\CW4ORA_D1.DBF' SIZE 524288000
AUTOEXTEND ON NEXT 100M MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

SQL> select dbms_metadata.get_ddl('TABLESPACE','CW4ORA_INDX') from dual;

CREATE TABLESPACE "CW4ORA_INDX" DATAFILE
'C:\ORACLE\ORADATA\ORA10\CW4ORA_I1.DBF' SIZE 314572800
AUTOEXTEND ON NEXT 100M MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

SQL> spool off;

 


I edit the script c:\temp\create_tablespaces.sql in order to replace cw4ora_data with test_data and cw4ora_indx with test_indx. Then I lauch the script.

I create a directory towards I wanna export

 

SQL> create directory test as 'c:\temp';

Directory created.

SQL> grant all on directory test to public;
Grant succeeded.

 

Let' s invoke expdp with the options as discribed here above

 

C:\>expdp system directory=my_export_dir dumpfile=export_cw4ora.dmp schemas=CW4ORA 

Export: Release 10.1.0.4.0 - Production on Samedi, 30 Juillet, 2005 19:58

Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."EXPORT_CW4ORA": system/******** directory=my_export_dir dumpfile=export_cw4ora.dmp schemas=CW4ORA job_name=export_cw4ora
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1,590 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER
Processing object type SCHEMA_EXPORT/CLUSTER/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "CW4ORA"."AN_TIME" 18,93 MB 548220 rows
. . exported "CW4ORA"."EQP_ANAL" 4,237 MB 91370 rows
. . exported "CW4ORA"."JOB_ACTV" 8,704 MB 6321 rows
. . exported "CW4ORA"."EARL" 2,009 MB 121228 rows
. . exported "CW4ORA"."LATE" 4,072 MB 242456 rows
. . exported "CW4ORA"."S_DISC_PRI" 4,071 MB 242456 rows

I cut the output here and just show the bottom end

. . exported "CW4ORA"."WP_WORK_PERMIT" 0 KB 0 rows
. . exported "CW4ORA"."WP_WORK_PERMIT_EMPL" 0 KB 0 rows
. . exported "CW4ORA"."WP_WORK_PERMIT_INFO" 0 KB 0 rows
. . exported "CW4ORA"."WP_WORK_PERMIT_TYPE" 0 KB 0 rows

Master table "SYSTEM"."EXPORT_CW4ORA" successfully loaded/unloaded
Dump file set for SYSTEM.EXPORT_CW4ORA is:
C:\TEMP\EXPORT_CW4ORA.DMP
Job "SYSTEM"."EXPORT_CW4ORA" successfully completed at 20:06



Let' s invoke expdp with the options as discribed here above

 

C:\>impdp system directory=my_export_dir dumpfile=export_cw4ora.dmp schemas=CW4ORA job_name=export_cw4ora remap_schema=CW4ORA:TEST remap_tablespace=CW4ORA_DATA:TEST_DATA remap_tablespace=CW4ORA_INDX:TEST_INDX exclude=GRANT

 

Import: Release 10.1.0.4.0 - Production on Samedi, 30 Juillet, 2005 20:30

Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."EXPORT_CW4ORA" successfully loaded/unloaded
Starting "SYSTEM"."EXPORT_CW4ORA": system/******** directory=my_export_dir dumpfile=export_cw4ora.dmp schemas=CW4ORA job_name=export_cw4ora remap_schema=CW4ORA:TEST remap_tablespace=CW4ORA_DATA:TEST_DATA remap_tablespace=CW4ORA_INDX:TEST_INDX exclude=GRANT
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER
Processing object type SCHEMA_EXPORT/CLUSTER/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."AN_TIME" 18,93 MB 548220 rows
. . imported "TEST"."EQP_ANAL" 4,237 MB 91370 rows
. . imported "TEST"."JOB_ACTV" 8,704 MB 6321 rows
. . imported "TEST"."EARL" 2,009 MB 121228 rows
. . imported "TEST"."LATE" 4,072 MB 242456 rows
. . imported "TEST"."S_DISC_PRI" 4,071 MB 242456 rows
. . imported "TEST"."S_DISC_RATEP" 4,071 MB 242456 rows
. . imported "TEST"."S_ORD_QTY_VAL" 4,071 MB 242456 rows
. . imported "TEST"."SUPL_ITEMS" 6,675 MB 60614 rows
. . imported "TEST"."PO_ITEM_OTHER" 9,067 MB 390600 rows
. . imported "TEST"."PO_ITEMS" 7,504 MB 78120 rows
. . imported "TEST"."INV_ITEM_OTHER" 9,072 MB 390790 rows
. . imported "TEST"."INV_ITEMS" 8,358 MB 78158 rows
. . imported "TEST"."HIS_HO" 29,38 MB 101617 rows
. . imported "TEST"."PO_RMK" 21,09 MB 122980 rows
. . imported "TEST"."INVOICE_" 7,264 MB 43757 rows
. . imported "TEST"."INV_OTHER" 5,176 MB 218785 rows
. . imported "TEST"."PO_OTHER" 4,273 MB 180510 rows
. . imported "TEST"."PORDER_" 6,348 MB 36102 rows

I cut the output here and just show the bottom end

. . imported "TEST"."WIP_WO_EMPL" 0 KB 0 rows
. . imported "TEST"."WIP_WO_SHIFT" 0 KB 0 rows
. . imported "TEST"."WO_EMPL_ASSIGN" 0 KB 0 rows
. . imported "TEST"."WO_METERFDBK" 0 KB 0 rows
. . imported "TEST"."WP_WORK_PERMIT" 0 KB 0 rows
. . imported "TEST"."WP_WORK_PERMIT_EMPL" 0 KB 0 rows
. . imported "TEST"."WP_WORK_PERMIT_INFO" 0 KB 0 rows
. . imported "TEST"."WP_WORK_PERMIT_TYPE" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"TEST"."COSWINFILTER" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"TEST"."COSWINPIPE" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."EXPORT_CW4ORA" completed with 2 error(s) at 22:50

 

The error with regard to the packages coswinfilter and coswinpipe hasn' t anything to do with the import utility , it concerns a lack of privileges at dbms_pipe and v$session.


Let' s check if the object are owned by user test and that they are in the newly created tablespaces test_data and test_indx

 

SQL> column owner format a20

SQL> select owner,count(*) from dba_segments where tablespace_name='TEST_DATA' group by owner;

OWNER COUNT(*)
TEST 367

SQL> select owner,count(*) from dba_segments where tablespace_name='TEST_INDX' group by owner;

OWNER COUNT(*)
TEST 1173

 

Example 2 : Import accross a network link.

 

A nice feature if you ask me, the traditionnal approach to transfer data from one  database to another with the exp / imp utilities was



1. export to a dumpfile to get the data out of the source database

2. transfert the dumpfile with an os utility towards the target platform

3. import the dumpfile into the target database



With the datapump we can do it all in 1 shot. read from source and write into target. In the target database we create a public database link

 

SQL > create public database link to_remote_db using 'tns';

 

On the target platform

 

$ export ORACLE_SID=MYSID

$ORACLE_HOME/bin/impdp system/secret_password network_link=to_remote_db schemas=my_schema 

All Rights Reserved © 2024