whorollback ()
#------------------------------------------------------------------------------#
#    Module Name:  whorollback                                                 #
#                                                                              #
#    Purpose:      Report on the Active Rollback/Undo Sessions.                #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 1 ]
        then
        	ORD_LNE="ROLL.name, "
        	ORD_LNE="PROC.username, SESS.username, PROC.spid, SESS.sid"
	        ORD_LNE="${ORD_LNE}, SESS.serial#, SESS.status, SQLA.piece"
                SQL_LNE="AND  ROLL.name = '${1}'       ORDER BY ${ORD_LNE}"
        else
        	ORD_LNE="ROLL.name, "
        	ORD_LNE="PROC.username, SESS.username, PROC.spid, SESS.sid"
	        ORD_LNE="${ORD_LNE}, SESS.serial#, SESS.status, SQLA.piece"
                SQL_LNE="ORDER BY ${ORD_LNE}"
        fi

        clear
	ORANAM=${ORACLE_SID}
	
#------------------------------------------------------------------------------#
#  Initialize Database System-ID (SID)                                         #
#------------------------------------------------------------------------------#
	appsel  ${ORANAM}     > /dev/null
	getpass                 ## RapidMagic Function: Get Passwords         ##

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  @<<-END_FILE

	CONNECT  SYS/$PWDORA

	clear	BREAKS
	clear	BUFFER
	clear	COLUMNS
	clear	COMPUTES
	clear	SCREEN
	clear	SQL
	clear	TIMING

	set	feedback	off
	set	flush		on
	set	heading		on
	set	pause		off
	set	space		1
	set	termout		on
	set	verify		on
	set	linesize	80
	set	pagesize	999
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  PROC_username   FORMAT  A6      Heading 'Unix|User'
        COLUMN  SESS_username   FORMAT  A6      Heading 'Oracle|User'
        COLUMN  PROC_spid       FORMAT  A5      Heading 'Unix|ID'
        COLUMN  SESS_sid        FORMAT  99999   Heading 'Oracle|Session'
        COLUMN  SESS_serial#    FORMAT  99999   Heading 'Session|Serial'
        COLUMN  SESS_status     FORMAT  A6      Heading 'Sess|Status'
        COLUMN  ROLL_name       FORMAT  A10     Heading 'UnDo|RollBack|Segment'
        COLUMN  SQLA_text       FORMAT  A24     Heading 'SQL Text' WORD_WRAPPED

        BREAK   ON PROC_username skip 1 ON SESS_username skip 0         -
                ON SESS_status   skip 0 ON SESS_sid      skip 0         -
                ON SESS_serial#  skip 0 ON PROC_spid     skip 1

        TTITLE  LEFT            DATEVAR                                 -
        RIGHT   'Page:'         FORMAT 999 SQL.pno                      -
        CENTER  'OneDresser DBA Report'				skip 1  -
        LEFT    TIMEVAR                                                 -
        CENTER  'Oracle ($ORANAM) User Undo/Rollback Info'	skip 1  -

       SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')         timevalue,
                ROLL.name			ROLL_name,
                PROC.username                   PROC_username,
                SESS.username                   SESS_username,
                PROC.spid                       PROC_spid,
                SESS.sid                        SESS_sid,
                SESS.serial#                    SESS_serial#,
                SESS.status                     SESS_status,
                SUBSTR(SQLA.sql_text, 1, 4000)  SQLA_text
          FROM  sys.v\$process  PROC,
                v\$transaction  TRAN,
		sys.v\$session  SESS,
		sys.v\$sqltext  SQLA,
                v\$rollname     ROLL
	 WHERE  PROC.addr               = SESS.paddr
           AND  SESS.username   IS NOT    NULL
           AND  SESS.sql_address        = SQLA.address(+)
           AND  SESS.sql_hash_value     = SQLA.hash_value(+)
	   AND  TRAN.addr        	= SESS.taddr
	   AND  TRAN.xidusn      	= ROLL.usn
         ${SQL_LNE};


EXIT;

END_FILE
print
}
