DBAs script bunches

http://www.shutdownabort.com/

/*+ PRECOMPUTE_SUBQUERY */

Here is some interesting findings:

Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint

Some explanation at OTN: precompute_subquery hint

Here is simple example of common mistake:

SQL> drop table t
2 /

Table dropped
SQL> create table t
2 as
3 select level as num from dual connect by level <= 100
4 /

Table created

SQL> begin
2
3 savepoint a;
4
5 execute immediate 'truncate table t';
6
7 insert into t
8 select level+100 from dual connect by level <= 100;
9
10 raise_application_error( -20000,'Fake exception');
11
12 exception
13 when others then
14 rollback to a;
15 raise;
16 end;
17 /

begin

savepoint a;

execute immediate 'truncate table t';

insert into t
select level+100 from dual connect by level <= 100;

raise_application_error( -20000,'Fake exception');

exception
when others then
rollback to a;
raise;
end;

ORA-01086: savepoint 'A' never established
ORA-06512: at line 15
ORA-20000: Fake exception


The cause of rollback failure is that rollback to savepoint A operation could be completed after commitment by DDL statement.