ORACLE Using SERIALIZABLE to PL/SQL for download consistent data

Question

For download consistent data from OLTP-database make proc:

   procedure move_tables_to_mst_layer(v_sync sync_data) is
   pragma autonomous_transaction;
   v_src_table  varchar2(128);
   sql_stm      varchar2(30000);
   v_proc    varchar2(128);

begin

   execute immediate 'alter session SET ISOLATION_LEVEL = SERIALIZABLE';

   for rec in (select a_tbl_name as tblnm from a_tables
                where a_tbl_type in ('T0', 'T1')
                order by a_tbl_order
              )
   loop

       v_src_table := 'STG_'||rec.tblnm;
       sql_stm := 'insert /*+ append */ into '||v_src_table||' select t1.*
                    from '||rec.tblnm||' t1
                   where row_seq > '||v_sync.v_rowseq
                     ;
       --dbms_output.put_line(sql_stm);
       execute immediate sql_stm;
       --commit;
   end loop;

   commit;
   execute immediate 'alter session SET ISOLATION_LEVEL = READ COMMITTED';
exception
when others then
    errpck.raise_n_stop('SYNC', v_proc, 'Procedure move_tables_to_mst_layer');
end move_tables_to_mst_layer;

Version Oracle 12c. Field indicator row_seq - populated by trgger for each tables. For any DML operation (insert or update) :new.row_seq = seq_rowseq.nextavl, where seq_rowseq - global sequence. Periodic (once per 6-12 hours) occurs error foreign key no parent. Its mean what procedure inserted new records from child-table and not inserted new records from parent-table - i dont know how its happens. May be using execute immediate exclude isolation level serializable?

score 0 · Answer 1 · answered May 13 '20 at 13:41

I don't think you've provided enough information for anyone to help you. What are the table structures? Do other processes insert into these tables? What is the FK relationship between the tables?

One additional point: you absolutely should not be using triggers to populate a column from a sequence on Oracle 12c and above. Triggers are the enemy of well performing DML and the IDENTITY feature added in 12c negates the reasons for using them.

There is some good documentation already online. I personally like this article.

Here is a simple example using an existing sequence (which you have).

CREATE SEQUENCE so_61439648_seq;

CREATE TABLE so_61439648 (
 some_id NUMBER,
 data_1 VARCHAR2(100),
 data_2 VARCHAR2(100)
);

ALTER TABLE so_61439648 MODIFY some_id DEFAULT so_61439648_seq.nextval;

Here is how to set it up at table create, but with a separate sequence.

CREATE SEQUENCE so_61439648_seq;

CREATE TABLE so_61439648 (
 some_id NUMBER DEFAULT so_61439648_seq.nextval,
 data_1 VARCHAR2(100),
 data_2 VARCHAR2(100)
);

You can also leave generating and attaching the sequence up to Oracle, but personally, for the same reasons of having nice names for your constraints, I like the above method because it makes the relationship clear.

Here is the Oracle generated method.

CREATE TABLE so_61439648 (
 some_id NUMBER GENERATED ALWAYS AS IDENTITY,
 data_1 VARCHAR2(100),
 data_2 VARCHAR2(100)
);

SELECT t.data_default
  FROM all_tab_columns t
 WHERE t.table_name = 'SO_61439648'
   AND t.column_name = 'SOME_ID';
-- "ISEQ$$_1874517".nextval

ORACLE Using SERIALIZABLE to PL/SQL for download consistent data

1 Answers1