hardware lasts longer than software

A week ago, Oracle has replaced its good old support platform MetaLink with MyOracleSupport (MOS).
The thing I like most about MOS is the non Flash-based HTML-Version.
Many discussions about the need, target and failures during the last week of transition are ongoing in blogs, mailing lists, forums; I also had to suffer and had to communicate it. But that's not what's this blog is about.

I got into touch with MetaLink in 2000, as Oracle tried to move the customers from calling the Support to a self-service platform. They promoted MetaLink on many events, with everything you can imagine. The transition was not forced, but within a short period of time most customers liked the possibility to get many answers faster than on the phone. A real win-win situation.
Also I learned how to use MetaLink effective and therefore like it.



On one of these events, I got the promo-mug. I like it, because I like MetaLink.
Nowadays, everytime I feel without any Support from Oracle, I cling to the mug, hopefully filled with good, strong, hot coffee. This makes me feel a little bit better.
And sometimes, hardware really lasts longer than software, especially if you have the hardware in your hands and take care of.

rollback in PL/SQL

I recently came across a slightly, but possible dangerous mismatch between transactions and the PL/SQL code based on it.
Here a cut down example (not very realistic, but easy to follow):

create table mytab (myval number);

create or replace procedure tuwas (
    varA in number,
    varB in number,
    varC OUT number)
is
begin
 varC := varA;
 insert into mytab values (varA);
 if varB = 0 then
   commit;
 else
   rollback;
 end if;
end;
/

The Idea behind the code is, to get varA and varB as input parameters, do some calculations (missing here), insert the result into a table and return the calculated value for next steps.
There might be reasons to rollback the DML within the procedure, which is not seen as an error.

And here the result of the procedure:

set serverout on

declare
 vA number;
 vB number;
 vC number;
begin
 vA := 1;
 vB := 0; -- commit;
 tuwas(vA, vB, vC);
 dbms_output.put_line('vC: ' ||vC);
 vA := 2;
 vB := 1; -- rollback;
 tuwas(vA, vB, vC);
 dbms_output.put_line('vC: ' ||vC);
END; 
/

vC: 1
vC: 2

select * from mytab;

    MYVAL
----------
        1

What's dangerous here? Even with

 vA := 2;
 vB := 1;

the return value

vC: 2

is given. But t's not visible in the table, as the procedure did a rollback.
I learned from this example never to populate return-values until you are sure it's really stored (if this is a requirement, of course).

Oracle Security Options

Last week I attended a 1 day Seminar about Oracle Security by Dominique Jeunot.
Dominique did a great job to squeeze everything she knows into one day, and it was allways obvious there is a lot more she could tell us in the script and even more in her brain. Nevertheless she created a very good overview with the right level on all topics. It was obvious she could only make a kind of teaser, but it was a very tasty one!
Maybe the most important picture she draw was the first overview about all available features, options and products. I did the effort to draw it for my own joy and will share it here, as it might be helpful for others as well. All options are colored red, so you can see where you have to take extra money.

In general you can say: for nearly every issue there are possibilities to do it without extra money (but extra effort) or you can buy a solution.

If someone wants the visio behind this jpeg, just contact me :)

ML-fun

sometimes it's really funny to see error messages.
This ony I got today:

I think, I'm missing my workspace (or is it the application, who knows ...)

Maybe this is only because of the ongoing transition phase and within the next days everything will be better...

xplan 2.1.3 is out

Alberto Dell'Era released a ne version of his xplan utility. Version 2.1.3 is out now.
Why is it's worth to mention?
1st he fixed a problem I faced within extraordinary speed (< 4 hrs!):

declare /* xplan_exec_marker */  *** error before main block (
processing XPLAN_OPTIONS ) *** -- main block
                                *
ERROR at line 1:
ORA-06550: line 1, column 34:
PLS-00103: Encountered the symbol "*" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

--in setup.lst it's this error:

declare /* xplan_exec_marker */ -- process options
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 28

and 2nd: he didn't even announce it himself, so someone must do it :)

result cache not enabled for owner SYS and SYSTEM

EDIT: the original title of this blog was "result cache not enabled in SYSTEM and SYSAUX Tablespace".
After a totally correct post from Coskan I had to rework it totally. I mixed up the problem and conclusio so much it was totally messed up.

I hope this one is the better one:

---

During some Tests with result cache in 11gR2 I discovered a small docu bug.

You can still see the bug in the 11gR1 docu:

You cannot cache results when you use the following database objects or functions in your SQL query:

• Dictionary and temporary tables

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, and sys_timestamp

• Non-deterministic PL/SQL functions

In 11gR2 it sounds slightly different: 7.6.4.2 Additional Requirements for the Result Cache:

You cannot cache results when the following objects or functions are in a query:

• Temporary tables and tables in the SYS or SYSTEM schemas

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, andSYS_TIMESTAMP

for those of you who want to run the testcase:

create tablespace berx1 datafile '+DG' SIZE 50M;
create user berx1 identified by berx1;
grant connect to berx1;
grant unlimited tablespace to berx1;

create table sys.berx1_1 tablespace system as select * from dual;
create table sys.berx1_2 tablespace sysaux as select * from dual;
create table sys.berx1_3 tablespace berx1 as select * from dual;
create table system.berx1_4 tablespace system as select * from dual;
create table system.berx1_5 tablespace sysaux as select * from dual;
create table system.berx1_6 tablespace berx1 as select * from dual;
create table berx1.berx1_7 tablespace system as select * from dual;
create table berx1.berx1_8 tablespace sysaux as select * from dual;
create table berx1.berx1_9 tablespace berx1 as select * from dual;
grant select on sys.berx1_1 to berx1;
grant select on sys.berx1_2 to berx1;
grant select on sys.berx1_3 to berx1;
grant select on system.berx1_4 to berx1;
grant select on system.berx1_5 to berx1;
grant select on system.berx1_6 to berx1;

set autotrace on

-- run as user sys, system and berx1:

select /*+ result_cache */ * from sys.berx1_1;
select /*+ result_cache */ * from sys.berx1_2;
select /*+ result_cache */ * from sys.berx1_3;
select /*+ result_cache */ * from system.berx1_4;
select /*+ result_cache */ * from system.berx1_5;
select /*+ result_cache */ * from system.berx1_6;
select /*+ result_cache */ * from berx1.berx1_7;
select /*+ result_cache */ * from berx1.berx1_8;
select /*+ result_cache */ * from berx1.berx1_9;

my results are reflected in this matrix:

	berx1_1	berx1_2	berx1_3	berx1_4	berx1_5	berx1_6	berx1_7	berx1_8	berx1_9
sys	N	N	N	N	N	N	Y	Y	Y
system	N	N	N	N	N	N	Y	Y	Y
berx1	N	N	N	N	N	N	Y	Y	Y

So in my testcase it's not dependend of the schema (or the data dichtionary), but the tablespace.
This issue is addressed in BUG:8558309 and fixed in the latest docu :)

I know, I should never create any objects in SYS or SYSTEM schema. Especially if you want this object to be cached in result cache.

Once again thank you Coskan for showing my ridiculous error.

-prelim and direct_access

I recently asked a question about the 'difference' between /prelim and direct_access on oracle-l.
Tanel Poder gave a great answer. Now it's nearly clear, at least for me:

-prelim allows sysdba to start a preliminary connection to db, which
means that a server process is started for you and it attaches to sga
segments, but it does not allocate anything inside sga (no process
state nor session state object and session structures are allocated
for example). This means tha no latch gets are needed in prelim mode
and if you have an instance hang due these latches your session wont
get stuck. You can do very limited things with prelim mode, mostly
just various dumps with oradebug.

Oradebug direct-access allows you to also query few x dollar tables
with sql-like syntax. thats fake sql though its oradebugs hack, not
real sql. The same is doable with oradebug dumptype as well.

Or, to summarise it (just for me):

• -prelim allows me to start a sqlplus binary and connect it to the (correct ≈ right $ORACLE_HOME and $ORACLE_SID) SGA without creating anything within this SGA. So for me it's the cheapest way to get access to the shared memory of the specific instance.
• this prelim state is of no really use in ordinary life. Just for some really rare occasions, it might be of any use. Most of these are oradebug variants. I have not tested any ALTER SYSTEM or ALTER SESSION statement yet. Some of them might be worth testing, but most will fail. Ordinary SQL will fail all the time (I'm sure).
  
• oradebug direct-access is a nice gadget to query some x$ tables without really going through all the sql-parse-pin-heap-whatever engine ;-)
• at last, -prelim is a kind of last resort to access an instance and at least get some informations, until some really hard direct SGA access methods must be considered