Why you might want to think twice about using INSERT ALL.
One of those things I knew and then forgot.
So, let’s say you’ve got three tables or a partitioned table or something like that.
Let’s use regional tables for simplicity.
drop table t1_r1; drop table t1_r2; drop table t1_r3; create table t1_r1 (col1 varchar2(2) not null ,col2 number not null ,check( col1 in ('R1'))); create table t1_r2 (col1 varchar2(2) not null ,col2 number not null ,check( col1 in ('R2'))); create table t1_r3 (col1 varchar2(2) not null ,col2 number not null ,check( col1 in ('R3'))); insert into t1_r1 values ('R1',1); insert into t1_r2 values ('R2',1); insert into t1_r3 values ('R3',1); commit;
And you want a routine that will insert into one of those tables depending on region.
And you’re a simple fellow, so you go with an IF statement:
create or replace procedure p1 ( col1 in varchar2, col2 in number ) as begin if col1 = 'R1' then insert into t1_r1 values(col1,col2); elsif col1 = 'R2' then insert into t1_r3 values(col1,col2); else insert into t1_r3 values(col1,col2); end if; end p1; / Procedure P1 compiled
And then in the same session you run this uncommitted:
exec p1('R1',2); PL/SQL procedure successfully completed.
And then in another session you decide to truncate table T1_R3:
truncate table t1_r3; Table T1_R3 truncated.
None was expected.
Let’s say that we decide to tidy up that procedure and get rid of some of the repetition by using an INSERT ALL statement.
I will use a standalone sql statement just to demonstrate a further minor aspect rather than using a procedure with a bound parameter.
insert all when col1 = 'R1' then into t1_r1 when col1 = 'R2' then into t1_r2 when col1 = 'R3' then into t1_r3 select 'R1' col1,2 col2 from dual; 1 row inserted.
Let’s revisit the truncate:
truncate table t1_r3; SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 00054. 00000 - "resource busy and acquire with NOWAIT specified or timeout expired" *Cause: Interested resource is busy. *Action: Retry if necessary or increase timeout.
TM share locks from the INSERT ALL on all three possible targets prevent the TRUNCATE.
So, a simple/simplisitic illustration of why you might want to think twice about whether INSERT ALL is the best feature for your use case, based on a real life problem.
For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.
However, turns out this is ineffective IF you use:
ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;
This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query on a prod DB blowing out the main TEMP space.
Deduction quickly suggested that the above must be the case and it was quickly proven by a test case and also supported by the documentation .
the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects
Obviously… not using ALTER SESSION and using fully qualified object names prevents this “vulnerability”… but that’s not good enough unfortunately.