Archives For May 2014

this one is really nasty (Bug 18097476, 12.1.0.1 on Linux x86-64 )

test-case (although slightly different from the description of the bug):

connect / as sysdba
drop user pp cascade;
drop user ppp cascade;
drop user pppp cascade;
create user pp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to pp;
connect pp/"Welcome1$"

create or replace package p1 as
  type t1 is record ( a number, b varchar2(10));
  type tt1 is table of t1;
  function pipelined return tt1 pipelined;
end p1;
/

create or replace package body p1 as
function pipelined return tt1 pipelined
as
  l t1;
  lt tt1 := tt1();
begin
  for i in 1..10
  loop
    lt.extend;
    l.a := i;
    l.b := 'aaaa';
    lt(lt.last) := l;
    pipe row(lt(lt.last));
  end loop;
  return;
end pipelined;
end p1;
/
select * from table (p1.pipelined);

connect / as sysdba
select owner,object_name,object_type,object_id from dba_objects where object_name = 'P1';
select owner,object_name,object_type,object_id from dba_objects where object_name like 'SYS_PLSQL%' and owner like 'PP%';

create user ppp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to ppp;

create user pppp identified by "Welcome1$" default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create type, create procedure to pppp;

create or replace directory dir_tmp as '/home/oracle/';

host rm -f /home/oracle/dmp.dmp
host expdp userid="'/ as sysdba'" schemas=pp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log
host impdp userid="'/ as sysdba'" remap_schema=pp:ppp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log
host impdp userid="'/ as sysdba'" remap_schema=pp:pppp directory=dir_tmp dumpfile=dmp.dmp logfile=dmp.log

select owner,object_name,object_type,object_id from dba_objects where object_name like 'SYS_PLSQL%' and owner like 'PP%';

the last select shoud return something similar to this:

OWNER	  OBJECT_NAME			 OBJECT_TYPE OBJECT_ID
--------- ------------------------------ ----------- ----------
PP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE	          20223
PP	  SYS_PLSQL_62B6CB68_9_1	 TYPE	          20222
PP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20224
PPP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE		  20273
PPP	  SYS_PLSQL_62B6CB68_9_1	 TYPE		  20272
PPP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20274
PPPP	  SYS_PLSQL_62B6CB68_DUMMY_1	 TYPE		  20301
PPPP	  SYS_PLSQL_62B6CB68_9_1	 TYPE		  20300
PPPP	  SYS_PLSQL_62B6CB68_24_1	 TYPE		  20302

if now, for whatever reason, someone drops one of the “not” dummy_1 types:

drop type SYS_PLSQL_62B6CB68_24_1;

… the result is, that you can not drop or compile the package anymore:

alter package p1 compile;
alter package p1 compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_62B6CB68_24_1 does not exist

or, try to compile the package for the second schema without droppping a type before (does not happen on the third one :) ):

SYS@dbs101> alter package ppp.p1 compile;
alter package ppp.p1 compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_62B6CB68_24_1 does not exist

suprisingly this does not happen with 2 schemas in my case. it starts when a third schema is present.

fyi: oracle creates this SY_PLSQL* as soon as you create a package which contains pipeplined functions which return types defined in the package (bad sentence, I know :) ).

UPDATE 2014-JUL-18:Patch 18097476 is currently available on top of 12.1.0.1 for Linux x86-64.
UPDATE 2015-FEB-05:Same issue reproduces on 12.1.0.2 (with PSU2 applied) on Linux x86-64. No patch available currently
UPDATE 2015-FEB-09:Patch 19504744 is vailable for 12.1.0.2

Advertisements

If you have issues connecting to an APEX instance and firefox responds with:

firex error message

… you probably have the issue that someone told APEX to require https connections but https is not working for any reason. To at least enable the http connections again issue:

ALTER SESSION SET CURRENT_SCHEMA = [APEX_USER];
BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER('REQUIRE_HTTPS', 'N');
commit;
end;
/

In may case the reason for https not to work anymore is still under investigation, but it seems to be related to this:

select * from v$wallet;
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 1100
Session ID: 566 Serial number: 795

Which leads to a nice core dump in the alert-log:

Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x47A1644, __intel_new_memcpy()+52] [flags: 0x0, count: 1]
Errors in file xxxxxxx.trc  (incident=238044):
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+52] [SIGSEGV] [ADDR:0x0] [PC:0x47A1644] [SI_KERNEL(general_protection)] []
Incident details in: xxxxxx.trc

in oracle 12.1 there appeared a column in v$instance which is not documented: v$instance.family

oracle docs

another new column which appeared is: v$instance.edition (which is documented):

EDITION 	VARCHAR2(7) 	The edition of the database.
Possible values include:
    CORE EE
    CORE SE
    EE
    PO
    SE
    XE

I never heard of oracle editions CORE EE, CORE SE and PO. A look at the source of gv$instance lists even more but others are missing:

decode(ksuxsedition,  2, 'PO', 4, 'SE', 8, 'EE', 16, 'XE', 32, 'CS', 40, 'CE', 'UNKNOWN')

Perhaps Oracle Support can put some light on this :) seems to be somehow related to cloud installations, but this is just a guess …

ON NULL?

May 6, 2014 — Leave a comment

and another little new feature of oracle 12c: ON NULL for table columns definitions

SQL> !cat a.sql
CREATE TABLE T1 ( a number
                , b varchar2(5) default on null 'aaaaa' not null );

insert into t1 ( a,b ) values ( 1,'aaaa');
insert into t1 ( a,b ) values ( 1,null);
select * from t1;

	 A B
---------- -----
	 1 aaaa
	 1 aaaaa

on null