Oracle exp imp expdp imp
For oracle tables without any column of BLOB or CBLOB type, oracle normal users can use exp/imp command for exporting and importing oracle tables :
Suppose Oracle server name is “seora” ,
Oracle user “demo1” would like to export 2 tables : table1, table2 into a dump file called : “demo1-tables.dmp” in /public directory :
% exp userid=\”demo1@seora\” file=/public/demo1-tables.dmp tables=\(demo1.table1,demo1.table2 \) grants=y INDEXES=Y
Afterthat , another Oracle user “demo2” would like to import demo1.table1 and demo1.table2 under his oracle account schema (suppose demo2 OS user also has read access /public/demo1-tables.dmp ) :
% imp userid=\”demo2@seora\” file=/public/demo1-tables.dmp fromuser=demo1 touser=demo2 tables=\(table1,table2)
However, for oracle10g tables with CLOB , BLOB column, data pump utilities ( expdp , impdp) are required to use. However it is a server side process which can be only run by oracle system account in the oracle server. Normal user does not have enough rights to execute these data pump command and they cannot grant themselves read/write access on oracle data pump directory as well . As oracle system user “oracle” , do below :
First Logon as oracle in oracle server seora , and then create a Data pump directory “/app/dumpdir” in the oracle server “seora” :
% sqlplus /nolog
SQL> connect / as sysdba
SQL> CREATE DIRECTORY dumpdir AS ‘/app/dumpdir’;
SQL> GRANT read,write ON DIRECTORY dumpdir to demo1;
After making sure data dumpdir can be read/write by demo1 , now export table “demo1.table3” which contains CLOB column into a dump file “table3.dmp” :
% expdp demo1@seora tables=demo1.table3 directory=dumpdir dumpfile=table3.dmp logfile=expdp_table3.log
. . exported “demo1”.”table3″ 27.55 MB 8876 rows
Afterwards import table with CLOB column into Oracle user oracle2’s schema to become “demo2.table3” . This task is run as oracle system user
impdp system/PASSWORD tables=table3 directory=dumpdir dumpfile=table3.dmp logfile=impdp_table3.log remap_schema=demo1:demo2
Import: Release 10.2.0.4.0 – 64bit Production on Thursday, 12 May, 2011 23:32:10
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″: system/******** tables=table3 directory=dumpdir dumpfile=table3.dmp logfile=impdp_table3.log remap_schema=demo1:demo2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “demo2”.”table3″ 27.55 MB 8876 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 23:32:27
