Export Schema Question

Printable View

11-04-2004, 09:09 AM
lesstjm

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.
11-04-2004, 09:20 AM
OracleDoc

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 :D

imp help=y
11-04-2004, 09:41 AM
lesstjm

Thank you

Thanks for the help.
11-04-2004, 09:58 AM
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.
11-04-2004, 10:32 AM
Mr.Hanky

Quote:

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.
11-04-2004, 10:44 AM
DaPi

To enable Truncate to work, you should disable constraints first.
11-04-2004, 10:47 AM
thomasp

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
11-04-2004, 10:52 AM
Mr.Hanky

Quote:

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.