Data Moving

2) Import Utility

Similar to exp, used for putting data into database. Type imp help=y to get the list of all parameters

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>;