Re: rollback info

From: AJ Allen <aallen20_at_ford.com>
Date: Fri, 04 Feb 2000 13:57:00 -0500
Message-ID: <389B20FC.4BA644E9@ford.com>

Vitalij Serdakovskij wrote:
>
> How can I see session using specific rollback segment?
> Thanks.

Try the following. It works for me.

• - - - - C U T H E R E - - - - - - REM standard output (SET)tings

SET TERMOUT OFF
SET HEADING ON SET PAGESIZE 70
SET LINESIZE 80
SET NEWPAGE 0

SET VERIFY     OFF
SET ECHO       OFF

SET UNDERLINE =
SET FEEDBACK Off

SET LONG       1000
SET EMBED      ON

COLUMN sys_id       NOPRINT new_value system_id
COLUMN sys_date     NOPRINT new_value system_date
COLUMN sys_user     NOPRINT new_value system_user

COLUMN rbs          FORMAT a6         HEADING ' RBS'
COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username'
COLUMN sid          FORMAT 99999      HEADING ' SID'
COLUMN serial#      FORMAT 99999      HEADING 'Serial#'
COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID'
COLUMN Client_User  FORMAT a12        HEADING 'Client|Username'
COLUMN Unix_user    FORMAT a12        HEADING 'O/S|Username'

TTITLE Right  'Date: ' system_date   -
  skip Left   'Database: ' system_id -
       Center 'Who/What is Using Which RBS'  -
       Right  'User: ' system_user   -
  skip Center '~~~~~~~~~~~~~~~~~~~~~~~~~~~' -

  skip 2

REPFOOTER -
     SKIP 3 -
   CENTER '### ## END OF REPORT ## ###' BTITLE -
SKIP 2 RIGHT 'Page: ' format 99 sql.pno

SELECT  d.name               sys_id,
        to_char(SysDate,'mm/dd/yyyy  HH24:MI:SS') sys_date,
        User                 sys_user

  FROM v$database d
;

SET termout ON
SET heading ON

SPOOL rbs_users.lst

SELECT r.name                   rbs,
       nvl(s.username, 'None')  oracle_user,
       s.osuser                 client_user,
       p.username               unix_user,
       s.sid                    sid,
       s.serial#                serial#,
       p.spid                   unix_pid
  FROM v$process     p,
       v$rollname    r,
       v$session     s,
       v$transaction t
 WHERE s.taddr = t.addr

   AND s.paddr = p.addr(+)
   AND r.usn = t.xidusn(+)
 ORDER
    BY r.name
;

--SPOOL OFF
--EXIT;

• - - - - - - - - - - - - - - - - - -

--

*------------------------------*----------------------------*
| Andrew Allen                 | Against fools, the gods    |
| Process Leadership / PSL DBA | themselves contend in vain |
| Dearborn, MI USA             | -- author unknown          |
*------------------------------*----------------------------*

Received on Fri Feb 04 2000 - 12:57:00 CST