Home » RDBMS Server » Server Utilities » Datapump and index building (Oracle 11g)
Datapump and index building [message #539357] Fri, 13 January 2012 19:01 Go to next message
ryandamu
Messages: 1
Registered: January 2012
Junior Member

Hi,

Currently we are using "exp and imp" utilities to unload from production and load into Dev server. While importing, we are following below steps

(1) Load only data [by specifying INDEXES=N in the par file]
(2) Unlock statistics
(3) Load indexes, other objects [by specifying ROWS=N]

After doing these steps, both data, indexes and others objects are loaded. To verify indexes, we are checking DBA_INDEXES.

DBA_INDEXES :
-------------

OWNER INDEX_NAME TABLE_NAME STATUS LAST_ANALYZED
----- ---------- ---------- ------ -------------
MYSCH CP_INDEX_1 CP_TABLE_1 VALID 14/JAN/12

Question :-

(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?

(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?

(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ?

(b) I am planning to implement this in two steps :

first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE

then, load data - CONTENT=DATA_ONLY.

Does this approach work ?

Thanks for your help.

Re: Datapump and index building [message #539358 is a reply to message #539357] Fri, 13 January 2012 19:34 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>(1) Does imp utility rebuild the indexes while loading data ?
INDEX is CREATEd after table data completes loading.
>or it simply takes the rows from dump and load into test system without building from scratch ?
no

>(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?
impdp help=yes
expdp help=yes
when all else fails Read The Fine Manual below
http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm

>(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ?
yes or accept default

>(b) I am planning to implement this in two steps :
>first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE
>then, load data - CONTENT=DATA_ONLY.
>Does this approach work ?
let us know after you complete this exercise.
Previous Topic: Expdp Hangs in Oracle 11g
Next Topic: Data Pump error ORA-31693
Goto Forum:
  


Current Time: Thu Mar 28 18:10:59 CDT 2024