Sunday, January 17, 2010

Export/Import Questions and answers

416 :: How To Export Your Own Schema?
If you have a non-system user account and you want to export all data objects in the schema associated with your account, you can use the "expdp" command with the SCHEMAS parameter. Running "expdp" command with a non-system user account requires a directory object granted to this user account. The following tutorial exercise shows you how to define a directory object and export a schema:

>mkdir oraclexehr_dump

>cd oraclexeapporacleproduct10.2.0serverBIN

>sqlplus /nolog
SQL> connect SYSTEM/globalguideline

SQL> CREATE DIRECTORY hr_dump AS 'oraclexehr_dump';
Directory created.

SQL> GRANT READ ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> quit

>expdp hr/globalguideline SCHEMAS=hr
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=schema.log
417 :: How To Export Several Tables Together?
If you don't want to export the entire schema and only want to export several tables only, you can use the "expdp" command with the "TABLES" parameter as shown in the following tutorial exercise:

>cd oraclexeapporacleproduct10.2.0serverBIN

>expdp hr/globalguideline TABLES=employees,departments
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log

Starting "HR"."SYS_EXPORT_TABLE_01": hr/********
TABLES=employees,departments DIRECTORY=hr_dump
DUMPFILE=tables.dmp
LOGFILE=tables.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTI...
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF...
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TAB...
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
Master table "HR".
418 :: What Happens If the Imported Table Already Exists?
If the import process tries to import a table that already exists, the Data Pump Import utility will return an error and skip this table. The following exercise shows you a good example:

>cd oraclexeapporacleproduct10.2.0serverBIN
>impdp hr/globalguideline TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log

Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent
metadata and data will be skipped due to table_exists_action
of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
......
419 :: How To Import One Table Back from a Dump File?
If you only want to import one table back to the database, you can use a dump file that was created by full export, schema export or a table export. The following tutorial exercise shows you how to import the "ggl_links" table from a dump file created by a schema export:

>cd oraclexeapporacleproduct10.2.0serverBIN

>sqlplus /nolog
SQL> connect HR/globalguideline

SQL> DROP TABLE ggl_links;
Table dropped.

SQL> exit;

>impdp hr/globalguideline TABLES=ggl_links DIRECTORY=hr_dump
DUMPFILE=schema.dmp LOGFILE=tables.log

Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=ggl_links
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=tables.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."ggl_LINKS" 6.375 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTI...
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TAB...
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed.
420 :: What Are the Original Export and Import Utilities?
Oracle original Export and Import utilities are standalone programs that provide you a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.

An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.

Export and Import utilities are now being replaced by Data Pump Export and Import utilities in Oracle 10g. But you can still use them.



 421 :: How To Invoke the Original Export Import Utilities?
If you really want to run the original export import utilities, you can still go to "bin" directory of the Oracle server path and run the "exp" or "imp" command. The tutorial exercise below tells you how to run the export and import utilities in help modes:

>cd oraclexeapporacleproduct10.2.0serverBIN

>exp help=y
You can let Export prompt you for parameters by entering the
EXP command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP
command followed by various arguments. To specify parameters,
you use:

Format: EXP KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned
table
......

>imp help=y
......

No comments: