Wednesday, November 26, 2008

Using DataPump Export/Import examples

I will present some expamples using DataPump.

1) Full export
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y

- Excluding a schema or a list of schemas

expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA')"


expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA','MYOTHERSCHEMA')"


expdp system include=SCHEMA:\"like 'TST%'\" directory=tmp dumpfile=tstexpuser.dmp full=y content=metadata_only


2) Exporting Schema to a dump file

expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics SCHEMAS=MYSCHEMA

3) Exporting Metadata Only

- Full Database
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp content=metadata_only full=y

-- Schema's

expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp content=metadata_only schemas=myschema

-- Excluding DBA Scheduler JOBS

expdp system directory=exp_dir content=metadata_only "exclude=procobj:\"in(SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))\"" schemas=STAGING,STARSAMA,STARFIN dumpfile=dwh_schemas_metadata.dmp logifle=dwh_schemas_metadata.log


4) Importing and remaping schema using dump file
impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA REMAP_SCHEMA=MYSCHEMA:OTHERSCHEMA DUMPFILE=mydump.dmp

5) Importing schemas directly from an other database using dblink

a) impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA,MYSCHEMA1 NETWORK_LINK=MYDBLINK

b) impdp system DIRECTORY=MYDIR SCHEMAS=MYSCHEMA NETWORK_LINK=MYLINK REMAP_SCHEMA=MYSCHEMA:MYSCHEMA_NEW CONTENT=METADATA_ONLY remap_tablespace=MYTBS1:MYNEWTBS,MYTBS2:MYNEWTBS

6) Export Schemas and import with remaping tablespace (Some more parameters are used)

a) Take the export

expdp system directory=EXPORT_DIR dumpfile=mydump.dmp logfile=mydump.log estimate=statistics parallel=2 job_name=mydump_exp schemas=MYSCHEMA1,MYSCHEMA2

b) Import and remaping default tablespace and index tablespaces

impdp system directory=export_dir schemas=MYSCHEMA1,MYSCHEMA2 dumpfile=mydump.dmp logfile=mydump.log job_name=mydump_imp parallel=2 remap_tablespace=myschema1_data:users,myschema1_index:users,myschema2_data:users,myschema2_index:users

7) Export table from a schema and import to an other schema

expdp system directory=export_dir dumpfile=mytable.dmp logfile=mytable.log ESTIMATE=statistics tables=myschema.mytable

impdp system directory=export_dir tables=myschema.mytable remap_schema=myschema:mynewschema dumpfile=mytable.dmp

8) Import Transportable Tablespace with Remapping Schema also objects stats are excluded during the import

impdp system dumpfile=mydump.dmp DIRECTORY=my_dir remap_schema=ORIG_SCHEMA:NEW_SCHEMA EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS TRANSPORT_DATAFILES='/u02/oradata/myfile.dbf'

No comments:

Post a Comment