Hi Tom, Experts,

I am in a need to replicate manually all objects from one schema to another (manually, because my schemas, both source and target, reside in the cloud and I have no access to any OS level utility, nor sql*net access to the database to connect with a client tool).
The goal is that I create a regular backup of my data that I am refreshing say weekly, and in the case of a disaster (eg even user error) I can easily switch the database schema under my application from the source to the target schema.

I'd like to ask what is an elegant solution for keeping my sequences in sync between the source and the target. In practice I'd like to use highly automated solution that can be dbms_scheduler-scheduled and does not require any manual intervention and can be executed in both full/initial and incremental way.

I can grant select from the source to the target schema, and use insert as select - so far so good.

My problem is keeping the sequences that populate the primary keys in sync between the source and the target, so that
1. In the target the primary keys already populated in the tables remain the same as in the source, to maintain the FK integrity
2. When I try to insert new record in the target, the corresponding sequence provides a number that comes after the last assigned number in the source

I am finding it even a little trickier with the tables using the 12c Identity column feature, as the system generated sequences don't even have the same names in the source and the target schema (if at all I wanted to maintain them manually!)

Can you suggest an elegant way to keep my sequences in sync during such manual "replication"?
I have couple of ideas (sharing further down), but not finding any of them particularly elegant.

Here is a very simple test case (with Identity column):

-- creating the table in the source schema and populating couple of records

CREATE TABLE  "AI1" 
   ( "AI1ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 2 NOORDER  NOCYCLE  NOKEEP  NOT NULL ENABLE, 
 "NAME" VARCHAR2(10), 
  CONSTRAINT "AI1_PK" PRIMARY KEY ("AI1ID")
  USING INDEX  ENABLE
   )
/


insert into ai1 (name) values ('test1'); 
/
insert into ai1 (name) values ('test2'); 
/
insert into ai1 (name) values ('test3'); 
/

-- now recreating the object in the other schema (for this test it doesn't really matter if it is the same or different schema, as the identity column system-generated sequence will have a unique name anyway) and copying over the data with insert as select:

CREATE TABLE  "AI2" 
   ( "AI2ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 2 NOORDER  NOCYCLE  NOKEEP  NOT NULL ENABLE, 
 "NAME" VARCHAR2(5), 
  CONSTRAINT "AI2_PK" PRIMARY KEY ("AI2ID")
  USING INDEX  ENABLE
   )
/

insert into ai2 select * from ai1;
/

Now, suppose my source schema got broken and I want to switch to the target schema.

If I try to insert a row in AI2, it will error out as the data default sequence on the primary key column has not got advanced in sync with the previously inserted data:

insert into ai2 (name) values ('test4'); 

ORA-00001: unique constraint (EKTEST.AI2_PK) violated
ORA-06512: at "SYS.DBMS_SQL", line 1721

How can avoid this?

What I have been thinking of in case of identity columns:

1. Adjust my table creation ddl to specify the "start with" as a value greater than the largest value of the primary key of the source table - this will not help in case of incremental insert

2. Add a cycle to lift up the sequence to the same max value - well, not really nice

3. Add a cycle to insert dummy records in the target table until the sequence reaches the max value in the source table, then truncate the table and insert-as-select the real records - even less nice and again not good in case of incremental insert

What is your expert recommendation?

many thanks in advance!