I have this scenario:

  1. One huge (thousands of tables), complex, Oracle 8 production database,
  2. One huge (thousands of tables), complex, Oracle 9 development database, (same structure as production)
  3. In the development database, some stored procedures and packages have been modified and new ones have been added.
  4. The development database has new tables in some schemas
  5. Neither Oracle 8 exp nor Oracle 9 imp has ROWS ONLY option

We usually do as follows because it's the only way to get data updated properly because a import with ignore=yes would only insert new data, but wouldn't update pre-existing rows with the same PK but different values in non-PK columns:

  1. Delete one schema, then create the user again to have an empty schema
  2. Import from user to user to the empty schema

The problem is:

  1. How to update the development database with fresh data from a production export without having to delete the schema first, since there're new stored procedures/packages as well as modified ones in the development database ?
  2. The comparing process to re-create only modified or new stored procedures after deleting the schema, for getting them back from a backup, would be too error prone.
  3. There are thousands (literally) of tables so we don't want to program a stored procedure to refresh the data in certain order etc. That would take months to write and test.

What would be an import-based solution to this ?

EDIT: I failed to mention that prod is Solaris and dev is RedHat.

  • 3
    Well, normally dev schema changes would be implemented using alter table etc scripts (which are stored in some kind of source control system), so you'd take a copy of prod then apply the schema changes to the copy to create an up-to-date dev copy. How are you going to apply the schema changes to production when your development is complete?
    – Philᵀᴹ
    Commented Sep 13, 2013 at 12:53
  • @Phil Maybe my question wasn't clear enough. I need to update dev DB with fresh data from production. Your alter table suggestion is not related to the question. Regarding your question, we plan to copy the freshly-data-updated dev DB to a similar server with Ora9 and setting that one as production (with same name and IP as current one). Current Ora8 production will be shutdown. Please read my question carefully as I think you could help me. Commented Sep 13, 2013 at 13:14
  • 1
    I did read your question correctly. My comment describes how this would usually be done
    – Philᵀᴹ
    Commented Sep 13, 2013 at 13:23
  • Are you upgrading your prod database to 9i in the process or am I misunderstanding? Commented Sep 13, 2013 at 13:26
  • Also, it sounds like you've just taken a copy of prod and starting developing on it with no thought about source control etc, so you have no idea what changes have been made. Bad practice. There is no easy way out
    – Philᵀᴹ
    Commented Sep 13, 2013 at 13:27

2 Answers 2


I see two solutions. I did not test them but I hope they are userful.

In contrast to table mode export both user mode and tablespace mode export take care of the dependencies between tables so that the tables are imported in the right order. Both methods import the production table stuctures to dev. Tablespace mode export/import in ORacle 8i or 9i can be done only beteen database systems on the same operating system. On can handle this restriction by importing the data using user mode import into a intermediate schema or database on the same operating system as the target sytem.

Method 1: Using TablespaceMode export/import:
1) Put the tablespace(s) of prod in read only mode
2) TablespaceMode-Export the tablespace(s) and datafile copy from prod
3) Put the tablespaces of prod in read/write mode again
4) Drop the Tablespaces in dev (leaves procedures, views from dev)
5) TablespaceMode-Import the tablespaces in Target (includes tables, triggers , indexes, constraints of prod)
6) Put the tablespace(s) of dev in read/write mode

Maybe there are some problems you have to copy with changes in table structure, missing grants or that sequences have to be adjusted

Method 2: replace procedures by an export
1) UserMod-Export of the prod schema
2) UserMod-Export of the dev schema
3) drop the dev schema
4) import the prod schema (to dev)
5) import the dev-schema (to dev)

the second import schould replace the prod objects (procedures, views) by the dev objects but leave the tables and table related objects. Parameters with * are alredady set during the export. Maybe one must not set them during import. If you set IGNORE=N then you will get a lot of error messages but maybe there are some other advantages. It will happen hat you import tables from dev that are not in prod. Changes to the table structure must be handled mynually.

I would prefere a method that extracts the procedures from the dev schema before it is replaced by the prod schema. Tools like Toad from Quest (as mentioned by @r.m in a comment) that can compare schemas an create scripts to implement the differences may help.

1) UserMode-Export from prod
2) Extract the procedures from dev into sql-scripts
3) drop dev schema
4) UserMode-Import to dev
5) drop procedures in dev
6) run the scripts created by the extraction process
7) compile all procedures

The following method leaves the dev structures and only imports the data from production. You will get errors during import of data if the column of a table has been renamed or removed in dev. Adding columns with constraints in dev tables or altering contraints in dev may also make the import of data fail for this tables. If some tables aren`t imported because of this errors dependent tables that were not changed in dev can also have problems when the data is imported. So a lot of complicated problems can arise. The basic idea is:

*) remove the data from the dev tables
*) disable contraints, triggers and indexes on the dev tables
*) import the data only from the prod tables to the dev tables
*) enable the constraints, triggers and indexes on the dev tables
*) import the data to the new dev tables and enable their constaints, triggers and indexes

1)user mode export the dev schema without triggers
2) user mode export the dev schema again
3) table mode export of dev tables not found on prod
4) drop the schema on dev
5) create the empty schema on dev again
6) import the empty dev schema without triggers, constraints and indexes
7) import the indexes in a script
8) user mode export the prod schema
TRIGGERS=NO INDEXES=NO FILE=prod1.dmp 9) table import the prod table data
10) create the indexes using index.sql with sqlplus
11) import table constraints and triggers
12) import the dev tables you need

  • It won't work. There are schema changes that involve extra columns or data type changes.
    – Philᵀᴹ
    Commented Sep 13, 2013 at 23:46
  • My fault :( I failed to mention that prod is Solaris and Dev is RedHat... sorry, datafiles and not copy-compatible. But +1 for your effort, I will evaluate method 2. Commented Sep 14, 2013 at 0:54
  • @Phil: These are one of the "some problems". I will mention this explicitely. Such changes must be implemented by scripts.
    – miracle173
    Commented Sep 14, 2013 at 4:59
  • @user1598390 you can overcome this by using an intermediate schema. I modified this to my answer.
    – miracle173
    Commented Sep 14, 2013 at 6:52

What finally solved my problem was:

  • turning archive mode off
  • disallowing connections
  • disabling triggers and constraints
  • truncating all tables
  • performing imports ( attemp of creating existing objects fails but data is inserted )
  • re-enabling triggers and constraints
  • turning archive mode back on
  • allowing connections back

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.