Beginners question

[Rene K]

Hello there,

Being new to Oracle and databases I have a question that may be basic to some of you, but I hope you will help me anyway.

We have one database with three tablespaces. The tablespaces are exact copies of each other. Al tables are present in both tablespaces. One tablespace holds our operational data, the other tablespace is for demo/testing.

I periodicaly want to update or replace the data in the demo tables with current data from the operational tables. I used imp / exp to create and fill the tables the first time, but I can't do this again becourse it fails if the tables are not empty. I think deleting all records in the tables by hand and and updating them again is not an option becourse there are 2000 or more tables.

IMHO there must be more people with simular problems. I think most ERP applications work with a operational company and one ore more test companies.

Thanks in advance,

Rene

[immu_d]

You must drop all the tables before importing it in the demo table & then import the export file.

Since you have many tables to be dropped, Write a drop script which will drop all the tables in that user. The script is as follows:

select ' drop table '|| tname ||';' from tab
Now run this script. It will automatically drop all tables pertaining to that user & then you can import.

Bye.

[Raminder]

You can insert records from one table to another if table definitions are same

Insert into demo.table1 (select * from operational.table1)

You can collect all these statements for all tables in a script and run it when database is relatively less used. To speed up insert, you can add /*direct*/ hint after insert.

IMHO, if you can, create another 'demo' database in another machine and use the 'Transportable tablespace' utility (of export/import) which will be much more efficient way of doing things.

[mageri]

Hi,

You can load production data which are not found in your test table with following:

insert /*+ direct */ into test_table select * from prod_table minus select * from test_table;