Re: refresh data in another schema

Linda Lee wrote:

> In the same database, there are two user accounts: Production and
> Test. We need to refresh Test's data by using data in Production. 99%
> of the table structures and indexes are same in both accounts, but
> they are in different tablespaces and some objects in two accounts may
> not be identical. All Test's objects are in tablespace T1 (for tables)
> and tablespace TI1 (for indexes), and all Production's objects are in
> tablespaces P1, P2 and P2 (for tables) and tablespace PI1 (for
> indexes).
>
> 1. What's the best way to load Production's data into Test's tables
> and keep all objects Test has? if export/import is the best mothod,
> what options should I use to do that (since their tablespaces are
> different)?
>
> 2. If I can overwrite everythings in Test account, what options should
> I choose in doing Export and Import?
>
> 3. I think use of loop to "TRUNCATE test.table1 reuse; INSERT into
> test.table1 select * from production.table1;" over all tables will
> also work. Is it true? Will it keep all Test's indexes valid?
>
> Thank you very much.

I would write a stored procedure in test to grab the data from test and grant SELECT explicitly on those tables you wish to extract information from. But this has the making of a bad idea. You should be able to test against production data that is extracted once ... no need to ever go back again.

Daniel Morgan Received on Thu May 30 2002 - 00:59:35 CDT