Oracle SQL Developer: Copy paste tables, with 2 different instances, with different table structure 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.

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


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.
"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.