Tuesday, August 25, 2009

Data Pump Export modes and Interfaces

Oracle Data Pump provides 5 Export modes:-

1. Full Export Mode: A full export is specified using the FULL parameter and entire database is exported using this mode. EXP_FULL_DATABASE role required for this mode.
2. Schema Mode: A Schema export is specified using the SCHEMAS parameter and you can specify more than one schema separated by comma. EXP_FULL_DATABASE role is required if you want to export other schemas.
3. Table Mode: A Table export is specified using the TABLES parameter and you can export specified set of tables and dependency objects. EXP_FULL_DATABASE role is required to export objects to that are not in your own schema.
4. Tablespace Mode: A tablespace export is specified using the TABLESPACES parameter and you can export full/part tables contained in a specified set of tablespaces including dependent objects. Privileged users get all tables and Nonprivileged users get only the tables in their own schemas.
5. Transportable Tablespace Mode: A transportable tablespace mode is specified using the TRANSPORT_TABLESPACES parameter. EXP_FULL_DATABASE role is required for this mode.

You can interact with Data Pump Export using below interfaces
1. Command line – specify all parameters directly on command line.
2. Parameter file – Specify all command-line parameters in a file i.e. PARFILE
3. Interactive – This mode is enabled by pressing Ctrl+C during an export and you can run various commands.

Oracle Data pump uses four methods for moving data in and out.
1. Datafile copy - The copying the datafiles is the fatest method of moving data and this method uses TRANSPORT_TABLESPACE,TRANSPORTABLE parameters
2. Direct Path - This is the second fatest method and data will be moved with minimal interpretation and in this method the SQL layer of the database is bypassed.
3. External Tables - This method creates external table mapping to a datafile dump
4. Network Link import - This methold is used to retrive data from a remote database over the network link and no dump files involved in this method.

Please see the Data pump Export/Import related documents:
Click here to learn Oracle Data Pump Export/Import with examples.

Satishbabu Gunukula

1 comment:

  1. Love what you share and write for us. You really have an amazing blog. APJ Abdul Kalam Marathi