Sess current user transactions
Aller à la navigation
Aller à la recherche
| Fiche express | |
|---|---|
| Type | Script SQL*Plus de diagnostic (Sessions) |
| Voir aussi | Sess uncommited transactions · Sess users |
Sess current user transactions combine deux rapports : les verrous d'objets posés par des transactions en cours (verrouillage de table) et le détail des transactions utilisateur actives (segment d'annulation utilisé, volume d'I/O logique/physique, temps écoulé depuis le début de la transaction).
Script
SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : User Transactions |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET LINESIZE 256
SET PAGESIZE 50000
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT +----------------------------------------------------+
PROMPT | Table Locking Information |
PROMPT +----------------------------------------------------+
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'
COLUMN logon_time FORMAT a18 HEADING 'Login Time'
COLUMN owner FORMAT a20 HEADING 'Owner'
COLUMN object_type FORMAT a11 HEADING 'Object Type'
COLUMN object_name FORMAT a25 HEADING 'Object Name'
COLUMN locked_mode FORMAT a11 HEADING 'Locked Mode'
SELECT
a.session_id sid
, c.serial# serial_id
, a.oracle_username oracle_username
, TO_CHAR(c.logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
, b.owner owner
, b.object_type object_type
, b.object_name object_name
, DECODE(a.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X',
4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') locked_mode
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
/
PROMPT
PROMPT +----------------------------------------------------+
PROMPT | User Transactions Information |
PROMPT +----------------------------------------------------+
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'
COLUMN os_pid FORMAT a8 HEADING 'O/S PID'
COLUMN trnx_start_time FORMAT a18 HEADING 'Trnx Start Time'
COLUMN elapsed_time FORMAT 999999999.99 HEADING 'Elapsed(mins)'
COLUMN undo_name FORMAT a10 HEADING 'Undo Name' TRUNC
COLUMN number_of_undo_records FORMAT 999,999,999,999 HEADING '# Undo Records'
COLUMN used_undo_size FORMAT 999,999,999 HEADING 'Used Undo (MB)'
COLUMN logical_io_size FORMAT 999,999,999,999 HEADING 'Logical I/O (MB)'
COLUMN physical_io_size FORMAT 999,999,999,999 HEADING 'Physical I/O (MB)'
COLUMN session_program FORMAT a26 HEADING 'Session Program' TRUNC
SELECT
s.sid sid
, s.status session_status
, s.username oracle_username
, p.spid os_pid
, TO_CHAR(TO_DATE(b.start_time, 'mm/dd/yy hh24:mi:ss'), 'mm/dd/yy hh24:mi:ss') trnx_start_time
, ROUND(60 * 24 * (SYSDATE - TO_DATE(b.start_time, 'mm/dd/yy hh24:mi:ss')), 2) elapsed_time
, c.segment_name undo_name
, b.used_urec number_of_undo_records
, (b.used_ublk * d.value) / 1024 / 1024 used_undo_size
, (b.log_io * d.value) / 1024 / 1024 logical_io_size
, (b.phy_io * d.value) / 1024 / 1024 physical_io_size
, s.program session_program
FROM
v$session s
, v$transaction b
, dba_rollback_segs c
, v$parameter d
, v$process p
WHERE
b.ses_addr = s.saddr
AND b.xidusn = c.segment_id
AND d.name = 'db_block_size'
AND p.addr = s.paddr
ORDER BY 1
/
Voir aussi
- Sess uncommited transactions — filtre uniquement les transactions non validées
- Sess users — vue d'ensemble de toutes les sessions connectées
- Rollback users — équivalent historique en gestion manuelle de l'annulation