Export Schema Question

[lesstjm]

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.

[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

imp help=y

[lesstjm]

Thanks for the help.

[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.

[Mr.Hanky]

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.

[DaPi]

To enable Truncate to work, you should disable constraints first.

[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

[Mr.Hanky]

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.