DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Beginners question

  1. #1
    Join Date
    Oct 2001


    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,


  2. #2
    Join Date
    Oct 2001
    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.


  3. #3
    Join Date
    Aug 2001
    Waterloo, On
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i

    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Sep 2001

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width