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 -- Received on Thu Jun 29 2006 - 14:43:19 CDT