Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.

    for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')      
       execute immediate     'INSERT INTO ||r.table_name|| @DBLINKSIT2
                          select a.* 
                          from DDSHIST.||r.table_name|| a  
                          WHERE b.GOLDEN_COPY = 'N'';   
       exception when others then null; 
      end loop;     

I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.

But I can't get the script to run and I don't know where I am getting this wrong.

Any help would be appreciated. Thank you.

  • 1
    Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/… – vercelli Aug 5 '16 at 8:55
  • Are you talking about Database Replication? Also as vercelli said you can use Datapump. – JSapkota Aug 5 '16 at 10:09
  • 1
    To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes. – Paul White Aug 10 '16 at 12:28

"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."

Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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