SET LINESIZE 145
SET PAGESIZE 66
COLUMN owner FORMAT a5 HEADING 'Owner'
COLUMN object_type FORMAT a10 HEADING 'Type'
COLUMN object_name FORMAT a25 HEADING 'Name'
COLUMN Object FORMAT a27 HEADING 'Object'
COLUMN locked_mode FORMAT a20 HEADING 'Locked Mode'
COLUMN sid FORMAT 999999 HEADING 'DB SID'
COLUMN username FORMAT a15 HEADING 'Locker'
COLUMN osuser FORMAT a10 HEADING 'O/S User'
COLUMN logon_time HEADING 'Login Time'
prompt
Prompt +-----------------------------------------------------------------+
Prompt | Table Locking Info FROM v$locked_object, dba_objects, v$session |
Prompt +-----------------------------------------------------------------+
SELECT
SUBSTR(b.owner, 1, 8)||'.'||SUBSTR(b.object_name, 1, 18) "Object"
, b.object_type object_type
, DECODE(a.locked_mode
, 0, 'NONE'
, 1, 'NULL'
, 2, 'ROW SHARE'
, 3, 'ROW EXCLUSIVE'
, 4, 'SHARE'
, 5, 'SHARE ROW EXCLUSIVE'
, 6, 'EXCLUSIVE') locked_mode
, a.session_id "DB Sid"
,a.oracle_username "Locker"
, a.os_user_name osuser
, TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time
FROM
v$locked_object a
, dba_objects b
, v$session c
WHERE
a.object_id = b.object_id
AND a.session_id = c.sid
ORDER BY
b.owner
, b.object_type
, b.object_name
/
COLUMN UserName FORMAT a20 HEADING 'UserName'
COLUMN "DB Sid" FORMAT 999999 HEADING 'DB Sid'
COLUMN "Unix Pid" FORMAT 99999999
COLUMN "Trnx_start_time" FORMAT a19
COLUMN "Current Time" FORMAT a19
COLUMN "Elapsed(mins)" FORMAT 999999999.99
COLUMN "Undo Name" FORMAT a09
COLUMN "Used Undo Blks" FORMAT a13
COLUMN "Used Undo Size(Kb)" FORMAT a17
COLUMN "Logical I/O(Blks)" FORMAT 99999999999999999
COLUMN "Logical I/O(Kb)" FORMAT 999999999999999
COLUMN "Physical I/O(Blks)" FORMAT 999999999999999999
COLUMN "Physical I/O(Kb)" FORMAT 999999999999999999
Prompt +-------------------------------------------------------------------------------------------------+
Prompt | User Transactions Info FROM v$session, v$transaction, dba_rollback_segs, v$parameter, v$process |
Prompt +-------------------------------------------------------------------------------------------------+
SELECT
a.username "UserName"
, a.sid "DB Sid"
, e.spid "Unix Pid"
, TO_CHAR(TO_DATE(b.start_time,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') "Trnx_start_time"
, TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time"
, ROUND(60*24*(sysdate-to_date(b.start_time,'mm/dd/yy hh24:mi:ss')),2) "Elapsed(mins)"
, c.segment_name "Undo Name"
, TO_CHAR(b.used_ublk*d.value/1024) "Used Undo Size(Kb)"
, TO_CHAR(b.used_ublk) "Used Undo Blks"
, b.log_io "Logical I/O(Blks)"
, b.log_io*d.value/1024 "Logical I/O(Kb)"
, b.phy_io "Physical I/O(Blks)"
, b.phy_io*d.value/1024 "Physical I/O(Kb)"
, a.program
FROM
v$session a
, v$transaction b
, dba_rollback_segs c
, v$parameter d
, v$process e
WHERE
b.ses_addr = a.saddr
AND b.xidusn = c.segment_id
AND d.name = 'db_block_size'
AND e.ADDR = a.PADDR
ORDER BY 4
/
Prompt +-------------------------------------------------------------------------------+
Prompt | Objects owned by users being accessed FROM v$session, v$transaction, v$Access |
Prompt +-------------------------------------------------------------------------------+
COLUMN TYPE FORMAT a24 HEADING 'Type'
COLUMN object FORMAT a30 HEADING 'Object'
COLUMN UserName FORMAT a20 HEADING 'UserName'
COLUMN "DB Sid" FORMAT 999999
SELECT
a.username "UserName"
, a.sid "DB Sid"
, SUBSTR(c.owner, 1, 8)||'.'||SUBSTR(c.object, 1, 18) "Object"
, c.type "Type"
FROM v$session a
, v$transaction b
, v$Access c
WHERE
a.saddr = b.ses_addr
AND c.sid = a.sid
AND c.owner NOT IN ('SYS')
ORDER BY a.sid, c.type
/
Prompt +-------------------------------------------+
Prompt | Blocked objects from V$LOCK and SYS.OBJ$ |
Prompt +-------------------------------------------+
COLUMN BLOCKED_OBJ FORMAT a35 HEADING 'Blocked Object'
SELECT /*+ ORDERED */
l.sid
, l.lmode
, TRUNC(l.ctime/60) min_blocked
, u.name||'.'||o.NAME blocked_obj
FROM (SELECT *
FROM v$lock
WHERE type='TM'
AND sid IN (SELECT sid
FROM v$lock
WHERE block !=0 )) l
, sys.obj$ o
, sys.user$ u
WHERE o.obj# = l.ID1
AND o.OWNER# = u.user#
/
Prompt +-------------------------------+
Prompt | Blocked sessions from V$LOCK |
Prompt +-------------------------------+
SELECT /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
FROM (SELECT *
FROM v$lock
WHERE block != 0
AND type = 'TX') blocker
, v$lock blocked
WHERE blocked.type='TX'
AND blocked.block = 0
AND blocked.id1 = blocker.id1
/
CLEAR COLUMN
CLEAR BREAKS
SET LINES 80
SET TRIMS on
SET PAGES 9000
COLUMN sql_text FORMAT a70 word_wrapped
COLUMN sid FORMAT a10 NOPRINT new_value n_sid
COLUMN serial FORMAT a10 NOPRINT new_value n_serial
COLUMN username FORMAT a20 NOPRINT new_value n_username
COLUMN machine FORMAT a20 NOPRINT new_value n_machine
COLUMN osuser FORMAT a20 NOPRINT new_value n_osuser
COLUMN process FORMAT a20 NOPRINT new_value n_process
COLUMN action FORMAT a45 NOPRINT new_value n_action
Prompt +--------------------------------------------------------+
Prompt | Blockers session details from V$SESSION and V$SQLTEXT |
Prompt +--------------------------------------------------------+
BREAK ON sid ON serial ON username ON process ON machine ON actiON skip page
ttitle -
"Sid .......... : " n_sid -
skip 1 -
"Serial ....... : " n_serial -
skip 1 -
"Username ..... : " n_username -
skip 1 -
"Machine ...... : " n_machine -
skip 1 -
"OSuser ....... : " n_osuser -
skip 1 -
"Process ...... : " n_process -
skip 1 -
"Action ....... : " n_action -
SELECT /*+ ORDERED */
sid,serial# serial,username,machine,osuser,process,module||' '||action action,sql_text
FROM v$session ses, v$sqltext txt
WHERE txt.address(+) = ses.sql_address
AND txt.hash_value(+) = ses.sql_hash_value
AND ses.sid IN (SELECT sid
FROM v$lock
WHERE block != 0
AND type = 'TX')
ORDER BY ses.sid,txt.piece
/
출처 : http://www.tek-tips.com/viewthread.cfm?qid=1291771&page=9
출처 : http://npnglife.tistory.com/100
TeCumulus_v123.zip
