I have found this script and its so useful, I had to share it again.
Anytime you want to sync your data from the production database to the test database you can use this script.
JUST BE VERY CAREFUL TO NOT UPDATE YOUR PRODUCTION DATA WITH TEST DATA.
I like to run it on the test database, to be safe I create a DB link with a user that just have only Read Only access on the production data, to ensure you don’t update production data.
If you have more than one test database its always advisable to test it first between test databases.
This script wont update any data it will only generate the merge statement that you can use to update your data.
-------------------------------------------------------------------------------- --Meta SQL for scaffolding table merge statements -- Usage: -- Execute SQL -- export/copy lines to sql file -- make necessary edits -- TODO: -- make all column references explicit -------------------------------------------------------------------------------- WITH target AS (SELECT upper(trim(:owner)) AS owner, upper(trim(:table_name)) AS table_name FROM dual ), all_cols AS (SELECT atc.owner AS owner, atc.table_name AS table_name, atc.column_name AS column_name, atc.column_id AS column_id, atc.data_type AS data_type, atc.data_length AS data_length FROM all_tab_cols atc, target WHERE atc.owner = target.owner AND atc.table_name = target.table_name AND atc.hidden_column != 'YES' AND atc.virtual_column != 'YES' ORDER BY column_id ), pk_cols AS (SELECT ac.owner AS owner, ac.table_name AS table_name, acc.column_name AS column_name, acc.position AS position, all_cols.column_id AS column_id FROM all_constraints ac, all_cons_columns acc, all_cols WHERE ac.owner = all_cols.owner AND ac.table_name = all_cols.table_name AND ac.constraint_type = 'P' AND acc.owner = ac.owner AND acc.table_name = ac.table_name AND acc.constraint_name = ac.constraint_name AND acc.column_name = all_cols.column_name ORDER BY acc.position ), data_cols AS (SELECT owner, table_name, column_name, column_id FROM all_cols MINUS SELECT owner, table_name, column_name, column_id FROM pk_cols ORDER BY column_id ), sql_parts AS (SELECT 'SELECT' AS sql_select, 'FROM' AS sql_from, 'WHERE' AS sql_where, 'ORDER BY' AS sql_order_by, ';' AS sql_semi, 'MERGE INTO' AS sql_merge_into, 'USING' AS sql_using, 'ON' AS sql_on, 'WHEN MATCHED THEN' AS sql_when_matched, 'UPDATE SET' AS sql_update_set, 'WHEN NOT MATCHED THEN' AS sql_when_not_matched, 'INSERT' AS sql_insert, 'VALUES' AS sql_values, '(' AS sql_paren_open, ')' AS sql_paren_close, chr(10) AS sql_lf, ' ' AS sql_t, MAX(LENGTH(column_name)) AS max_col_length FROM dual, all_cols ) -------------------------------------------------------------------------------- SELECT STATEMENT FROM (SELECT 1 AS statement_order, rownum AS row_order, sql_merge_into AS STATEMENT FROM sql_parts UNION SELECT 2 AS statement_order, rownum AS row_order, sql_parts.sql_t ||lower(owner) ||'.' ||lower(table_name) ||' o' AS STATEMENT FROM target, sql_parts UNION SELECT 3 AS statement_order, rownum AS row_order, sql_using AS STATEMENT FROM sql_parts UNION SELECT 4 AS statement_order, rownum AS row_order, sql_t ||sql_paren_open AS STATEMENT FROM sql_parts UNION SELECT 5 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_select AS STATEMENT FROM sql_parts UNION SELECT 6 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_t ||sql_t || rpad(lower(all_cols.column_name), sql_parts.max_col_length, ' ') ||rpad(' AS ' ||all_cols.column_name ||( CASE WHEN lead(all_cols.column_id) over (order by all_cols.column_id) IS NOT NULL THEN ',' ELSE NULL END), sql_parts.max_col_length+6, ' ') ||'-- ' ||all_cols.data_type ||sql_parts.sql_paren_open ||all_cols.data_length ||sql_parts.sql_paren_close AS STATEMENT FROM all_cols, sql_parts UNION SELECT 7 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_t ||sql_from FROM sql_parts UNION SELECT 8 AS statement_order, rownum AS row_order, sql_parts.sql_t ||sql_parts.sql_t ||sql_parts.sql_t ||sql_parts.sql_t || lower(target.owner ||'.' ||target.table_name) AS STATEMENT FROM target, sql_parts UNION SELECT 9 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_t || sql_order_by AS STATEMENT FROM sql_parts UNION SELECT 10 AS statement_order, position AS row_order, sql_t ||sql_t ||sql_t ||sql_t || lower(column_name) || ( CASE WHEN lead(position) over (order by position) IS NOT NULL THEN ',' ELSE NULL END ) FROM pk_cols, sql_parts UNION SELECT 11 AS statement_order, rownum AS row_order, sql_t ||sql_paren_close ||' n' AS STATEMENT FROM sql_parts UNION SELECT 12 AS statement_order, rownum AS row_order, sql_on AS STATEMENT FROM sql_parts UNION SELECT 13 AS statement_order, rownum AS row_order, sql_t ||sql_paren_open AS STATEMENT FROM sql_parts UNION SELECT 14 AS statement_order, position AS row_order, sql_t ||sql_t || rpad('o.' ||lower(column_name), max_col_length+2, ' ') ||' = ' ||'n.' ||lower(column_name) ||( CASE WHEN lead(column_id) over (order by position) IS NOT NULL THEN ' AND' ELSE NULL END) AS STATEMENT FROM pk_cols, sql_parts UNION SELECT 15 AS statement_order, rownum AS row_order, sql_t ||sql_paren_close AS STATEMENT FROM sql_parts UNION SELECT 16 AS statement_order, rownum AS row_order, sql_when_matched AS STATEMENT FROM sql_parts UNION SELECT 17 AS statement_order, rownum AS row_order, sql_t || sql_update_set AS STATEMENT FROM sql_parts UNION SELECT 18 AS statement_order, rownum AS row_order, sql_t ||sql_t || rpad('o.' ||lower(column_name), max_col_length+2, ' ') ||' = ' ||'n.' ||lower(column_name) ||( CASE WHEN lead(column_id) over (order by column_id) IS NOT NULL THEN ',' ELSE NULL END) AS STATEMENT FROM data_cols, sql_parts UNION SELECT 19 AS statement_order, rownum AS row_order, sql_when_not_matched AS STATEMENT FROM sql_parts UNION SELECT 20 AS statement_order, rownum AS row_order, sql_t ||sql_insert AS STATEMENT FROM sql_parts UNION SELECT 21 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_paren_open AS STATEMENT FROM sql_parts UNION SELECT 22 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_t || 'o.' ||lower(column_name) ||( CASE WHEN lead(column_id) over (order by column_id) IS NOT NULL THEN ',' ELSE NULL END) AS STATEMENT FROM all_cols, sql_parts UNION SELECT 23 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_paren_close AS STATEMENT FROM sql_parts UNION SELECT 24 AS statement_order, rownum AS row_order, sql_t || sql_values FROM sql_parts UNION SELECT 25 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_paren_open AS STATEMENT FROM sql_parts UNION SELECT 26 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_t || 'n.' ||lower(column_name) ||( CASE WHEN lead(column_id) over (order by column_id) IS NOT NULL THEN ',' ELSE NULL END) AS STATEMENT FROM all_cols, sql_parts UNION SELECT 27 AS statement_order, rownum AS row_order, sql_t ||sql_t ||sql_paren_close AS STATEMENT FROM sql_parts UNION SELECT 28 AS statement_order, rownum AS row_order, sql_semi AS STATEMENT FROM sql_parts ) ORDER BY statement_order, row_order ;