exp index=N
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: exp index=N

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    exp index=N

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Regular exp doesn't export the data of the indexes, just the DDL to re-create them.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 ....

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    sure, read about nfs mounts
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width