Re: imp/exp, replace one schema with a different


Ben wrote:
I'm running 9.2.0.5 Ent Ed. DMTs, compatible param=8.1.0, on Unix
AIX5L.

In my instance I have a production data schema. I also have a schema
that we use to test production applications against non-production
data. Periodically I have to refresh that non-production data with more
realistic/up-to-date production data.
Currently there is a script that basically exports the production
schema, then drops all tables from the test schema, and finally imports
the production data into the test schema.
I was thinking that it may be easier and more efficient to just drop
the user for the test schema and then re-create that user and import
the production tables.
I was also considering truncating all the test tables as an option, but
in some cases we need to adjust the initial and next sizes of table
extents. If you truncate a table can you resize the initial extent?
Overall what would you consider the best way of doing this refresh of
data?

Use of exp then imp with fromuser= touser= parameters and ignore=y to
import into pre-existing truncated tables. You can provide a new
initial extent size on an alter table move statement.

But if your test system used Locally managed tablespace with either
uniform extents or autoallocate this would be less of an issue.
However, being a test system I can understand why the unlimited extent
value that comes with LMT may not be acceptable due to available disk
space limitations.

HTH -- Mark D Powell --

.