How to copy selective data from one database to another (ORACLE)

Question

We have a need to find a way to copy certain data from production into our dev regions so that we can debug/fix any issue. Sometimes single user related data gets impacted. We have to replicate the same scenario in dev and find a solution. Presently we follow two approaches:-

 1. Check the audit history and try to recreate the similar scenario
    in  dev. <50% sucess rate in recreating the exact same scenario.
 2. Restore+Encrypt the "whole" production into dev and then continue
    on  the work. It is an overkill if issue impacts only a single user.

So I am trying to find a way to just select a single user data from production and insert it into dev region.

We just have Java and Oracle. Can't use any external tools. Because we dont have license and cannot download freeware due to security issues.

I tried the follwing:-

1. Write a java code which will query the informaition schema tables to find the relationships between the tables and create select statements like below:-

select 'insert into TABLE1(C1,C2,C3,C4) values ('||''''||C1||''''||','||coalesce(to_char(C2),'null')||','||''''||C3||''''||','||coalesce(to_char(C4),'null'));'
from TABLE1 where ID='1006' union all 
select 'insert into TABLE2(C1,C2,C3,C4) values ('||''''||C1||''''||','||coalesce(to_char(C2),'null')||','||''''||C3||''''||','||coalesce(to_char(C4),'null'));'
from TABLE2 WHERE TABLE1ID in ( select ID FROM TABLE1 where ID='1006') union all 
select 'insert into TABLE3(C1,C2,C3,C4) values ('||''''||C1||''''||','||coalesce(to_char(C2),'null')||','||''''||C3||''''||','||coalesce(to_char(C4),'null'));'
from TABLE3 WHERE TABLE2ID in ( select ID FROM TABLE2 WHERE TABLE1ID in ( select ID FROM TABLE1 where ID='1006'));

2. Use this set of selects in production, so that you get a set of insert statements as output. 3. Use the insert statements in dev.

Problem:- The select queries are becoming huge. Around 25 MB in total :( We cannot even execute that big query in production.

Could you suggest any better approach for this usecase? Does oracle itself allow selective data exports? Or any other way I should write my java code?

Answer 1

We use something like this to move records from one database to another:

copy from username/password@database1 to username/password@database2 insert target_table using select * from source_table where where_clause_goes_here;