RE: Oracle What savepoints are active for a given session?

Hi Venkata,

There is no V$ view or X$ table that contains this information. The only solution that occurs to me is to dump the savepoints to the process trace file with

    alter session set events 'immediate trace name savepoints';

and then use UTL_FILE to read and parse the trace file information. Here is an example of what you might get:

SAVEPOINT FOR CURRENT PROCESS

   flag: 0x3
   name: S3
   dba: 0x831408, sequence #: 964, record #: 12, savepoint #: 19082    status: VALID, next: 3822f60
   name: S2
   dba: 0x831408, sequence #: 964, record #: 11, savepoint #: 19046    status: VALID, next: 37f63ec
   name: S1
   dba: 0x831408, sequence #: 964, record #: 10, savepoint #: 18602    status: VALID, next: 0

This process has three savepoints named S1, S2 and S3 respectively.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
Sent: Tuesday, 1 May 2001 18:20
To: Multiple recipients of list ORACLE-L

HI PLEASE SEND ME ANSWER
Question Title: Oracle What savepoints are active for a given session?

Detailed Question: Does anybody know IF and HOW can I get a list of active savepoints for the current session? I need a native way, not solutions based on additional application-level housekeeping. Some query on the V$ tables/views would be the kind of answer I'm looking for. Details: Within one stored proc I'd like to obtain a list (in any form) of the savepoints issued currently in the current transaction. Example: Proc A issues savepoint svA; then proc B issues savepoint svB; and then proc C builds and uses a cursor having 'svA' and 'svB' as rows, or something like that. Of course, there are no intervening commits or rollbacks.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).