Scribd
Upload
56 views2 pages

Tableas Lock Processo Lok

This document contains several SQL queries: 1) The first query selects session information like session ID, serial number, blocking session, event, client info, username, OS user, machine, last call elapsed time, and seconds in wait from the gv$session view for active sessions that are not waiting and have a non-null username. 2) The second query selects session ID, username, OS user, machine, SQL hash value, percentage done, start time, elapsed seconds, estimated finish time, remaining time, and message from gv$session and gv$session_longops views for long-running SQL to show progress. 3) The third query selects machine, session ID, serial number,

Uploaded by

Thomas Flores
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
56 views2 pages

Tableas Lock Processo Lok

This document contains several SQL queries: 1) The first query selects session information like session ID, serial number, blocking session, event, client info, username, OS user, machine, last call elapsed time, and seconds in wait from the gv$session view for active sessions that are not waiting and have a non-null username. 2) The second query selects session ID, username, OS user, machine, SQL hash value, percentage done, start time, elapsed seconds, estimated finish time, remaining time, and message from gv$session and gv$session_longops views for long-running SQL to show progress. 3) The third query selects machine, session ID, serial number,

Uploaded by

Thomas Flores
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

select s.sid, s.serial#, s.blocking_session as holder, s.event, s.

client_info,
s.username, s.osuser, s.machine,s.last_call_et,s.seconds_in_wait
from gv$session s left outer join gv$sqlarea a on(s.sql_id = a.sql_id)
where s.status = 'ACTIVE'
and s.username is not null
and s.wait_class# != 6
order by last_call_et desc;

select s.sid
SId,
s.username
USERBANCO,
s.osuser ,
s.machine
MAQUINA,
s.SQL_HASH_VALUE,
TRUNC((sofar/totalwork)*100,2) || '%' "%DONE",
to_char(l.start_time,'DD/MM/YYYY hh24:mi:ss')
INICIO,
l.elapsed_seconds || ' segundos'
EXECUCAO,
to_char(sysdate + (l.TIME_REMAINING/(24*3600)),'DD/MM/YYYY hh24:mi:ss')
FINAL,
'faltam '||l.TIME_REMAINING || ' segundos'
RESTANTE,
l.message
MENSAGEM
from gv$session s, gv$session_longops l
where s.sid = l.sid
and s.status = 'ACTIVE'
and (sofar/totalwork) < 1
and totalwork > 0
--and s.username <> 'SYS'
order by sofar/totalwork desc;

SELECT
MACHINE, ses.sid, ses.serial#, ses.sql_id,
REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM gV$SESSION SES,
gV$SQLtext_with_newlines SQL
where SES.STATUS = 'ACTIVE'
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1,sql.piece;

select trunc(completion_time) rundate


,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024/1024)) "REDO PER DAY (GB)"
from v$archived_log
group by trunc(completion_time)
order by 1;
set long 54
set lines 157
set pages 45
col spid format 99999
col SQL_TEXT format A54
col username format A10
col CHD format 99
col SID_SERIAL format A12
COL RESRC FORMAT A12
Select A.SID||','||A.serial# SID_SERIAL,A.AUDSID,A.username,
A.SQL_ID,B.child_number CHD,B.ROWS_PROCESSED ROW_P,B.SQL_FULLTEXT
SQL_TEXT,last_call_et/60 lce,executions EXEC,RESOURCE_CONSUMER_GROUP RESRC
from gV$sql B, gv$session A
where B.sql_id=A.sql_id
and a.sql_child_number=b.child_number
and A.SID not in (select SID from v$mystat)
and upper(B.sql_text) not like 'BEGIN FND%'
and upper(B.sql_text) not like 'BEGIN XDP%'
and upper(B.sql_text) not like 'BEGIN XNP%'
and upper(B.sql_text) not like 'BEGIN WF_EVENT_OJMSTEXT_QH%'
and lower(B.sql_text) not like 'begin dbms_aqin.aq$_dequeu%'
and username <> 'SYS'
order by 8 desc
/

You might also like