Geek Answers Handbook

Does anyone have an example of data migration script (Oracle 10g to Oracle 10g, but different schemes)?

I am trying to write a data migration pl / sql script to transfer some data in one schema to another schema on another server. The second database started as a subset of the original database, but we changed the schema. Therefore, I cannot use the following for each table:

Insert into DB2.table_name select * from DB1.table_name2;

I tried searching for sample scripts that showed how to do this, but didn't find anything.

+4
source share
4 answers

You can create a link to the database .

Then, if you are trying to switch from db1 to db2:

 Insert into table_name (f1, f2, etc) select (f1, f2, etc) from table_name2@DB2 ;

The choice can be as complex or simple as possible.

+1
source

If the columns differ between DB1.table_name and DB2.table_name, then you will need to list the columns in the insert statement. Unfortunately, there is no "magic bullet".

With that said, to speed up the process, you can write PL / SQL to generate insert statements, and then you can fix them manually. Here is a sample PL / SQL code for this. In this example, l_src_table will be your source table, and l_target_table will be your target table. Obviously, you still have to manually fix the SQL statement generated by this code, but this will at least lead to the creation of an SQL template that should save you a lot of time.

 DECLARE l_insert_stmt VARCHAR2(4000); l_comma VARCHAR2(1) DEFAULT ' '; l_src_table VARCHAR2(500) := 'TABLE1'; l_src_table_owner VARCHAR2(500) := 'DB1'; l_target_table VARCHAR2(500) := 'TABLE2'; l_target_table_owner VARCHAR2(500) := 'DB2'; BEGIN l_insert_stmt := 'INSERT INTO ' || l_target_table || ' ( '; FOR rec IN (SELECT column_name FROM all_tab_columns WHERE TABLE_name = l_target_table AND owner = l_target_table_owner) LOOP l_insert_stmt := l_insert_stmt || l_comma || rec.column_name; l_comma := ','; END LOOP; l_insert_stmt := l_insert_stmt || ' ) '; l_insert_stmt := l_insert_stmt || ' SELECT '; l_comma := ' '; FOR rec IN (SELECT column_name FROM all_tab_columns WHERE TABLE_name = l_src_table AND owner = l_src_table_owner) LOOP l_insert_stmt := l_insert_stmt || l_comma || rec.column_name; l_comma := ','; END LOOP; l_insert_stmt := l_insert_stmt || ' FROM ' || l_src_table; dbms_output.put_line(l_insert_stmt); END;
+3
source

If you need to do this often enough, then another option is to use a circuit synchronization tool. You can use a toad, dbkolo and, possibly, several other tools. It saved me a lot of time and effort.

0
source

You are looking for dbms_magic.perform_data_migration(old_schema, new_schema) .

0
source

More articles:


All Articles