Import Oracle schema data without losing modifications of stored procedures

importoracle-9ischema

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.

Best Answer

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
CONSTRAINTS=N
GRANTS=N
STATITICS=N
TRIGGERS=N
ROWS=N
3) drop the dev schema
4) import the prod schema (to dev)
5) import the dev-schema (to dev)
IGNORE=Y
CONSTRAINTS=N*
GRANTS=N*
INDEXES=N
ROWS=N*

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
ROWS=NO
TRIGGERS=NO
FILE=dev1.dmp
2) user mode export the dev schema again
ROWS=NO
TRIGGERS=NO
FILE=dev1.dmp
3) table mode export of dev tables not found on prod
TABLES=list_of_tables_in_right_order
TRIGGERS=Y
CONSTRAINTS=Y
INDEXES=YES
GRANTS=Y
ROWS=Y
STATISTICS=NO
dev3.dmp
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
ROWS=NO
CONSTRAINTS=N
INDEXES=NO
FILE=dev1.dmp
7) import the indexes in a script
INDEXES=YES
ÌNDEXFILE=index.sql
FILE=dev1.dmp
8) user mode export the prod schema
CONSTRAINTS=NO
GRANTS=NO
TRIGGERS=NO INDEXES=NO FILE=prod1.dmp 9) table import the prod table data
TABLES=*
IGNORE=Y
CONSTRAINTS=N
INDEXES=N
GRANTS=N
FILE=prod1.dmp
10) create the indexes using index.sql with sqlplus
11) import table constraints and triggers
TABLES=*
CONSTRAINT=Y
TRIGGERS=Y
ROWS=N
FILE=dev2.dmp
12) import the dev tables you need
TABLES=list_of_tables_in_right_order
IGNORE=Y
CONSTRAINT=Y
GRANTS=Y
INDEXES=Y

Related Question