Database Technologies‎ > ‎Oracle‎ > ‎

Data Moving

Many a times it is required to migrate data, mostly for uat refresh
where data from production machine is copied to developers machines
for testing purposes.  It could be from one from one table to another, from one
schema to other schema or from one database to another etc.
 
These are the common utilities for moving data
 
1) Export/ Import
2) Tranportable tablespace
3) Datapump (10g)
4) Sql Loader

     1) Export Utility :
         exp is the utility used for export and is located at ORACLE_HOME\bin directory.
         For getting information about the utility and all its arguments type in exp help=y,this show output as
         shown below.
 
 
Detailed Explation of Each Parameter.
 
 buffer  Size of the buffer to fetch data, default is system dependent.
 grants  has value Y/N indicates whether grants on the database objects  are to be exported, default is Y
 indexes  has value Y/N indicates where indexes on tables will be exported, default is Y
 direct  has values Y/N, Y directs to bypass buffer cache thereby improving performance, default is N
 log  value is the file name, where it writes the output that is splashed on the screen to file
 rows  has value Y/N, N indicates only the structure will be exported and not  the rows, default is Y
 consistent  This is usefull when the the table being exported is being modified, Y indicates to maintain  read consistent
 full  has value Y/N, specifies whether to export full database, default is N
 tables  A list of tables to be exported.
 triggers  has value Y/N, specifies whether to export the triggers associated with the tables, default is Y
 statistic  whether analyze command for the exported objects should be written to the export dump file values are COMPUTE,ESTIMATE, and N
 constraints  has value Y/N, whether to export the constraints associated with the table, default is N
 feedback  To display the number of exported rows, default is 0 for no display
 query  A where clause for partial export of the tables
 recordlength  As oracle says this is to specified for 10 records size in bytes. This is normally not required when export and import is to be done between similar operating system but when different os are to be used for export and import then we have to specify this
 
 
      2) Import Utility
        Similar to exp, used for putting data into database. Type imp help=y to get the list of all parameters
 
 
 
Detailed Explanation of Each Parameter
 
 buffer  size of the buffer to fetch data
 file  name of the file to be imported
 show  just list the file contents.
 ignore  Y/N value, to ignore error encountered during execution of the create commands, default is N
 grants  Y/N value, to indicate whether grants on database to be imported or not, default is Y
 indexes  Y/N value to indicate whether indexes on tables are to be imported, default is Y
 rows  Y/N values whether rows are to be imported, default is Y
 fromuser  fromuser specifies the  list of owner whose objects were exported in dumpfile
 touser  specifies into which account the objects are to be imported
 tables  A list of tables to be imported, here we could use the wildcards % and _
 commit  This specifies whether to commit the array data when buffer data is imported in the table, default is N.For large tables we have to specify commit=Y, otherwise we may get error pertaining to Undo size
 
 
 
To prevent any error in data while importing the above exported data, there is sequence
followed while exporting the data. Objects in database are exported in following order

1. Type definitions
2. Table definitions
3. Table data
4. Table indexes
5. Integrity constraints, views, procedures, and triggers
6. Bitmap, function-based, and domain indexes
 
 
Lets start exporting...
 
  
To Export the Whole database.
 
exp file=<file_name.dmp> log=<log_name.log> full=y statistics=none feedback=<value> direct=y
 
To Export Particular Schema of a database.
 
exp file=<file_name.dmp> log=<log_name.log> owner=<schema_name> statistics=none feedback=<value> direct=y

To Export Particular Table of a database.
 
exp file=<file_name.dmp> log=<log_name.log>  tables=(‘table1’,’table2’,…) statistics=none feedback=<value> direct=y
 
To Export Using where clause, for varchar character in datatype
 
exp file=exp.dmp log=exp.log tables=('OWNER.TABLE1') query=\"where COLUMEN=\'<VALUE>\'\"  statistics=none feedback=100
check
query=\"where Column=\'<value>\' \and\ Column2=\'<value>\'\"  statistics=none feedback=100
 
Importing
 

To Import the Whole database.
 
Make sure the tablespace structure exists with the required datafiles and sizes
then fire the import command
 
imp file=<file_name>.dmp log=<log_name>.log full=y feedback=<value> buffer=52428800 commit=y ignore=y

To Import Particular Schema of a database.
 
imp file=<file_name.dmp> log=<log_name.log> fromuser=<schema_name> touser=<schema_name> feedback=<value> buffer=52428800 commit=y ignore=y

To Import Particular Table of a database.
 
imp file=<file_name.dmp> log=<log_name.log> fromuser=<schema_name> touser=<schema_name> tables=(‘table1’,’table2’,…) feedback=<value> commit=y ignore=y

 

To Check the Speed of Import
 
select
   substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
   rows_processed,
   round((sysdate-
    to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/
    ((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from  
   sys.v_$sqlarea
where 
   sql_text like 'INSERT %INTO "%'
and 
   command_type = 2
and 
   open_versions > 0;
 
 
 
==
While importing a table with foreign key constraint.
 
Disable the foreign key constraint.
 
ALTER TABLE <TABLE_NAME> DISABLE CONSTRAINT <CONSTRAINT_NAME>;
 
Whilst the import is done with default option and ignore=y, it would throw error relating to
the constraint.
 
After import you could enable the constraint without checking for the existing rows as follows
 
ALTER TABLE <TABLE_NAME> ENABLE NOVALIDATE <CONSTRAINT_NAME>;
 
 
Comments