Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Oracle 9 imports/exports

Options
  • 25-11-2002 8:01pm
    #1
    hamster
    Registered Users Posts: 1,766 ✭✭✭


    Any handy oracle (8 or 9i) dba reading this willing to impart some tips?


    I have this database .dmp which contains a complete databse with a single user 'JOE'. Joe lives on a single tablespace 'JOEDATA'. On this tablespace 'JOEDATA' contains all data including indices. JOE has full privileges.

    I wish to import this database onto a clean database except I wish to separate the indicies from the data. Lets say I want the indicies on JOEINDX. I heard that one can import the dump file using the INDEXFILE= option which separates the data from the indices. Has anyone tried this before?

    eg. usually lazy way of importing the user JOE (data and indices combined):
    imp joe/abcdefg@joedatabase FROMUSER=joe TOUSER=joe FILE=joefile.dmp LOG=joefile.log


    Would this solution (solution A) seem fleasible?
    a) export the original database as normal (like above).
    b) Import the dump file using the INDEXFILE= option
    c) Edit the indexfile. Remove remarks and specify the correct tablespaces. (ie I'm assuming JOEINDX for the indices)
    d) Run this indexfile against the database, this will create the required tables in the appropriate tablespaces
    e) Import the table(s) with the IGNORE=Y option.



    The possible syntax for Solution A:

    a)
    exp joe/abcdefg@originaldatabase FROMUSER=JOE TOUSER=JOE FILE=joefile.dmp LOG=joefile_exp.log

    b)
    imp joe/abcdefg@newdatabase FILE=joefile.dmp LOG=joefile_imp1.log INDEXFILE=joeindices.idx
    I assume no data will be imported but the file containing index definitions will be created.

    c)
    Next I edit the indexfile 'joeindices.idx' to specify the correct tablespace where I want the indices to go...
    ie JOEINDX, is this possible as this is normally used for tables?

    d)
    imp joe/abcdefg@newdatabase FILE=joeindices.idx LOG=joefile_imp2.log
    This Creates indices (tables?) in correct JOEINDX tablespace? According to what I edit in the indexfile.

    e) imp joe/abcdefg@newdatabase FILE=joefile.dmp LOG=joefile_imp3.log IGNORE=Y
    This import the data into JOEDATA and doesn't import the indices again.



    Again I appreciate any tips or correct pointers in this.


Advertisement