The Batman
Reputation: 21
dml error logging for partitioned tables in oracle 11g
We are facing some issue when we try to use DML error logging while inserting data in a partitioned table.
In our programs, we load the data from different tables in some target tables. In one of our program, we are inserting data in a table which
- Is a partitioned table
- Have one unique index
- Have not null constraint for one or more columns
Currently we are not removing records violating NULL/ UNIQUE constraint before inserting data in the final table. When, we try to insert records which will violate the NULL constraint or UNIQUE constraint we get some duplicates in the error log table.
Ex. if we have 2 records violating NULL constraint and one record violating UNIQUE constraint then the error log table should have 3 records. But here we get 4 or 5 records.
Oracle version details-
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production
Below are the sample queries to create the tables to replicate this issue
/* Sample table & index creation -- START */
drop table tmp_table purge;
create table tmp_table
(
CREATE_DT date
,REC_SEQ_NBR number( 12 )
,REC_TYP_CD varchar2( 3 byte )
,EMP_NBR number( 6 )
,DEPT_NBR number( 8 )
);
-- create final table with partitions to insert data into it
drop table tmp_final_table purge;
create table tmp_final_table
(
CREATE_DT date not null
,REC_SEQ_NBR number( 12 ) not null
,REC_TYP_CD varchar2( 3 byte ) not null
,EMP_NBR number( 6 )
,DEPT_NBR number( 8 )
)
nocompress
tablespace ALL_DATA_4M_01_D
result_cache (mode default)
pctused 0
pctfree 5
initrans 1
maxtrans 255
storage( buffer_pool default flash_cache default cell_flash_cache default )
partition by range
(create_dt)
(
partition
PM201603
values less than
(to_date( ' 2016-03-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
))
logging
nocompress
tablespace ALL_DATA_4M_01_D
pctfree 5
initrans 1
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
)
,partition
PM201606
values less than
(to_date( ' 2016-06-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
))
logging
nocompress
tablespace ALL_DATA_4M_01_D
pctfree 5
initrans 1
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
)
,partition
PM201609
values less than
(to_date( ' 2016-09-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
))
logging
nocompress
tablespace ALL_DATA_4M_01_D
pctfree 5
initrans 1
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
) )
nocache
noparallel
monitoring;
-- create a primary key
create unique index tmp_final_table_pk
on dkharwandikar.tmp_final_table( create_dt, rec_seq_nbr )
logging
tablespace ALL_DATA_4M_01_I
pctfree 10
initrans 2
maxtrans 255
storage( initial 4 m
next 4 m
maxsize unlimited
minextents 1
maxextents unlimited
pctincrease 0
buffer_pool default
flash_cache default
cell_flash_cache default
)
noparallel;
;
/* Sample table & index creation -- END */
/* PLSQL block to insert data in the final table - START */
begin
insert /*+ parallel(6) */
into tmp_final_table( create_dt
,REC_SEQ_NBR
,REC_TYP_CD
,EMP_NBR
,DEPT_NBR
)
select create_dt
,REC_SEQ_NBR
,REC_TYP_CD
,emp_nbr
,dept_nbr
from tmp_table
log errors into err$_tmp_final_table ( 'INSERT' )
reject limit unlimited;
commit;
end;
/* PLSQL block to insert data in the final table - END */
Below are the different scenarios and the results (Please truncate/ drop & create the tables before you test each of the scenario mentioned below)
-- Case 1- Few records violating UNIQUE constraint and few records violating NULL constraint
-- Result- For each record violating NULL constraint, we can see 2 records in the error log table.
-- Use below data set to test this scenario
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
-- insert some records with REC_TYP_CD value as NULL. This is having NULL constraint on final table
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 11, null, 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, null, 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 12, null, 3, 10 );
-- insert some duplicates
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
COMMIT;
--
-- Case 2- No record violating NULL constraint; few records with UNIQUE constraint violation
-- Result- For first record causing duplicate, we can see 2 records in the error log table. So, in below example, we should get 3 records in error log but we get 4 records.
-- Use below data set to test this scenario
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
-- insert some duplicates
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
COMMIT;
--
-- Case 3- Try to insert duplicate records by running insert query twice.
-- Result- For first run, it will insert all the data & in 2nd run it will insert all records in error log. In error log you can see one additional record is present.
-- Use below data set to test this scenario
-- In below example, in 2nd run, we should get 10 records in error log. But we get 11 records in error log.
insert into tmp_table values ( to_date('04192016','MMDDYYYY'), 1, 'AA', 1, 10 );
insert into tmp_table values ( to_date('03292016','MMDDYYYY'), 2, 'AA', 2, 10 );
insert into tmp_table values ( to_date('12132015','MMDDYYYY'), 3, 'AB', 3, 10 );
insert into tmp_table values ( to_date('01102015','MMDDYYYY'), 4, 'AA', 4, 10 );
insert into tmp_table values ( to_date('06092014','MMDDYYYY'), 5, 'AC', 5, 10 );
insert into tmp_table values ( to_date('05152016','MMDDYYYY'), 6, 'AA', 6, 10 );
insert into tmp_table values ( to_date('02182015','MMDDYYYY'), 7, 'DH', 7, 10 );
insert into tmp_table values ( to_date('02122011','MMDDYYYY'), 8, 'XX', 8, 10 );
insert into tmp_table values ( to_date('03102016','MMDDYYYY'), 9, 'AB', 9, 10 );
insert into tmp_table values ( to_date('12232015','MMDDYYYY'), 10, 'AA', 10, 10 );
COMMIT;
--
-- Case 4- Few records violating NULL constraint; no records with UNIQUE constraint violation
-- Result- For each record violating NULL constraint we can see 1 records in the error log table.
-- No Issue
--
-- Case 4- Use any of the scenario (case 1, 2 or 3) data setup with CURSOR FORALL-BULK COLLECT- INSERT
-- Result- For each record violating constraint we can see 1 records in the error log table.
-- No Issue
-- code to try this scenario
declare
type t_create_dt is table of tmp_final_table.create_dt%type
index by binary_integer;
v_create_dt t_create_dt;
type t_rec_seq_nbr is table of tmp_final_table.rec_seq_nbr%type
index by binary_integer;
v_rec_seq_nbr t_rec_seq_nbr;
type t_rec_typ_cd is table of tmp_final_table.rec_typ_cd%type
index by binary_integer;
v_rec_typ_cd t_rec_typ_cd;
type t_emp_nbr is table of tmp_final_table.emp_nbr%type
index by binary_integer;
v_emp_nbr t_emp_nbr;
type t_dept_nbr is table of tmp_final_table.dept_nbr%type
index by binary_integer;
v_dept_nbr t_dept_nbr;
type t_cmd_cur is ref cursor;
cmd_cur t_cmd_cur;
v_sql_stmt varchar2 ( 2500 ) := null;
n_commit_cnt number := 2;
n_tot_rec_load_cnt number := 0;
begin
v_sql_stmt := ' select create_dt
,rec_seq_nbr
,rec_typ_cd
,emp_nbr
,dept_nbr
from tmp_table ';
open cmd_cur for v_sql_stmt;
loop
fetch cmd_cur
bulk collect into v_create_dt
,v_rec_seq_nbr
,v_rec_typ_cd
,v_emp_nbr
,v_dept_nbr
limit n_commit_cnt;
if cmd_cur%rowcount > n_tot_rec_load_cnt
then
forall j in v_create_dt.first .. v_create_dt.last
insert
into tmp_final_table ( create_dt
,rec_seq_nbr
,rec_typ_cd
,emp_nbr
,dept_nbr
)
values (
v_create_dt ( j )
,v_rec_seq_nbr ( j )
,v_rec_typ_cd ( j )
,v_emp_nbr ( j )
,v_dept_nbr ( j ) )
log errors into err$_tmp_final_table ( 'INSERT' )
reject limit unlimited;
n_tot_rec_load_cnt := n_tot_rec_load_cnt + sql%rowcount;
end if;
commit;
exit when cmd_cur%notfound;
end loop;
close cmd_cur;
commit;
end;
I know that we can take care of these issues the tmp_table itself and remove the records causing problem. But I want to know what is going wrong here? This happens only when we use it on a partitioned table. If we remove the partitions from the final table, the problem will be resolved.
We are following similar logic and table/ index setup in many programs and I want to avoid modifying those programs. Is it the expected behavior?
In one of the oracle forums I see that unique constraint in error table works differently for direct path inserts. I believe I am not doing a direct path insert here.
Let me know if you need additional information on this.
Upvotes: 0
Views: 241
Answers (1)
The Batman
Reputation: 21
Was able to figure out a workaround for this. This issue happens only if the error table has zero records. So, we first inserted a dummy record to the error table and after loading the data, we deleted this dummy record. This seems to solve the issue.
Upvotes: 0