Oracle DBA FAQ - Loading and Exporting Data

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

What Are Data Pump Export and Import Modes?

Data pump export and import modes are used to determine the type and portions of database to be exported and imported. Oracle 10g supports 5 export and import modes:

Full: Exports and imports a full database. Use the FULL parameter to specify this mode.
Schema: Enables you to export and import all objects that belong to a schema. Use the SCHEMAS parameter to specify this mode. This is the default mode.
Table: Enables you to export and import specific tables and partitions. Use the TABLES parameter to specify this mode.
Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TABLESPACES parameter to specify this mode.
Tablespace: Enables a privileged user to move metadata from the tables within a specified set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACES parameter to specify this mode.

How To Estimate Disk Space Needed for an Export Job?

If you just want to know how much disk space for the dump without actually exporting any data, you can use the ESTIMATE_ONLY=y parameter on the expdp command. The following tutorial exercise shows you how a system user wants to see the disk space estimates on a full database export:

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>expdp SYSTEM/fyicenter FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  SYSTEM/**** FULL=y
  ESTIMATE_ONLY=y

Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
.  estimated "FLOWS_020100"."WWV_FLOW_PAGE_PLUGS"      42 MB
.  estimated "FLOWS_020100"."WWV_FLOW_STEP_ITEMS"      27 MB
.  estimated "FLOWS_020100"."WWV_FLOW_STEP_PROCESSING" 16 MB
......
.  estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"         0 KB
.  estimated "TSMSYS"."SRS$"                            0 KB
Total estimation using BLOCKS method: 169.8 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed

Now you know that you need 170 MB disk space to export the entire data base.

Oracle also records the screen output in a log file called export.log at \oraclexe\app\oracle\admin\XE\dpdump.

How To Do a Full Database Export?

If you are ready to do a full database export, you can use the FULL=y parameter on the expdp command, as shown in the following tutorial exercise:

>expdp SYSTEM/fyicenter FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  SYSTEM/**** FULL=y 
Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 169.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
......
. . exported FLOWS_020100.WWV_FLOW_PAGE_PLUGS      32.51MB
. . exported FLOWS_020100.WWV_FLOW_STEP_ITEMS      21.68MB
. . exported FLOWS_020100.WWV_FLOW_STEP_PROCESSING 11.17MB
......
Master table "SYSTEM"."SYS_EXPORT_FULL_01" unloaded
**********************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\EXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...