Skip to main content

Who am I?

Quite often, a procedure needs to know some information about the session it is running in - at least for logging some information.

It seems to be easy to find this information - just look at SYS_CONTEXT documentation, and find the impressive list of attributes of USERENV context. And if something is missing, just grab the SESSIONID, SID or AUDSID and query v$session or other view.

Well, this works nicely, as long as you don't do it in a job. Unfortunately, in a job you always get 0.
You can try to battle with FG_JOB_ID and BG_JOB_ID, but I think this deserves a more elegant solution - and that's why I always query v$mystat to get the SID of my process. Just fetch the first line:
select * from v$session where sid = (select sid from v$mystat where rownum=1)
You need a select grant on v_$mystat by SYS, but as this view does not expose anything important, it's not a security issue.

The next question that comes is: if I am run by a job (let's talk about dbms_job now), what's my job id?

The universal solution is to query v$lock and check for the JQ lock you are holding - and id2 is jor id:
select id2 from v$lock where type = 'JQ' and sid = (select sid from v$mystat where rownum=1)
However, there is one more elegant solution - the dbms_job engine does not run just the "what" code specified by you - it wraps it in a short anonymous block. In this block, a variable called "job" is defined and binded to contain just the id you need. So, in your "what" code, just use this variable:
dbms_job.submit(j, 'my_proc(job, my_param1, my_param2);'
If you are curious, this block will be run:
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN my_proc(job, my_param1, my_param2);
:mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0;

For dbms_scheduler, see for example discussion at I still personally don't like it, it's too cumbersone for the things I do (a workflow engine, for example).


Popular posts from this blog

ORA-27048: skgfifi: file header information is invalid

I was asked to analyze a situation, when an attempt to recover a 11g (standby) database resulted in bunch of "ORA-27048: skgfifi: file header information is invalid" errors.

I tried to reproduce the error on my test system, using different versions (EE, SE,,, but to no avail. Fortunately, I finally got to the failing system:

SQL> recover standby database;
ORA-00279: change 9614132 generated at 11/27/2009 17:59:06 needed for thread 1
ORA-00289: suggestion :
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-00280: change 9614132 for thread 1 is in sequence #208

Interestingly, nothing interesting is written to alert.log n…

Reading data from PGA and SGA

Overview For our investigation of execution plan as it is stored in memory, we need in the first place to be able to read the memory.

We have the options of
x$ksmmem, reading SGA using SQL. Personally I don't like it, it's cumbersome and SGA read: obviously reading SGA only; it's fast and easy to doread process memory: can read PGA, process stack - and since the processes do map the SGA, too, you can read it as well. Unfortunately ptrace sends signals to the processes and the process is paused when reading it, but so far all my reads were short and fast and the processes did not notice. Some OS configurations can prevent you from using ptrace (e.g. docker by default), google for CAP_SYS_PTRACE.gdb: using your favorite debugger, you can read memory as well. Useful when investigating. Direct SGA read I always considered direct SGA read of some dark magic, but the fundamentals are actually very easy. It still looks like sorcery when actually reading the Oracle in…

A few thoughts about OCM 12c upgrade

Yesterday I sat for the 12c OCM upgrade exam, which I mentioned in few blog posts before. The first step after checking your ID is of course signing the NDA, and thus you won't find much real information here.

This time I chose Utrecht as the place to take the exam. Not that I have any special preference, I took each of the exams in a different place so far. The only requirements were convenient time and location defined as 'somewhere in Europe'. But in the end, Utrecht turned out to be a good place. Oracle NL headquarters are easy accessible, it's a very new building, the lunch was good:-)
And the city is nice to see.

Regarding the exam, the usual important notes still hold true:

Arrive on time. It's a long day and you will have a lot of things to do.You will work hard the whole day. Get a good sleep before, be well rested.Review the exam topics well. Note that they may have change over time. There is for example an update as of January 1, 2016: Flex ASM was added…