Archives For July 2014

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

Advertisements

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
26-AUG-2014 UPDATE 2: the workaround is no workaround if you use encrypted tablespaces as exp/imp can not handle this

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