When you export an entire database, this is referred to as a full export, and the resultant export file contains everything required to make a copy of your database. Unless restricted by filtering parameters, a full export consists of the following:
• All DDL required to re-create tablespaces, users, user tables, indexes, constraints, triggers, sequences, stored PL/SQL, and so on
• All table data (except the SYS schemas SYS, ORDSYS, or MDSYS)
A full export is initiated with the FULL parameter set to Y and must be done with a user that has DBA privileges or that has the DATAPUMP_EXP_FULL_DATABASE role granted to it. Here is an example of taking a full export of a database:
$ expdp mv_maint/Pa$$0rd123! directory=dp_dir dumpfile=full.dmp logfile=full.log full=y
Once you have a full export, you can use its contents to either re-create objects in the original database (e.g., in the event a table is accidentally dropped) or replicate the entire database or subsets of users/tables to a different database.
This next example assumes that the dump file has been copied to a different database server and is now used to import all objects into the destination database:
$ impdb mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=full.dmp logfile=fullimp.log full=y
Schema Level When you initiate an export, unless otherwise specified, Data Pump starts a schema-level export for the user running the export job.
User-level exports are frequently used to copy a schema or set of schemas from one environment to another.
The following command starts a schema-level export for the MV_MAINT user:$ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=mv_maint.dmp logfile=mv_maint.log You can also initiate a schema-level export for users other than the one running the export job with the SCHEMAS parameter.
The following command shows a schema-level export for multiple users: $ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=mv_maint.dmp logfile=mv_maint.log schemas=hsolo,hr With the schema-level import, there are some details to be aware of:
• No tablespaces are included in a schema-level export.
• The import job attempts to re-create any users in the dump file. If a user already exists, an error is thrown, and the import job continues.
• Tables owned by the user will be imported and populated. If a table already exists, you must instruct Data Pump on how to handle this with the TABLE_EXISTS_ACTION parameter.
Table Level
You can instruct Data Pump to operate on specific tables via the TABLES parameter.
For example, say you want to export the following:
$ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=tab.dmp tables=sales.inv, sales.inv_items Similarly, you can initiate a table-level import by specifying a table-level-created dump file: $ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=tab.dmp
You can also initiate a table-level import when using a full-export dump file or a schema-level export. To do this, specify which tables you want extracted from the full- or schema-level export:
$ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=full.dmp tables=sales.inv
Leave a Reply