Session

Show all connected users

set lines 100 pages 999

col ID format a15

select username

,      sid || ',' || serial# "ID"

,      status

,      last_call_et "Last Activity"

from   v$session

where  username is not null

order by status desc

,        last_call_et desc

/

 Time since last user activity

set lines 100 pages 999

select username

,      floor(last_call_et / 60) "Minutes"

,      status

from   v$session

where  username is not null

order by last_call_et

/

 

Sessions sorted by logon time

set lines 100 pages 999

col ID  format a15

col osuser format a15

col login_time format a14

select  username

, osuser

, sid || ',' || serial# "ID"

, status

, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time

, last_call_et

from v$session

where username is not null

order by login_time

/

 

Show user info including os pid

col "SID/SERIAL" format a10

col username format a15

col osuser format a15

col program format a40

select s.sid || ',' || s.serial# "SID/SERIAL"

, s.username

, s.osuser

, p.spid "OS PID"

, s.program

from v$session s

, v$process p

Where s.paddr = p.addr

order  by to_number(p.spid)

/

 

Show a users current sql

Select sql_text

from   v$sqlarea

where  (address, hash_value) in

(select sql_address, sql_hash_value 

        from v$session

        where username like '&username')

/

 

Session status associated with the specified os process id

select s.username

, s.sid

, s.serial#

, p.spid

, last_call_et

, status

from  V$SESSION s

, V$PROCESS p

where s.PADDR = p.ADDR

and p.spid='&pid'

/

 

Display any long operations

set lines 100 pages 999

col username format a15

col message format a40

col remaining format 9999

select username

, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started

, time_remaining remaining

, message

from v$session_longops

where time_remaining = 0

order by time_remaining desc

/

Rman

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.
Login as sysdba -
This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;
This script will report all on full and incremental backups, not archivelog backups -
col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS where input_type='DB INCR' order by session_key;

SCRIPTS TO CHECK RMAN RESTORE STATUS:
set lines 230
col opname for a26
col progress for a15
col progress for a8
col TARGET for a20
col USERNAME for a12
col TIME_REMAINING for 99999
set pages 800
select a.sid,a.serial#,b.username,b.opname,LAST_UPDATE_TIME,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING <> 0 order by 6;

SCRIPT TO CHECK RMAN SID:
COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
;
 

SCRIPT TO CHECK RMAN TAPE PROGRESS:
COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
       STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR
/