Search

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.***

Saturday, 2 February 2013

Discussion: Import/Export Utility?

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.
A simple automated script to export full database
SET ORACLE_SID=ORCL3
Column instnc new_value v_inst noprint
column instdate new_value v_instdate noprint
SELECT TO_CHAR(sysdate,'DDMMYYHH24') instdate FROM dual;
host exp system/oracle@orcl3 full=y consistent=y file=D:\BACKUP\dump&&v_instdate..dmp log=D:\BACKUP\dump&&v_instdate..log
exit
Full export/export:
The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export. 
Tablespace:
Use the tablespaces export parameter for a tablespace export.
User:
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the FROMUSER import parameter for a user (owner) export-import. 
Table:
Specific tables (or partitions) can be Exported/Imported with table export mode. Use the tables export parameter for a table Export/ Import mode. 
For more details example follow the other post: Using Import/Export
Yes, is possible. Here is an example:  
exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log
Export and Import Schema in the same and different database
EXP SYSTEM/SYSMAN@mujazorc.world OWNER=KAFAFIN FILE=H:\dump\kafafin_dump.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y LOG= H:\dump\kafafin.DMP.LOG
The above two commands will export the 'kafafin' schema from mujazorc database and import (restore) into the 'awaed' schema located on the same database.
But in the case if you want to import into new schema of new tablespace you need to quota 0 on old_tablespace and quota unlimited on new_tablespace before importing.
For Example: if you have schema KAFAFIN with tablespace KAFA_FIN_DBF and need to import into new schema AWAED of new tablespace AWAED then before import you must set quota 0 on old_tablespace and quota unlimited on new tablespace.
alter user AWAED quota 0 on KAFA_FIN_DBF quota unlimited on AWAED;
revoke unlimited tablespace from AWAED;
IMP SYSTEM/sysman@mujazorc.world FILE=D:\backup\kafafin_dump.DMP FROMUSER=KAFAFIN TOUSER=AWAED LOG=D:\backup\kafafin.DMP.LOG
after import you will need to set it again ALTER USER AWAED DEFAULT TABLESPACE AWAED
QUOTA UNLIMITED ON AWAED;
While in case if you want to import different schema on different database there is no need to set the quota 0 for old_tablespace
create tablespace AWAED datafile
'D:\ORACLE\ORA92\MUJAZORC\AWAED.DBF' size 20480m autoextend on;

create user AWAED IDENTIFIED BY AWAED
TEMPORARY TABLESPACE TEMP
DEFAULT TABLESPACE AWAED
QUOTA UNLIMITED ON AWAED;
GRANT CONNECT, RESOURCE TO AWAED;

EXP SYSTEM/SYSMAN@sadhan OWNER=ORAFIN FILE=F:\Dump\orafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:\Dump\orafin_16022015.log

IMP SYSTEM/sysman@MUJAZORC FILE=F:\Dump\orafin_16022015.DMP FROMUSER=ORAFIN TOUSER=AWAED LOG=F:\Dump\orafin_16022015.DMP.log

The above two commands will export the 'ORAFIN' schema from SADHAN database and import (restore) into the different schema (awaed) on the mujazorc database.
How we can use exp/ imp when we have 2 different Oracle database versions?
·        exp must be of the lower version
·        imp must match the target version
Before importing database objects, we have to drop or truncate the objects, if not, the data will be added to the objects. If the sequences are not dropped, the sequences will generate inconsistent values.  If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import.
By default, NO. Because there is no tablespace parameter for the import operation. However this could be done in the following manner:
·        (Re) create the table in another tablespace (the table will be empty)
·        Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated)
·        Modify this script to create the indexes in the tablespace we want
·        Import the table using IGNORE=y option (because the table exists)
·        Recreate the indexes
·        Eliminate database fragmentation
·        Schema refresh (move the schema from one database to another)
·        Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption)
·        Transporting tablespaces between databases
·        Backup database objects
·        Set the BUFFER parameter to a high value (e.g. 2M)
·        If you run multiple export sessions, ensure they write to different physical disks. 
·        Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes
·        Store the dump file to be imported on a separate physical disk from the oracle data files
·        If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import
·        Set the BUFFER parameter to a high value (ex. BUFFER=30000000 (~30MB)  ) and COMMIT =y  or set COMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.)
·        Use the direct path to import the data (DIRECT=y)
·        (if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init<SID>.ora file
·        (if possible) Set the LOG_BUFFER to a big value and restart oracle.
ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh..
IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).
ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter) while importing.
Import/Export Related Post:

0 comments:

Post a Comment