Import Problems
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Import Problems

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    Could anyone help

    I am trying to speed up the import of a database (it usually takes over 8 hours).

    I am using the following command to perform the import, it runs, however when I do a count on the importing database the indexes are being created, I specifically don't want it to do this.

    imp73 username/password@database file=f:\oradba\admin\exp\fullexport.dmp indexes=n full=y buffer=300000000 commit=n ignore=y

    Could anyone help

    Many thanks

    Alison

  2. #2
    Join Date
    Mar 2001
    Posts
    18
    Alison,

    I beleive that when you do a full import (full=y) this will override the indexes=n parameter with indexes=y. One way to overcome this would be to do a full export and bring in the tables a schema at a time. You could then specify indexes=n

    Any use?

    Dave

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Thanks for your message,

    However in this case importing schema by schema wont work.

    What I am doing is importing from production to test, therefor the schema's don't exist in the target yet.

    I could create them and then do the import but I am looking for a cleaner solution.

    Many thanks

    Alison

  4. #4
    Join Date
    Nov 2000
    Posts
    89
    Writing a shell script to import the tables 1 by 1 wouldn't be that bad. You just have to output the tables and paste them into a file- then paste in the appropriate imp command that excludes the indexes. This should be too much of an effort.

    [url]http://www.timonions.com/[/url]

    has some reverse engineering scripts that will spit out the table names for 1 or all users.

    Nobody said being a DBA was fun :-)


  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Thanks for that suggestion

    Is there really no way of doing a full import but specifying indexes=n

    If I use a parfile to specify full and then outside the parfile specify indexes=n would that work (I read that the last option read overwrites previous ones)

    Any advice would be great

    Many thanks for your time

    Alison

  6. #6
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Could anyone give me any advice on this problem.

    Many thanks in advance

    Alison

  7. #7
    Join Date
    Nov 2000
    Posts
    89
    Alison:

    This page sez just about all that can be said about import. Before you get frusterated- read the docs carefully and expereiment with a test DB.

    [url]http://www-rohan.sdsu.edu/doc/oracle/server803/A54652_01/import.htm[/url]


  8. #8
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi Friends.

    Thank you all for your replies.

    I have an extra query to add to the above information.

    I ran the import using the following parameters
    imp73 username/password@database file=f:\oradba\admin\exp\fullexport.dmp indexes=n full=y buffer=300000000 commit=n ignore=y

    I understood that the full=y overwrites the indexes=n

    I did get indexes imported however not all of them.

    At last import with indexes=y I imported 756

    With indexes=n I only got 369

    All the tables imported correctly
    I got no errors in my import log either time

    This is running on a test db, which is a replica of production.

    I would be grateful if anyone could shed any light on this

    Regard

    Alison

  9. #9
    Join Date
    Mar 2001
    Posts
    12

    RE: import question

    Hi, Alison

    Can you check what kind of indexes created when you specify
    indexes=n?

    Because when import utility will create system generated indexes such as LOB index, OID index and unique constraint index (include primary key) regardless of the setting of INDEXES parameter. Set INDEXES=N only disable the creation of user generated indexes.

    I think this is the reason you got different result when you use different setting.


    Tang Qiang

  10. #10
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi Tang,

    Many thanks for your help, this makes much more sense to me now.

    I have tried many different way's of performing the import.

    1. Full
    2. Partial with indexes=n then running the createindex script to finish the job.
    3. Partial with fromuser touser indexes=n then createindex.

    All of these work but in total come back to roughly the same timings.

    Can anyone suggest another option which may speed things up.

    Cheers

    Alison

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