a german version:
Oregon verklagt Oracle
Archives For August 2014
do not apply PSU 12.1.0.1.4 if you are using APEX and the embedded plsql gateway: you might hit bug 18610915 which is internal and therefore no description is available.
if you hit this you are either not able to even bring up the apex login page (neither http nor https) or it stops working after a few connections (dispatchers are dying).
path “Patch 18610915: INTERMITTENT SEGMENTATION FAULT DURING NZ/PRNG INIT” is only available for 12.1.0.2.
once a session which is doing a transaction gets killed before the session commits or rollbacks oracle needs to recover(undo) the work done by this session. one way to watch the progress of this recovery is to use the v$fast_start_servers and v$fast_start_transactions views.
small test case:
-- FIRST SESSION drop user u1 cascade; create user u1 identified by u1 default tablespace users temporary tablespace temp quota unlimited on users; grant create session, create table to u1; grant select on dba_objects to u1; connect u1/u1 create table t1 as select * from dba_objects; insert into t1 select * from t1; / / / commit;
in a second session get the sid and serial# for the first session:
connect / as sysdba select sid,serial# from v$session where username = 'U1';
back in the first session do some (stupid) work on the table:
begin
while 1=1 loop
update t1 set OBJECT_NAME = OBJECT_NAME;
end loop;
end;
/
this will write undo data without committing the work. now, in the first session, kill the session which is doing the transaction:
alter system kill session '[SID_FROM_ABOVE],[SERIAL#_FROM_ABOVE]';
if your hardware is slow enough or you are fast enough or you keep the loop running for some time before killing the session you can now see the slave(s) doing recovery:
select * from v$fast_start_servers; STATE UNDOBLOCKSDONE PID XID ----------- -------------- ---------- ---------------- RECOVERING 9379 26 020021006D010000
… and the transaction(s) being recovered:
select * from v$fast_start_transactions;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ----------------
PXID RCVSERVERS
---------------- ----------
2 33 365 RECOVERED 19357 19357 11 020021006D010000
1
5 4 357 RECOVERED 17220 17220 13 0500040065010000
consider reading this before jumping on this train:
tested on 11.2.0.4 and 12.1.0.1 on Linux x64:
#!/bin/bash
SQLFILE=/var/tmp/subqueries.sql
echo "select * from ( " > ${SQLFILE}
for x in {1..100000}
do
if [ $x == "100000" ]; then
echo "select * from dual )" >> ${SQLFILE}
else
echo " select * from ( " >> ${SQLFILE}
fi
done
for x in {1..99999}
do
echo ")" >> ${SQLFILE}
done
execute the generated /var/tmp/subqueries.sql script:
@/var/tmp/subqueries.sql 200001 ; select * from ( * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 21287 Session ID: 34 Serial number: 2369
I do not say, that using 100000 subqueries is in any way useful :)
maybe this is not related to the amount of subqueries but rather to the number of lines or the number of characters in the statement. did not test this.
Daniel Westermann's Blog 