Use DBMS_UTILITY instead of SQLERRM

The function SQLERRM can be used to return an Oracle error message in PL/SQL to the calling environment.

In Oracle 10g the value which the function SQLERRM can return is something like 512,

prior to 10g it was 255.

 

Therefore Oracle recommends not to use this function but rather use

DBMS_UTILITY.FORMAT_ERROR_STACK instead of the SQLERRM function!!

This in fact returns the entire error stack!

Thanks to Steven Feuerstein for pointing this out to us today in Zurich.

 

Billiard with the PL/SQL evangelist

Yesterday we had a fun evening with Steven Feuerstein at the Spielhaus in Zurich a nice Billard saloon in Zurich where you can get a good dinner as well.

Steven is really good at billiard , I did not have a chance against him.

Laurent Schneider was around also

and we had lots of fun after a good dinner. The below picture shows Federica D’Incau from Oracle University, the good soul who is always behind the scenes and without whome these great OU events would never be possible.

Special thanks to Federica!!!

Steven Feuerstein delivering for Oracle University Switzerland

Yesterday Steven Feuerstein arrived at Zurich airport where I picked him up.

He will deliver a two day seminar for Oracle University about
best practices for PL/SQL programming.

The event takes place at the Sonnenberg convention center in Zurich,
where the Head Quarters of the World Soccer Association FIFA is located.

The below picture show Steven with the FIFA Cup .

I expect two days of fun with advanced PL/SQL techniques
and so do about 42 other programmers .
The event is completely sold out and
there will be a follow up event with Steven Feuerstein
probably since many people were not manage to get a seat any more
because of the high demand.

and this is as it was like, having fun with Steven Feuerstein in multiple ways:

Oracle course + Oracle course = IPOD

Today I could I could hand over the IPOD to one of my students
because he had booked two Oracle courses
between December 2006 and February 2007.

The foto shows me and Fred Goeres with his brand new 4GB IPOD.
Have fun with it Fred!

=;-)

Tom Kyte Interview in DOAG News 1/2007

Today I have received a copy of the DOAG news 1/2007
from the German Oracle User Group
with my Tom Kyte Interview in German 
.  Thanks to DOAG for sending it to me in Switzerland!

=;-)

Managed to get away from blogger finally

After I had more and more problems with the new blogger version
I have decided to move my blog to here.

Hope you can get used to the new style.

It is still undergoing reconstruction.

Pls keep on reading

Automatic Memory Management in Oracle 11g

Since quite a few days I had the chance to look at parts of the beta documentation for the Oracle 11g database and since a few days I also have the beta software.

One of the new features coming up is the enhencement of automatic memory management.

The whole automatic memory thing had already started with the release Oracle 9i where the parameter SGA_MAX_SIZE was introduced, to limit the maximum memory Oracle can use for the SGA. Within SGA_MAX_SIZE a number of caches were dynamically, but still manually adjustable. These caches included the
DB BUFFER CACHE, the
SHARED POOL, the
JAVA POOL and the
LARGE POOL.

In those days the DBA had to check statistics in v$views in order to find out if the caches needed manual adjustment.

Starting with Oracle 10gR1 the new parameter SGA_TARGET was introduced which allowed us to limit the amount of SGA_MAX_SIZE which can be used by Oracle.
If you set SGA_TARGET to a value other than 0 (zero) then Automatic Shared Memory Management (ASMM) is enbaled in 10g. This meens that we allow Oracle to adjust these caches as the workload needs it. We can increase this value dynamically, but manually by adjusting SGA_TARGET up to SGA_MAX_SIZE. In most cases it does not make sense to set SGA_TARGET to a value different from SGA_MAX_SIZE. This is only for systems like a SUN FIRE which allow the dynamic reconfiguration of the server (adding memory boards while the server is running).
Within SGA_TARGET a number of caches are autotuned, including the
DB BUFFER CACHE, the
SHARED POOL, the
JAVA POOL, the
LARGE POOL and starting from 10gR2 also the new in 10gR1
STREAMS POOL.
At startup time SGA_TARGET is allocated and first of all the non dynamically adjustable caches get their catch. Afterwards the others, the automatically tunable caches get their share.
SGA_MAX_SIZE is reserved for ORACLE at startup time, but as long as it is not touched it is not used.
As of Oracle 11g we will have one parameter which we can use to allow Oracle not only to adjust these five caches in the SGA but also we allow Oracle to shrink and grow the entire SGA memory in order to hand over memory to the PGAs (process memory) and vice versa.
This parameter is called MEMORY_TARGET. With this we can specify how much memory we want to allow ORACLE to use all over, including SGA and PGAs.
MEMORY_TARGET is a dynamic parameter and can be changed with an ALTER SYSTEM SET… statement. It can be adjusted up to the value of MEMORY_MAX_TARGET. Now we do not only have Automatic Shared Memory Mamangement but Automatic Memory Management (system memory plus process memory).
Here is what the reference says:
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

This is a feature we had expected to come up since a long time and I hope that it will work fine for at least most of the systems.
But I would suggest to first test it thoroughly before using it in production because all these new features which have AUTOMATIC in their names can have their issues at least in the first releases. And this is why they never are the default right away.

connect lutz hartmann as sysdba

DBA course under special conditions

This week I have delivered a 10g DBA course in Baden which was very special.
1. The course was in English which is not a problem
2. I had customers from three different nationalites which was very interesting

There was an Parminder, an Indian

and a Palestinean, and an Israeli.
It made me very happy to see the Hazim and Tsachi
sitting next to each other discussing techincal things
and even political and religious topics peacefully and having fun with each other.

3. The Baden Oracle plant is undergoing reconstruction
at the moment and it was not easy to keep up business as normal.
This picture show Barbara Haller wearing a dust mask because behing the reception there was heavy work going on.

Does anybody recognize the training room from last week?

connect lutz hartmann as sysdba