exp index=N

[prakashs43]

Hi ,

We are having short of space so , i am planning to take exp with index=N option .

in case schema is dropped . how can i rebuild indexes with export dump taken ( with INDEX=N ) option

I want to confirm if index structure are still available in ethe export dump so that i can execute below command after imp from above export.

ALTER INDEX xyz REBUILD ;

How can i test this ? sorry for such a simple question

[marist89]

Regular exp doesn't export the data of the indexes, just the DDL to re-create them.

[Mr.Hanky]

This is a perfect example of why (I always say) you should take more than you need, or THINK you need.

As Jeff pointed out exporting indexes will cost next to nothing since you are only grabbing the DDL to create them.

If you dropped the schema and did not export the indexes you are in a world of shiete.
Can you pull the index DDL from another (developement) environment?

MH

[prakashs43]

Thanks Marist and Hanky

Just confirming
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
so if i do
exp user1/user1 full=Y rows=Y indexes=N file=xyz.dmp

i would not get DDL for index creation when i import

imp file=xyz full=Y ......

now i am just working out how i can create space for export in Unix box .

1) i have to take exp of full database , which is taking 20GB
2) i have just 24 GB free in our Unix box
3) but we have remote server where we can ftp our export dump .
but

can i directly export/import to remote server using exp /imp command

presently i am using following syntax

BACKUPDIR=/export/home
export BACKUPDIR

. $HOME/.profile
mknod /tmp/exp_pipe p
compress < /tmp/exp_pipe > $BACKUPDIR/ful_exp.dmp.Z &
exp file=/tmp/exp_pipe parfile=/export/home/oracle/ravy/weekly/ful_exp.par log=/export/home/oracle/ravy/weekly/ful_exp.log
. /export/home/oracle/ftp_exp_dmp.sh
rm /tmp/exp_pipe


and i am ftping this to remote host (ftp_exp_dmp.sh) .

FTP_DIR=oracle

HOST=172.13.21.14

ftp -n -v -i $HOST < user ravy xyz
cd $FTP_DIR
lcd $BACKUPDIR
bin
mput *.Z
ascii
put ful_exp.log*
close
quit
!

do anyone have script to directly export to remote node using ftp

thanks for your help ....

[marist89]

sure, read about nfs mounts

[adewri]

Originally posted by prakashs43
exp user1/user1 full=Y rows=Y indexes=N file=xyz.dmp

Better export using indexes=y, just import using indexes=n if you dont want the indexes.

Later when you need the indexes, import again using indexes=y and they will be created.

[JMac]

you are only grabbing the DDL to create them.

When you import this .dmp does it create the indexes as it inserts the rows into the target tables?

Is it quicker/better to import into a DB with no indexes defined, then run CREATE INDEX scripts?

[marist89]

quicker, yes. better, depends. If you want to change something about the index, then no, it is better to pre-create it than to let import create it wrong.

[Mr.Hanky]

I thought import loaded the table then built the index. Isn't that why you get failed to build errors on import from tme to time?

MH

[marist89]

Originally posted by Mr.Hanky
I thought import loaded the table then built the index. Isn't that why you get failed to build errors on import from tme to time?

MH

sure, that's the default behaviour.