Friday, July 11, 2014


Learning  Datapump:

SQL> create directory export as '/u01/work/export';
SQL> grant read,write on directory export to system;

expdp userid=system/manager dumpfile=expdp.dmp logfile=expdp.log full=y                  directory=exportparallel=4

if using "exp" watch out if some columns  with property 1073741824 (see the column value PROPERTY in COL$ for column COL001) are columns with defaults that are not saved in the column. If so the imp tool is going to fail ORA-1400 During Import of Export Dump Written in Direct Path Mode. So the work around is using expdp or not using Direct Path mode during the export.

select col#, name, property 
from   col$ 
where  obj# in (select object_id 
                from   dba_objects 
                where  owner = 'TEST' and 
                       object_name = 'A_TAB'); 


Another trick to extract an schema (indexes in this case) or part of a schema using imp tool:
imp system/manager parfile=indexes.par

[oracle@rac1 indexes]$ more indexes.par
FILE=../dat1.dmp
FEEDBACK=100000
BUFFER=100000000
LOG=indexes.log
INDEXFILE=indexes.sql
FROMUSER=DRAW_SUM_TEST,REPORTING,ADMIN,GGE,LOTTERY,GENDBA
TOUSER=DRAW_SUM_TEST,REPORTING,ADMIN,GGE,LOTTERY,GENDBA

IGNORE=Y



No comments: