Processos – Acompanhar andamento

[Sousa04Participante]

Utilizo esse script para ver o status atual da Query. Muito bom!!

SET PAGESIZE 100 WRAP ON
SPOOL sessinfo.txt

PROMPT ======================================================================
PROMPT
PROMPT Session information for Session ID &1
SET FEED OFF HEA OFF
SELECT INITCAP(TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’)) ||’ ‘|| name
FROM v$database
/
SET FEED ON HEA ON
PROMPT
PROMPT ======================================================================
PROMPT ======================================================================
PROMPT v$session – General info
PROMPT ======================================================================
col username for a12 HEA “Username”
SELECT
SUBSTR(TO_CHAR(s.sid) ||’, ‘|| TO_CHAR(s.serial#), 1, 10) “Sid/Serial#”
, s.osuser “Osuser”
, s.username
, SUBSTR(TO_CHAR(logon_time, ‘HH24:MI:SS’), 1, 8) “Logon”
, status “Status”
, p.spid “OS process”
, s.program “Program”
, s.module “Module”
, s.action “Action”
from v$session s
, v$process p
where type = ‘USER’
and s.paddr = p.addr
AND s.sid = &1
/

PROMPT ======================================================================
PROMPT v$sql – Current SQL statement this session executes
PROMPT ======================================================================
col sql_text for a70 hea “Current SQL”
select q.sql_text
from v$session s
, v$sql q
WHERE s.sql_address = q.address
and s.sql_hash_value + DECODE
(SIGN(s.sql_hash_value)
, -1, POWER( 2, 32)
, 0
) = q.hash_value
AND s.sid = &1
/

PROMPT ======================================================================
PROMPT v$transaction – Current transaction of this session
PROMPT ======================================================================
col undo_mb for 999.9 hea “Undo Mb”
col undo_blks for 99999 hea “Undo blocks”
select r.name “Rollback segment”
, substr( t.start_time, 10, 5) “Start”
, t.used_ublk * p.value / (1024*1024) undo_mb
, t.used_ublk undo_blks
from v$transaction t
, v$rollname r
, v$session s
, v$parameter p
where t.addr = s.taddr
and t.xidusn = r.usn
and p.name = ‘db_block_size’
AND s.sid = &1
/

PROMPT ======================================================================
PROMPT v$access – Objects accessed by this session
PROMPT ======================================================================
SELECT substr(a.owner,1,30) “Owner”
, substr(a.object,1,30) “Object Being Accessed”
from v$access a
where a.sid = &1
/

PROMPT ======================================================================
PROMPT v$lock – Locks held by this session
PROMPT ======================================================================
column LTYPE format a20 heading “Type”
column LMODE format a13 heading “Mode”
column LWHAT format a34 heading “Object”
column LTIME format 99999 heading “Time”
select decode
(LCK.type
, ‘MR’, ‘Media Recovery’
, ‘RT’, ‘Redo Thread’
, ‘UN’, ‘User Name’
, ‘TX’, ‘Transaction’
, ‘TM’, ‘DML’
, ‘UL’, ‘PL/SQL User Lock’
, ‘DX’, ‘Distributes Xaction’
, ‘CF’, ‘Control File’
, ‘IS’, ‘Instance State’
, ‘FS’, ‘File Set’
, ‘IR’, ‘Instance recovery’
, ‘ST’, ‘Disk Space Transaction’
, ‘TS’, ‘Temp Segment’
, ‘IV’, ‘Library Cache Invalidation’
, ‘LS’, ‘Log Start or Switch’
, ‘RW’, ‘Row Wait’
, ‘SQ’, ‘Sequence Number’
, ‘TE’, ‘Extend table’
, ‘TT’, ‘Temp Table’
, ‘Unknown’
) LTYPE
, decode
(LCK.lmode
, 2, ‘Row-S (SS)’
, 3, ‘Row-X (SX)’
, 4, ‘Share’
, 5, ‘S/Row-X (SSX)’
, 6, ‘Exclusive’
) LMODE
, DO.owner || ‘.’ || DO.object_name LWHAT
, LCK.ctime LTIME
from v$lock LCK
, v$locked_object LO
, sys.dba_objects DO
where LCK.lmode in (2, 3, 4, 5, 6)
and LCK.sid = LO.session_id
and LO.object_id = DO.object_id
and LCK.sid = &1
order by 1
, 2
, 3
/

PROMPT ======================================================================
PROMPT v$session – Object this session is waiting for
PROMPT ======================================================================
COL owner FOR A15 HEA “Object owner”
COL object_name FOR A25 HEA “Object name”
COL session_id HEA “Lock SID”
COL oracle_username FOR A15 HEA “Lock owner”
BREAK ON owner ON object_name
SELECT o.owner, o.object_name, l.session_id, l.oracle_username
from v$session s
, dba_objects o
, v$locked_object l
WHERE s.sid = &1
AND s.row_wait_obj# != -1
AND s.row_wait_obj# = o.object_id
AND l.object_id = o.object_id
AND l.session_id != &1
/
CL BREAKS
CL COL

PROMPT ======================================================================
PROMPT v$session_wait – Wait statistics for this session
PROMPT ======================================================================
col event for a60 hea “Event”
col wait_time for 999999 hea “Wait time”
SELECT e.event
, e.wait_time
FROM v$session_wait e
WHERE e.sid = &1
/

PROMPT ======================================================================
PROMPT v$sesstat – System statistics of this session
PROMPT ======================================================================
SELECT
n.statistic# “Statistic#”
, SUBSTR(n.name,1,50) “Name”
, s.value “Value”
FROM v$statname n
, v$sesstat s
WHERE s.statistic# = n.statistic#
AND s.sid = &1
AND s.value != 0
/

SPO OFF
CL COL
prompt dados salvos em sessinfo.txt
undefine 1

Agora para verificar quanto tempo falta para o processo terminar ou quanto já foi processado, Não faço tbm a mínima idéia. A propósito já me questionaram várias vezes sobre isso. O Vieri deve saber o bicho é cabeção!!! rsrs