Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How long does the Oracle call_stack stay available?

Randy Hagedorn
Regular Advisor

How long does the Oracle call_stack stay available?

Hi oracle database guru's:

We am investigating and trying to learn more about Oracle's database 'call_stack'. From what we have learned so far is that the call_stack, (when a procedure is executed from PL/SQL), the call_stack lists what is currently being executed in the procedure. So if an error occurs, it can show what was exactly happening at the time.

Our question:
How long is the information in the call_stack available for interegation? Is it only available during the current oracle session or can it be examined even from a non-current session? In other words, if a PL/SQL procedure would have an error, could we examine the data in the call_stack after the session has ended?

Thanks in advance,
Randy
2 REPLIES
Mr. Sandeep Kapare
Occasional Visitor

Re: How long does the Oracle call_stack stay available?

hi

it depends upon the number of distinct calls to the database. if the database is been bombed with tonnes of ad-hoc queries then chances are there that the call_stack would be empty soon. otherwise you can pin the object in pl/sql with dbms_shared_pool package and have the call_stack of that particular pl/sql always available.

hope this reply to some of your question.
Brian Crabtree
Honored Contributor

Re: How long does the Oracle call_stack stay available?

Sandeep,

Kind of. The call stack is based primarily on the shared pool size (large shared pool, more space for SQL statements). Technically, distinct calls in a slow database can last for quite a while.

However, errors are not shown in the call stack, so while you would see the procedure being run in the v$sqlarea or v$sqltext, you would not necessarily see why it failed. You can get around this by coding in checkpoints in the script (ie: insert a check into a table, etc), or setting up an EXCEPTIONS clause to error out with information on what failed.

Also, whoever is getting the output of the failure (stdout, dbms_job, etc) should receieve a set of error messages defining what failed where.

To summarize, you can view SQL statements from other sessions. You will need to get the SQL_ADDRESS and PREV_SQL_ADDRESS from the v$session view for the session that you want to query, and select that information from the ADDRESS column in the v$sqlarea or v$sqltext views. I prefer v$sqltext for this, however you will want to make sure that you order by the address and piece columns to sort the data.

Hope this helps,

Thanks,

brian