Export Schema Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Export Schema Question

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Export Schema Question

    I want a backup of a schema from our development database. The developers are going to go in there and run programs that will change data. Then they want the data refreshed.


    I am planning on exporting the schema. The question is when it is time to refresh the data do I have to drop the user including contents or is there a way just to have the import truncate every table before in inserts the data??? My concern is that if I drop and recreate the user I will lose grants that the user has given to other schemas.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    You won't loose the grants. When the import imports the table, the following takes place....create the table, import the rows, create the indexes,triggers,constraints,give grants.
    If you take a schema export, you can effectivly go into the database and drop the user cascade, recreate the user and run the import and it will be just like it was before you started the process.

    also executing the following will help you see the light

    imp help=y
    Last edited by OracleDoc; 11-04-2004 at 09:26 AM.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    Thank you

    Thanks for the help.

  4. #4
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    If you dont want to drop the user each time you want to do a data refresh, you could do the following:
    (i) Truncate the tables within the schema
    (ii) Disable the constraints
    (iii) Drop the indexes
    (iv) Do the import with constraints=n, indexes=n ( this is mainly effective if there are really large tables
    (v) Enable the contraints
    (vi) Create the indexes.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by thomasp
    If you dont want to drop the user each time you want to do a data refresh, you could do the following:
    (i) Truncate the tables within the schema
    (ii) Disable the constraints
    (iii) Drop the indexes
    (iv) Do the import with constraints=n, indexes=n ( this is mainly effective if there are really large tables
    (v) Enable the contraints
    (vi) Create the indexes.

    What he said.
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    To enable Truncate to work, you should disable constraints first.

  7. #7
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Yup, you need to disable the constraints first before you truncate the tables oe else you will keep getting the
    ORA-02266: unique/primary keys in table referenced by enabled foreign keys

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by thomasp
    If you dont want to drop the user each time you want to do a data refresh, you could do the following:
    (i) Truncate the tables within the schema
    (ii) Disable the constraints
    (iii) Drop the indexes
    (iv) Do the import with constraints=n, indexes=n ( this is mainly effective if there are really large tables
    (v) Enable the contraints
    (vi) Create the indexes.
    Almost what he said.
    I remember when this place was cool.

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