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.

another ouch: test-case

do a datapump export on 11.2.0.4 Linux x64:

connect / as sysdba
drop user u15 cascade;
create user u15 identified by u15 default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create table to u15;
connect u15/u15
create table test ( a number, b timestamp with time zone );
begin
for i in 1..10000
loop
insert into test values ( i
, to_timestamp_tz ('2014-06-06 21:07:46.214879 +2:00','YYYY-MM-DD HH24:MI:SSXFF TZH:TZM' )
);
end loop;
commit;
end;
/
select count(*) from test;
SQL> select min(b),max(b) from test;

MIN(B)
---------------------------------------------------------------------------
MAX(B)
---------------------------------------------------------------------------
06-JUN-14 09.07.46.214879 PM +02:00
06-JUN-14 09.07.46.214879 PM +02:00

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!expdp userid="'/ as sysdba'" parallel=4 dumpfile=test_%U.dmp logfile=test.log schemas=u15 directory=dir_tmp;

import the dump into a 12.1.0.1 database on linux x64:

connect / as sysdba
create or replace directory dir_tmp as '/var/tmp/';
!impdp parallel=2 userid="'/ as sysdba'" schemas=u15 dumpfile=test_%U.dmp logfile=test.log directory=dir_tmp;

… and check the timestamps:

U15@dbs101> select * from test order by 2;

A B
---------- ---------------------------------------------------------------------------
636 06-JUN-14 09.07.46.000000 PM +02:00
637 06-JUN-14 09.07.46.000000 PM +02:00
638 06-JUN-14 09.07.46.000000 PM +02:00
639 06-JUN-14 09.07.46.000000 PM +02:00

… outch

UPDATE: One workaround is to use tradtional exp/imp

simple test-case for 11.2.0.4 and 12.1.0.1 on linux x64:

connect / as sysdba
drop user u21 cascade;
create user u21 identified by u21 default tablespace users temporary tablespace temp;
grant create session, create table to u21;
connect u21/u21
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u21 cascade;
drop user u21 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

ouch …

does not happen with 1000 indexes:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

happens again with 1002:

connect / as sysdba
drop user u22 cascade;
create user u22 identified by u22 default tablespace users temporary tablespace temp;
grant create session, create table to u22;
connect u22/u22
declare
  lv_statement varchar2(32000) := 'create table t1 ( ';
  ln_max_indexes pls_integer := 1000;
  ln_max_columns pls_integer := 1000;
  ln_max_ind_columns pls_integer := 32; 
begin
  for i in 1..ln_max_columns
  loop
    if i < ln_max_columns
    then
      lv_statement := lv_statement || ' c'||i||' number,';
    else
      lv_statement := lv_statement || ' c'||i||' number';
    end if;
  end loop;
  lv_statement := lv_statement || ')';
  execute immediate lv_statement;
  for i in 1..ln_max_indexes
  loop
    execute immediate 'create index ii'||i||' on t1 ( c'||i||')';
  end loop;
  execute immediate 'create index i1001 on t1 (c1,c2)';
  execute immediate 'create index i1002 on t1 (c1,c2,c3)';
end;
/
select count(*) from user_indexes;
connect / as sysdba
drop user u22 cascade;

drop user u22 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01418: specified index does not exist

seems to be limit at 1000, somehow.

even more weird:

SYS@dbs101> select count(*) from dba_indexes where owner = 'U22';

  COUNT(*)
----------
	 0

SYS@dbs101> select count(*) from dba_objects where owner='U22';

  COUNT(*)
----------
       996