Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 4 April 2012

Import/Export Utility in Oracle 9i

An export is a logical backup of the database. The export utility copies the data and database definitions to a binary operating system file. Export can only be used against an open database. Because export copies one table at a time, it can only guarantee read consistency for individual tables. Read consistency between tables is not guaranteed. For a read consistent export of the entire database, changes to the database must stop during the export. This can be enforced by closing the database and then reopening it in RESTRICT mode.
Parameter Listing:
exp help=yes
imp help=yes
Simple Export command:
exp hr/hr file=c:\shahid\shaan.dmp log=c:\shahid\shaan.log tables=shaan rows=yes indexes=no
exp hr/hr file=c:\shahid\shaan.dmp log=c:\shahid\shaan.log tables=(shaan,employees)

exp userid=hr/hr@shaan parfile=c:\shahid\export.txt
before executing create export.txt parafile on the location c:\shahid\export.txt having

To execute an incremental, cumulative or complete export:
exp username/password  inctype=incremental file=export_file_name

Export the file into multiple dump file:
exp hrms/hrms@orcl3 file=c:\shahid\hrms01.dmp, c:\shahid\hrms02.dmp,c:\shahid\hrms03.dmp log=c:\shahid\hrms.log filesize=50mb;
Note: In Oracle 10g autogenerate file name with %U postfix
expdp hrms/hrms@orcl3 dumpfile=expdir:hrms%U.dmp log=c:\shahid\hrms.log filesize=50mb;
Query using in Export:
exp HRMS/HRMS@ORCL3 tables=PAY_PAYMENT_MASTER query="""where PAYSLIP_NUMBER=5000""" file=c:\shahid\payslip_21853.dmp log=c:\shahid\payslip_21853.log
•The parameter QUERY cannot be specified for full, user, or tablespace mode exports.‎
•The parameter QUERY must be applicable to all specified tables‎
•The parameter QUERY cannot be specified in a direct path export (DIRECT=y)‎
•The parameter QUERY cannot be specified for tables with inner nested tables.‎
•You cannot determine from the contents of the export file whether the data is the ‎result of a QUERY export.‎
Connection as Sysdba for export:
exp 'sys/oracle@ORCL3 as sysdba' tables=PAY_PAYMENT_MASTER query="""where PAYSLIP_NUMBER=5000""" file=c:\shahid\payslip_21853.dmp log=c:\shahid\payslip_21853.log
Export Session in Full Database Mode:
exp system/oracle@orcl3 FULL=y file=c:\shahid\hrms_full.dmp log=c:\shahid\hrms_full.log GRANTS=y ROWS=y
Note: Entire database is exported with all grants and all data.
Export Session in User Mode:
exp hrms/hrms FILE=c:\shahid\hrms.dmp log=c:\shahid\hrms.log OWNER=hrms GRANTS=y ROWS=y COMPRESS=y‎
Note:User mode is appropriate for backup one or more database user data or move objects from one owner to another.
Export Session in Table Mode: Export Table from different schema:
exp system/oracle@orcl3 FILE=c:\shahid\table.dmp log=c:\shahid\table.log TABLES=(hr.employees,hrms.pay_payment_master) GRANTS=y INDEXES=y
Export Table from same Schema:
exp hrms/hrms@orcl3 FILE=c:\shahid\table.dmp log=c:\shahid\table.log TABLES=(pay_employee_personal_info, pay_payment_master) GRANTS=y INDEXES=y
Export Table Using Pattern Maching:
exp system/oracle@orcl3 FILE=c:\shahid\misc.dmp log=c:\shahid\misc.log TABLES=(hrms.%e%)‎
exp hrms/hrms@orcl3 FILE=c:\shahid\misc.dmp log=c:\shahid\misc.log TABLES=(emp%)
Exporting a table with a specific partition:
exp hrms/hrms@orcl3 TABLES=pay_payment_master:m rows=y
Exporting a table with composite partition:
exp hrms/hrms@orcl3 TABLES=(emp:m, emp:sp4) ROWS=y
Note: Exports in table mode do not include cluster definitions, so data is unclustered in table mode.
Partitioned a Database Migration
imp dba/password FILE=full FULL=y
Partitioned a Database Migration: Scheam Level
exp dba/password OWNER=scheman FILE=scheman
imp dba/password FILE=scheman FROMUSER=scheman TOUSER=scheman IGNORE=y
Use of FROMUSER Command:
imp SYSTEM/password FROMUSER=scott TOUSER=joe TABLES=emp‎
imp SYSTEM/password FROMUSER=scott, fred TOUSER=joe, ted
imp SYSTEM/password FILE=dba.dmp FROMUSER=scott TABLES=(dept,emp)‎
Import of Tables Exported by Another User:
imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp - ‎TABLES=(unit, manager)‎
Parameter File Method:
‎imp SYSTEM/password PARFILE=params.dat
The params.dat file contains the following information:‎
TABLES=(unit, manager)‎
Import of Tables from One User to Another:
imp SYSTEM/password FILE=scott.dmp FROMUSER=scott TOUSER=blake TABLES=(*)‎
Import of Using Pattern Matching to Import Various Tables:
imp SYSTEM/password FROMUSER=scott FILE=scott.dmp TABLES=(%d%,b%s)‎

How to Schedule Export:
As a minimum the following export schedule should be used if you have any other alternative backup such as RMAN then you can take export as a backup for Weekly/Monthly Full export Schedule.
Once the Full export is taken, the DBA can remove previous backups. This should be implemented using automated procedures.
The expired exports would be deleted as before. This should also be implemented using automated procedures.
All export procedures should be logged so that they can be reviewed. Some data corruption problems, such as un-readable characters in a record, will only be caught by the export process.
Exports of individual tables can be taken prior to major operations such as bulk data loads to provide for individual table recovery.

Keyword used with Export
BUFFER: Buffer is the number of bytes used to fetch data rows. This will determine the number of rows fetched at a time. The formula for buffer is buffer size = (rows in array) * (maximum row size). The default is OS dependent. If you specify zero, the Export utility fetches only one row at a time. Tables with LONG, LOB, BFILE, REF, ROWID, LOGICAL ROWID, DATE, or type columns are fetched one row at a time.
COMPRESS: Compress will cause export to resize the initial extents for all tables so they will import into one initial extent. The default value is YES.
Note: You can specify the COMPRESS parameter only when you export, not when you import. The Export utility is what generates the data definitions, including the storage parameter definitions. If you specify COMPRESS=Y when you export, you can import the data in consolidated form only.
Note: LOB data is not compressed. For LOB data, the original values of initial extent size and next extent size are used.
CONSISTENT: If consistent is set to YES export will make the export consistent to a single point in time. A rollback segment will be retained for the duration of the export. The default value in NO. Setting this value to YES could cause rollback segments to grow very large if many changes are happening to the database.
CONSTRAINTS: If constraints is set to YES constraints will be exported. The default value is YES.
DIRECT: Setting DIRECT=Y will cause export to read data directly, without using the public buffer cache or the SQL command processing layer. The default value is NO.
FEEDBACK: When feedback is set to a non-zero number export will display a dot every time that many rows are exported. The default value is 0 which gives not feedback.