Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files.
When and why to perform a full database export :-
1) Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).
2) When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.
3) To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.
4) If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.
Privileges Required:-
exp_full_database and imp_full_database.
Types of exports and Imports along with syntax:
1) Exporting the complete database:
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export syntax
]$ exp username/password@listener full=Y directory=<directory name> dumpfile=<file name> logfile=<file name>;
2) Exporting the table:
The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export
]$ expdp username/password@listener table=<table name>
directory=<directory name> dumpfile=<file name>
logfile=<file name>;
3) Schema level exporting:
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export
]$ expdp username/password@listener schemas=<name>
directory=<directory name> dumpfile=<file name>
logfile=<file name>;
4) Importing the database:-
The FULL parameter indicates that a complete database import is required. The following is an example of the full database import syntax.
]$ impdp username/password@listener full=Y directory=<directory name> dumpfile=<file name> logfile=<file name>;
5) Importing the table:-
The TABLES parameter is used to specify the tables that are to be imported. The following is an example of the table import
]$ impdp username/password@listener table=<table name>
directory=<directory name> dumpfile=<file name>
logfile=<file name>;
6) Importing the schema:-
The OWNER parameter of imp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema import
]$ impdp username/password@listener schemas=<name>
directory=<directory name> dumpfile=<file name>
logfile=<file name>;
7) Exclude and Include:-
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.
The following code shows how they can be used as command line parameters
expdp username/password@listener schemas=<name> include=TABLE:"IN ('EMP', 'DEPT')" directory=<directory name> dumpfile=<file name>
logfile=<file name>;
expdp username/password@listener schemas=<name> exclude=TABLE:"= 'BONUS'" directory=<directory name> dumpfile=<file name>
logfile=<file name>;
No comments:
Post a Comment