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.
2) Import Utility
Similar to exp, used for putting data into database. Type imp help=y to get the list of all parameters
Detailed Explation of Each Parameter.
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>;
Detailed Explanation of Each Parameter