Test - Create table with Upstream (Local) Capture

I tested Oracle Stream on 11.1 (Upstream) with DEMO schema, then created new table. I just tested to solve it(error). It's simple and fun.

On SRC:

SQL> show user;
USER is "STRMADMIN"

SQL> SELECT TABLE_OWNER, TABLE_NAME, SCN from DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER TABLE_NAME SCN
------------------------------ ------------------------------ ----------
DEMO TEST01 1759366
DEMO TEST02 1761703

SQL> create table demo.tb_test (id number);

Table created.

SQL> insert into demo.tb_test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo.tb_test;

ID
----------
1

SQL> SELECT TABLE_OWNER, TABLE_NAME, SCN from DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER TABLE_NAME SCN
------------------------------ ------------------------------ ----------
DEMO TB_TEST 1766632
DEMO TEST01 1759366
DEMO TEST02 1761703

On DEST:

SQL> select * from demo.tb_test;
select * from demo.tb_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show user;
USER is "STRMADMIN"
SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME STATUS
------------------------------ --------
DEMO_APPLY ABORTED

SQL> select APPLY_NAME, LOCAL_TRANSACTION_ID, SOURCE_DATABASE, ERROR_MESSAGE from dba_apply_error;

APPLY_NAME LOCAL_TRANSACTION_ID SOURC ERROR_MESSAGE
---------- ---------------------- ----- --------------------------------------------------------------------------------
DEMO_APPLY 2.32.1124 SRC ORA-26687: no instantiation SCN provided for "DEMO"."" in source database "SRC"

Oops!!! no "TB_TEST" table. So.. exp/imp!!! (metadata only)
On SRC: Test,Check and etc

SQL> insert into demo.tb_test values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT TABLE_OWNER, TABLE_NAME, SCN from DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER TABLE_NAME SCN
------------------------------ ------------------------------ ----------
DEMO TB_TEST 1766632
DEMO TEST01 1759366
DEMO TEST02 1761703

SQL> alter table DEMO.TB_TEST read only;

Table altered.

On DEST: use DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN on database (destination), SRC = global name of the source site

SQL> select * from demo.tb_test;

no rows selected

SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME STATUS
---------- --------
DEMO_APPLY ABORTED

SQL> BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name=> 'DEMO.TB_TEST',
source_database_name=>'SRC',
instantiation_SCN=> 1766632);
END;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.stop_apply(apply_name => 'DEMO_APPLY');

PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.start_apply(apply_name => 'DEMO_APPLY');

PL/SQL procedure successfully completed.

SQL> select APPLY_NAME, STATUS from dba_apply;

APPLY_NAME STATUS
---------- --------
DEMO_APPLY ENABLED

SQL> select * from demo.tb_test;

no rows selected

On SRC:

SQL> alter table DEMO.TB_TEST read write;

Table altered.

SQL> select * from demo.tb_test;

ID
----------
1
2

On DEST:

SQL> select * from demo.tb_test;

ID
----------
1
2

OK. It showed data at destination. Another Test!!!
On SRC:

SQL> alter table demo.tb_test add (id2 number);

Table altered.

SQL> select * from demo.tb_test;

ID ID2
---------- ----------
1
2

On DEST:

SQL> select * from demo.tb_test;

ID ID2
---------- ----------
1
2

Maybe, I don't know other idea. I just tested, tested and fixed :)

Remark:
ORA-26687: no instantiation SCN provided for tablename.
After, You use DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN, You should use DBMS_APPLY_ADM.EXECUTE_ERROR(‘localtransactionid’) procedure.
but this case, ORA-26687: no instantiation SCN provided for "DEMO".""

read more DBA_APPLY_INSTANTIATED_OBJECTS and ORA-26687 [ID 783815.1]