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.
Imports/Exports Modes:--
1)Full Export/Import
2)Tablespace
3)User and
4)Table (With partition).
Export and Import Modes
The Export and Import utilities support four modes of operation:
Full: Exports and imports a full database. Only users with the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles can use this mode. Use the FULL parameter to specify this mode.
Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACE parameter to specify this mode.
User: Enables you to export and import all objects that belong to you (such as tables, grants, indexes, and procedures). A privileged user importing in user mode can import all objects in the schemas of a specified set of users. Use the OWNER parameter to specify this mode inExport, and use the FROMUSER parameter to specify this mode in Import.
Table: Enables you to export and import specific tables and partitions. A privileged user can qualify the tables by specifying the schema that contains them. Use the TABLES parameter to specify this mode.
*** Before executing the exp/imp scripts use the nohup command in $ prompt.***
$nohup
If the size of the undo tablespace is small then change the size of the undo tablespace i.e increase the size of the undo tablespace.
Options For Exp Utility:--
Exp Option Default Value Description
----------------------------------------------------------------------------------------------------------------------
userid =======>> username/password
buffer ========>> Specifies the size, in bytes, of the buffer (array) used to insert the data OR Specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.
"buffer_size = rows_in_array * maximum_row_size"
compress ======>> Y When “Y”, export will mark the table to be loaded as one extent OR import into one extent (Y).
consistent =======>> N Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error. Or cross-table consistency(N).
constraints ========>> Y Specifies whether table constraints should be exported with table data.
direct ===========>> N Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance (Direct=Y).
File ===============>> The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.
Full =========>> N The entire database is exported,if full=y.
grants ======>>> Y Specifies object grants to export.
log =========>>> log file of screen output
owner ==========>>> Only the owner’s objects will be exported.
parfile ===========>>> parameter filename .
query ==========>>> Allows a subset of rows from a table to be exported, based on a SQL,where clause.
rows ==========>>> Y export data rows. If (rows=N),it will export the objects without data.
statistics ===========>>> ESTIMATE Indicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.
tables =======>>> Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.
OR
list of table names.
You can see all the parameters(options) of the exp utility with the help of this command:--
$exp help=Y
For Imp Utility:--
Options:--
USERID =========>> username/password
BUFFER ========>> size of data buffer
IGNORE =========>> ignore create errors (N),Mostly Ignore=Y is taken.
If IGNORE=y, object creation errors are not reported. The database object is not replaced. If the object is a table, rows are imported into it. Note that only object creation errors are ignored; all other errors (such as operating system, database, and SQL errors) are reported and processing may stop.
COMMIT=======> y prevents rollback segments from growing inordinately large and improves the performance of large imports. COMMIT=y is advisable if the table has a uniqueness constraint
default commit========>> N.
FILE ==========>> input files (EXPDAT.DMP).
FILESIZE =============>> maximum size of each dump file.
FROMUSER ==========>> list of owner usernames.
TOUSER ============>> list of usernames.
LOG ========>>> log file of screen output.
PARFILE =======>>> parameter filename.
These above are the important parameters of Exp/imp utility options.
Imports/Exports Modes:--
1)Full Export/Import
2)Tablespace
3)User and
4)Table (With partition).
Export and Import Modes
The Export and Import utilities support four modes of operation:
Full: Exports and imports a full database. Only users with the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles can use this mode. Use the FULL parameter to specify this mode.
Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACE parameter to specify this mode.
User: Enables you to export and import all objects that belong to you (such as tables, grants, indexes, and procedures). A privileged user importing in user mode can import all objects in the schemas of a specified set of users. Use the OWNER parameter to specify this mode inExport, and use the FROMUSER parameter to specify this mode in Import.
Table: Enables you to export and import specific tables and partitions. A privileged user can qualify the tables by specifying the schema that contains them. Use the TABLES parameter to specify this mode.
*** Before executing the exp/imp scripts use the nohup command in $ prompt.***
$nohup
If the size of the undo tablespace is small then change the size of the undo tablespace i.e increase the size of the undo tablespace.
Options For Exp Utility:--
Exp Option Default Value Description
----------------------------------------------------------------------------------------------------------------------
userid =======>> username/password
buffer ========>> Specifies the size, in bytes, of the buffer (array) used to insert the data OR Specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.
"buffer_size = rows_in_array * maximum_row_size"
compress ======>> Y When “Y”, export will mark the table to be loaded as one extent OR import into one extent (Y).
consistent =======>> N Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error. Or cross-table consistency(N).
constraints ========>> Y Specifies whether table constraints should be exported with table data.
direct ===========>> N Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance (Direct=Y).
File ===============>> The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.
Full =========>> N The entire database is exported,if full=y.
grants ======>>> Y Specifies object grants to export.
log =========>>> log file of screen output
owner ==========>>> Only the owner’s objects will be exported.
parfile ===========>>> parameter filename .
query ==========>>> Allows a subset of rows from a table to be exported, based on a SQL,where clause.
rows ==========>>> Y export data rows. If (rows=N),it will export the objects without data.
statistics ===========>>> ESTIMATE Indicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.
tables =======>>> Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.
OR
list of table names.
You can see all the parameters(options) of the exp utility with the help of this command:--
$exp help=Y
For Imp Utility:--
Options:--
USERID =========>> username/password
BUFFER ========>> size of data buffer
IGNORE =========>> ignore create errors (N),Mostly Ignore=Y is taken.
If IGNORE=y, object creation errors are not reported. The database object is not replaced. If the object is a table, rows are imported into it. Note that only object creation errors are ignored; all other errors (such as operating system, database, and SQL errors) are reported and processing may stop.
COMMIT=======> y prevents rollback segments from growing inordinately large and improves the performance of large imports. COMMIT=y is advisable if the table has a uniqueness constraint
default commit========>> N.
FILE ==========>> input files (EXPDAT.DMP).
FILESIZE =============>> maximum size of each dump file.
FROMUSER ==========>> list of owner usernames.
TOUSER ============>> list of usernames.
LOG ========>>> log file of screen output.
PARFILE =======>>> parameter filename.
These above are the important parameters of Exp/imp utility options.
No comments:
Post a Comment