Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
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