rem Xuser.sql - ALL Database users
rem Shows all users defined in the Database.
col temporary_tablespace format A20
col default_tablespace format A18
select username,default_tablespace,temporary_tablespace
from dba_users
/
rem Xactive.sql - Connected users
rem Shows all users connected to the instance.
col SID for 999
col UnixID for A7
col object for A30
col command for A8
col username for A14
col OSuser for A12
col process for A7
select S.SID
,S.username
,S.OSuser
,initcap(S.status) Status
,S.terminal
,decode(S.lockwait,NULL,'No','Yes') Lockwait
,initcap(A.Name) Command
,S.process
,P.SPID "UnixID"
from v$SESSION S
,v$PROCESS P
,sys.AUDIT_ACTIONS A
where S.username is not null
and A.action = S.command
and P.ADDR = S.PADDR
/
rem Name: XObj.sql - Accessed objects
rem Date: 27-sept-2000
rem Desc: Shows number of users accessing each table ordered by object name
col object for A34
col owner for A16 trunc
col type for A12 head Obj|type
col accessed for 9999 head "Accessed|by # users"
select A.owner,A.object,A.TYPE,count(*) accessed
from v$ACCESS A
where A.owner not in ('SYS','SYSTEM')
and A.TYPE = 'TABLE'
group by A.owner,A.object,A.TYPE
order by accessed
/
rem Name: XObj1.sql
rem Date: 20-dec-2000
rem Desc: Shows users accessing a specific table.
col SID for 999
col owner for A10 trunc
col type for A6 trunc
col object for A24 trunc
col command for 999
col username for A14
set linesize 90
select S.SID
,S.username
,S.OSuser
,A.owner
,A.object
,A.TYPE
,S.command
from v$ACCESS A
,v$SESSION S
,AUDIT_ACTIONS C
where S.username is not null
and S.SID = A.SID
and A.owner not in ('SYS','SYSTEM')
and A.TYPE = 'TABLE'
and C.action = S.command
and A.object like upper('&Object_name%')
/
rem XStart.sql - Activity by starttime
rem Shows all users connected to the instance, first_load_time and activity.
col SID for 999
col username for A14 head Username
col D1 for A6 head Date
col users_opening for 999 head Users|open
col users_executing for 999 head Users|exec
col executions for 99999999 head Exec#
col buffer_gets for 99999999 head Buffer|gets
col buff_exec for 999999999.9 head Gets/|exec
select S.SID
,S.username
,to_char(
to_date(substr(first_load_time,3,8),'YY-MM-DD')
,'MON-DD') D1
,substr(first_load_time,12,10) "Time"
,A.users_opening
,A.users_executing
,A.buffer_gets
,A.executions
,buffer_gets/executions buff_exec
from v$sqlarea A
,v$session S
,v$process P
where address=sql_address
and P.addr = S.paddr
and S.username is not null
order by buffer_gets
/
rem Name: NOW.sql - SQLText executing now
rem Date: 1-nov-2002
rem Note: If users_executing=0 then it's the last statement of an idle user.
set pages 60 lines 100 feed OFF wrap on
col SID for 999
col username for A10 trunc
col osuser for A10 trunc
col now for A5 head 'Exec|now'
col buffer_gets for 9999999 head "Buffer|reads"
col disk_reads for 999999 head "Disk|reads"
col executions for 999999 head 'Execs'
col rows_processed for 999999 head 'rows|procesd'
col firstload head 'First|load'
col sql_text for A35
select SID
,S.username
,S.OSuser
,decode(A.users_executing,0,'*idle',A.users_executing) NOW
,A.buffer_gets
,A.executions
,A.rows_processed
,substr(A.first_load_time,12,5) Firstload
,A.sql_text
from v$SQLAREA A
,v$SESSION S
where A.address = S.sql_address
and S.audsid != userenv('SESSIONID')
order by now,SID
/
rem Name: LongOps.sql - Long operations
rem date: 11-nov-2002
col SID for 999
col username for A10 trunc
col sofar for 9999999
col target for A45 trunc
col message for A40 trunc
col Strt head 'Start|time'
col units for A10
col time_remaining for 999999 head 'Remain|sec'
col elapsed_seconds for 999999 head 'Elapsd|sec'
prompt
prompt Full Table Scans suggests some performance tuning of stmts is useful
select target
,to_char(start_time,'DD-MON') Strt
,sum(ELAPSED_SECONDS) elapsed_seconds
,count(*)
from v$session_longops
where opname = 'Table Scan'
group by target,to_char(start_time,'DD-MON')
/
prompt where remain > 0 :
select sid
,username
,to_char(start_time,'hh24:mi') strt
,sofar
,totalwork
,elapsed_seconds
,time_remaining
,units
,OPname
from V$SESSION_LONGOPS
where time_remaining > 0
order by time_remaining
/
rem XKill.sql - Kill a user
rem Shows 1 or all users (return=all); and kills a user defined by SID,serial#.
rem Press return to kill no users.
col C1 for A10 head UserID
set verify off
prompt Enter a username (return=all)
select SID,serial#,SID||','||serial# C1,username,status
from v$session
where username like upper('&username%')
/
prompt Now kill the user: (return = kill no users)
alter system kill session '&UserID'
/
select SID,serial#,username,status
from v$session
where username = upper('&username')
/
undefine username
rem Rbs1.sql
rem name: RBS1.sql
rem date: 17-sep-2004
rem Show objects accessed and rollback usage.
rem ====================================================================
set lines 100
col USED_UREC for 9,999,999 head "Undo|records"
col SID for 999
col Strt head "Strt|time"
select
e.SID "SID",
substr(a.os_user_name,1,8) "OS User",
substr(a.oracle_username,1,8) "DB User",
substr(b.owner,1,8) "Schema",
substr(b.object_name,1,20) "Object Name",
substr(b.object_type,1,10) "Type",
substr(start_time,10,5) Strt,
substr(c.segment_name,1,5) "RBS",
substr(d.used_urec,1,12) "Undo Records"
from
v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
where a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
;
rem Rbs2.sql
rem date: 10-mar-99
rem desc: Show rollback stats and optsize in Kbyte.
rem ====================================================================
set linesize 90
set numwidth 9
set feed 10
col name for A10
col rssize for 99999999 head 'Size|Kbyte'
col optsize for 99999999 head 'Optimal'
col hwmsize for 99999999 head 'Size|Hwmark'
col XACTS for 999
col shrinks for 999
col waits for 999
col wraps for 999
col extends for 999
col status for A8
select N.name
,rssize/1024 rssize
,S.optsize/1024 optsize
,S.hwmsize/1024 hwmsize
,shrinks
,extends
,wraps
,Xacts
,writes
,waits
-- ,aveactive
,status
from v$rollname N
,v$rollstat S
where N.usn = S.usn
order by 1
/
set feed 1
rem Rbs3.sql
rem date: 01-apr-01
rem ====================================================================
set feed 10 linesize 90
col Rname for a10 head "Rollback|Segment"
col sz for 999,999 head "Size|Kbyte"
col com for a15 head "Command|desciption"
col command for 999 head "Com-|mand"
col extents for 999 head "Extents"
col waits for 999 head "Waits"
col username for a12 head "Username" trunc
col USED_UREC head "Undo|records"
prompt Rollback Segment info Transaction info
prompt ================================= ============================>
select N.name Rname
,R.rssize/1024 sz
,R.waits
,R.extents
,S.SID
,S.username
,USED_UREC
-- ,LOG_IO
,S.command
,decode(s.command,0,'Unknown',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
6,'UPDATE',
7,'DELETE',
9,'CREATE INDEX',
10,'DROP INDEX',
12,'DROP INDEX',
26,'LOCK TABLE',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
48,'SET TRANSACTION',
NULL, NULL,
'see: AUDIT_ACTIONS') com
from v$session S
, v$transaction T
, v$rollstat R
, v$rollname N
where S.taddr (+) = T.addr
and T.xidusn(+) = R.usn
and N.usn = R.usn
order by N.usn
/