PROCESOS EN EJECUCION
Desbloquear Program
SQL> select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', s.program from gv$session s where username = 'SYS';
SQL>
SELECT sess.sid, sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
FROM v$session sess, v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
AND sess.status = 'ACTIVE';
SELECT sess.sid, sess.process, sess.status, sess.username, sess.schemaname,SESS.WAIT_CLASS,sess.SQL_EXEC_START, SYSDATE, sql.sql_text, sql.ACTION, sess.MACHINE, SESS.CLIENT_INFO,SESS.PROGRAM,'ALTER SYSTEM kill SESSION '''||sess.SID||','||sess.SERIAL#||''' IMMEDIATE;'
FROM v$session sess, v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
AND sess.status = 'ACTIVE'
AND (sess.username like 'NOM_OWNER' OR sess.username like 'NOM_OWNER2')
AND sql.sql_text like 'SELECT%'
ORDER BY sess.SQL_EXEC_START ASC;
Bloqueos DDL dba_ddl_locks
SELECT
d.NAME, d.OWNER, d.TYPE, s.USERNAME, s.STATUS STATUS_SESSION, s.SCHEMANAME,Q.SQL_TEXT,
s.OSUSER USUARIO, s.MACHINE EQUIPO, s.PROGRAM, s.MODULE, s.LOGON_TIME INICIO_SESSION, s. ACTION, 'ALTER SYSTEM KILL SESSION '||chr(39)||d.SESSION_ID||','||s.SERIAL#||chr(39)||' IMMEDIATE;' AS KILL_PROCCESS
FROM sys.dba_ddl_locks d, v$session s, V$SQL q
WHERE
D.SESSION_ID=S.SID and
s.SQL_ID=q.SQL_ID and
d.OWNER NOT IN ('SYS', 'XDB') AND s.USERNAME = 'NOM_OWNER'
ORDER BY 5, 2;
Usuarios conectados
select count(STATUS), USERNAME from v$session s where STATUS='ACTIVE' group by USERNAME;
select count(STATUS), USERNAME from v$session s where STATUS='INACTIVE' group by USERNAME;
Ver SQL y tablas mas solicitadas
select 'select ''' || table_name || ''' as TABLA, ''' || sysdate ||
''' as FECHA_ACTUAL, ''' || last_analyzed ||
''' as ULTIMO_ANALISIS, count(*) as RECUENTO,' || num_rows ||
' as RECUENTO_ANALISIS , to_date(''' || sysdate ||
''', ''DD/MM/YYYY'') - to_date(''' || last_analyzed ||
''',''DD/MM/YYYY'') as DIAS_DESDE_ANALISIS , count(*) - ' || num_rows ||
' as DIFERENCIA_RECUENTO, (count(*) - ' ||
num_rows || ')/(to_date(''' || sysdate ||
''', ''DD/MM/YYYY'') - to_date(''' || last_analyzed ||
''',''DD/MM/YYYY'')) as INCREMENTO_DIARIO from ' || owner || '.' ||
table_name || ' union '
from dba_Tables
where owner = 'OWNER';
Encontrar trozos de Código en todo el source
select OWNER, NAME, TYPE, LINE, TEXT
from all_source
where --owner = 'SYS' and
type in ('PACKAGE', 'PACKAGE BODY')
and upper(text) like upper('%select * from tabla %');
Tablas mas utilizadas
SELECT ROWNUM AS RANK, Seg_Lio.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'LIO' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'logical reads'
ORDER BY St.VALUE DESC) Seg_Lio
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_r.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO Reads' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads'
ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO Writes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical writes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO READS Direct' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads direct'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'DB Block changes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'db block changes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20
###########################################################################
SELECT ROWNUM AS Rank, Seg_Lio.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'logical reads'
ORDER BY St.VALUE DESC) Seg_Lio
WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_r.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Reads' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads'
ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_w.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Writes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical writes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE ROWNUM <= 10;
Consumo en disco duro
SELECT *
FROM
(SELECT
parsing_schema_name
,direct_writes
,SUBSTR(sql_text,1,75)
,disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE rownum < 20;
Consumo en disco duro
SELECT *
FROM
(SELECT
s.statistic_name
,s.owner
,s.object_type
,s.object_name
,s.value
FROM v$segment_statistics s
WHERE s.statistic_name IN
('physical reads', 'physical writes', 'logical reads',
'physical reads direct', 'physical writes direct')
ORDER BY s.value DESC)
WHERE rownum < 20;
SQL monitoreo
SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
Q.SQL_TEXT,
Q.ACTION,
S.SQL_EXEC_START,
S.PREV_EXEC_START,
S.LOGON_TIME,
Q.LAST_LOAD_TIME,
Q.FIRST_LOAD_TIME,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER,
'ALTER SYSTEM kill SESSION '''||S.SID||','||S.SERIAL#||''' IMMEDIATE;',
S.SID,
S.SERIAL#
FROM v$lock l,dba_objects O,v$session S, V$SQL Q
WHERE
l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND l.TYPE in ('TM','TX')
AND s.SQL_ID=Q.SQL_ID;