Oregon Sues Oracle

a german version:
Oregon verklagt Oracle

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:

Oracle’s In-Memory Database: The True Cost Of Licensing

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.

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)

today (22-JUl-2014) I spend some minutes to check the amount of patches available in mos for linux x64 by oracle version (only base releases and patchsets as 10.1.0.1.0 or 11.2.0.4.0 ). The following picture shows an example of the search filters used:

search filter example

here is the result:
search results

10.1.0.3.0 is the oldest available release in the list.