set pages 200 lines 200
set feedback off
set timing on
select decode(status, 'COMPLETED', 0,1)
from v$RMAN_BACKUP_JOB_DETAILS
where session_key =(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('ARCHIVELOG'));
sexta-feira, 28 de março de 2014
Verifica Log de Backup RMAN
set feedback off
set timing on
select decode(status, 'COMPLETED', 0,1) as STATUS
from v$RMAN_BACKUP_JOB_DETAILS
where session_key = (select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR'));
set timing on
select decode(status, 'COMPLETED', 0,1) as STATUS
from v$RMAN_BACKUP_JOB_DETAILS
where session_key = (select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR'));
Verifica Archives Gerados por Dia
set lines 200 pages 200
set timing on;
select
trunc(first_time) day
,to_char(sum(blocks * block_size)/1024/1024,'999G999D99') MB
from gv\$archived_log
where first_time > trunc(sysdate) -30
and first_time < sysdate +1
group by trunc(first_time)
order by 1 desc;
set timing on;
select
trunc(first_time) day
,to_char(sum(blocks * block_size)/1024/1024,'999G999D99') MB
from gv\$archived_log
where first_time > trunc(sysdate) -30
and first_time < sysdate +1
group by trunc(first_time)
order by 1 desc;
Verifica Archivos Gerados no Ultimo Backup Full RMAN
set lines 200 pagesize 80
col TAG for a20
col DEVICE for a8
col CAMINHO for a65
col INICIO for a15
col FINAL for a17
col TAMANHO_TOTAL for a15
col TAMANHO for a10
set feedback off
set timing on
alter session set nls_date_format='dd/mm/yy hh24:mi';
select /*+ rule */ tag
,device_type as DEVICE
,handle as CAMINHO
,START_TIME as INICIO
,COMPLETION_TIME as FINAL
,SIZE_BYTES_DISPLAY as TAMANHO
from v$backup_piece_details
where session_recid = (select max(session_recid) from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR') and status = 'COMPLETED')
and START_TIME > sysdate -30
order by START_TIME;
select /*+ rule */ TO_CHAR(sum(bytes)/1024/1024,'9G999G999D9') as TAMANHO_TOTAL
from v$backup_piece_details
where session_recid = (select max(session_recid) from V\$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR') and status = 'COMPLETED')
and START_TIME > sysdate -30;
col TAG for a20
col DEVICE for a8
col CAMINHO for a65
col INICIO for a15
col FINAL for a17
col TAMANHO_TOTAL for a15
col TAMANHO for a10
set feedback off
set timing on
alter session set nls_date_format='dd/mm/yy hh24:mi';
select /*+ rule */ tag
,device_type as DEVICE
,handle as CAMINHO
,START_TIME as INICIO
,COMPLETION_TIME as FINAL
,SIZE_BYTES_DISPLAY as TAMANHO
from v$backup_piece_details
where session_recid = (select max(session_recid) from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR') and status = 'COMPLETED')
and START_TIME > sysdate -30
order by START_TIME;
select /*+ rule */ TO_CHAR(sum(bytes)/1024/1024,'9G999G999D9') as TAMANHO_TOTAL
from v$backup_piece_details
where session_recid = (select max(session_recid) from V\$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('DB FULL', 'DB INCR') and status = 'COMPLETED')
and START_TIME > sysdate -30;
Verifica Ultimos Backups de Archives Gerados
set pages 200 lines 200
set feedback off
col handle format a75
col tag format a25
col DEVICE_TYPE format a10
col TAMANHO for a10
col INICIO for a20
col FINAL for a20
set timing on
alter session set nls_date_format='dd/mm/yy hh24:mi';
select /*+ rule */ tag
,device_type
,handle
,START_TIME as INICIO
,COMPLETION_TIME as FINAL
,SIZE_BYTES_DISPLAY as TAMANHO
from v$backup_piece_details
where session_recid > (select max(session_recid) -5 from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('ARCHIVELOG') and status = 'COMPLETED')
and START_TIME > sysdate -30 order by START_TIME;
set feedback off
col handle format a75
col tag format a25
col DEVICE_TYPE format a10
col TAMANHO for a10
col INICIO for a20
col FINAL for a20
set timing on
alter session set nls_date_format='dd/mm/yy hh24:mi';
select /*+ rule */ tag
,device_type
,handle
,START_TIME as INICIO
,COMPLETION_TIME as FINAL
,SIZE_BYTES_DISPLAY as TAMANHO
from v$backup_piece_details
where session_recid > (select max(session_recid) -5 from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-10 and input_type in('ARCHIVELOG') and status = 'COMPLETED')
and START_TIME > sysdate -30 order by START_TIME;
Verifica Quantidade de Archives Gerados por Hora
set feedback off
set timing on
alter session set nls_date_format='dd/mm/yy';
set pagesize 80 lines 200
col 00 for a4
col 01 for a4
col 02 for a4
col 03 for a4
col 04 for a4
col 05 for a4
col 06 for a4
col 07 for a4
col 08 for a4
col 09 for a4
col 10 for a4
col 11 for a4
col 12 for a4
col 13 for a4
col 14 for a4
col 15 for a4
col 16 for a4
col 17 for a4
col 18 for a4
col 19 for a4
col 20 for a4
col 21 for a4
col 22 for a4
col 23 for a4
select trunc(first_time) day
,to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)), '999') "00"
,to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)), '999') "01"
,to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)), '999') "02"
,to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)), '999') "03"
,to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)), '999') "04"
,to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)), '999') "05"
,to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)), '999') "06"
,to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)), '999') "07"
,to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)), '999') "08"
,to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)), '999') "09"
,to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)), '999') "10"
,to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)), '999') "11"
,to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)), '999') "12"
,to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)), '999') "13"
,to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)), '999') "14"
,to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)), '999') "15"
,to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)), '999') "16"
,to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)), '999') "17"
,to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)), '999') "18"
,to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)), '999') "19"
,to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)), '999') "20"
,to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)), '999') "21"
,to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)), '999') "22"
,to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)), '999') "23"
from gv$log_history
where first_time > trunc(sysdate -5)
and first_time < sysdate + 1
group by trunc(first_time)
order by 1;
set timing on
alter session set nls_date_format='dd/mm/yy';
set pagesize 80 lines 200
col 00 for a4
col 01 for a4
col 02 for a4
col 03 for a4
col 04 for a4
col 05 for a4
col 06 for a4
col 07 for a4
col 08 for a4
col 09 for a4
col 10 for a4
col 11 for a4
col 12 for a4
col 13 for a4
col 14 for a4
col 15 for a4
col 16 for a4
col 17 for a4
col 18 for a4
col 19 for a4
col 20 for a4
col 21 for a4
col 22 for a4
col 23 for a4
select trunc(first_time) day
,to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)), '999') "00"
,to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)), '999') "01"
,to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)), '999') "02"
,to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)), '999') "03"
,to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)), '999') "04"
,to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)), '999') "05"
,to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)), '999') "06"
,to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)), '999') "07"
,to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)), '999') "08"
,to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)), '999') "09"
,to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)), '999') "10"
,to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)), '999') "11"
,to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)), '999') "12"
,to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)), '999') "13"
,to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)), '999') "14"
,to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)), '999') "15"
,to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)), '999') "16"
,to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)), '999') "17"
,to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)), '999') "18"
,to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)), '999') "19"
,to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)), '999') "20"
,to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)), '999') "21"
,to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)), '999') "22"
,to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)), '999') "23"
from gv$log_history
where first_time > trunc(sysdate -5)
and first_time < sysdate + 1
group by trunc(first_time)
order by 1;
Verifica Estatisticas de Backup RMAN
set lines 200 pagesize 200
set timing on
col STATUS format a10
col tempo format a10
col INPUT_TYPE for a15
col OPTIMIZED for a10
col tam_backup for a10
col inicio for a20
col final for a20
select *
from (select INPUT_TYPE
,STATUS
,to_char(START_TIME,'DD/MM/yy hh24:mi') as INICIO
,to_char(END_TIME,'DD/MM/yy hh24:mi') as FINAL
,TIME_TAKEN_DISPLAY tempo
,OPTIMIZED
,d.OUTPUT_BYTES_DISPLAY tam_backup
from V$RMAN_BACKUP_JOB_DETAILS d
where START_TIME > sysdate-30
order by session_key desc)
where rownum <=30;
set timing on
col STATUS format a10
col tempo format a10
col INPUT_TYPE for a15
col OPTIMIZED for a10
col tam_backup for a10
col inicio for a20
col final for a20
select *
from (select INPUT_TYPE
,STATUS
,to_char(START_TIME,'DD/MM/yy hh24:mi') as INICIO
,to_char(END_TIME,'DD/MM/yy hh24:mi') as FINAL
,TIME_TAKEN_DISPLAY tempo
,OPTIMIZED
,d.OUTPUT_BYTES_DISPLAY tam_backup
from V$RMAN_BACKUP_JOB_DETAILS d
where START_TIME > sysdate-30
order by session_key desc)
where rownum <=30;
VIEWS MATERIALIZADAS DESATUALIZADAS
set pages 200 lines 200;
set timing on;
select
owner
,type
,tablespace_name
,round(sum(mb)) as mb
from (select
owner
,'mview' as type
,tablespace_name
,round(sum(bytes) / 1024/1024) as mb
from dba_segments
where (owner,segment_name) in (select owner, mview_name from dba_mviews)
group by owner, segment_type, tablespace_name
union
select
owner
,'mview_log' as type
,tablespace_name
,round(sum(bytes) / 1024/1024) as mb
from dba_segments
where (owner,segment_name) in (select log_owner, log_table from dba_snapshot_logs)
group by owner, segment_type, tablespace_name
union
select
owner
,'mview_index' as type
,tablespace_name
,round(sum(bytes) / 1024/1024) as mb
from dba_segments
where (owner,segment_name) in (select owner, index_name from dba_indexes where (owner,table_name) in (select owner, mview_name from dba_mviews))
group by owner, segment_type, tablespace_name) t1
group by owner,type,tablespace_name;
Verifica Jobs
set lines 200 pages 200;
set timing on;
col JOB_NAME for a40;
SELECT JOB_NAME
,STATE
,ENABLED as ENABLED
FROM DBA_SCHEDULER_JOBS;
set timing on;
col JOB_NAME for a40;
SELECT JOB_NAME
,STATE
,ENABLED as ENABLED
FROM DBA_SCHEDULER_JOBS;
Verifica Tamanho do Banco
set lines 200 pages 200
set timing on
select to_char(sum(bytes) / 1024 / 1024,'9G999G999D9') "Tamanho MB"
from ( select sum(bytes) bytes
from
dba_data_files
union all
select
sum(bytes) bytes
from
dba_temp_files
union all
select
sum(bytes * members)
from
v\$log
union all
select
sum(block_size * file_size_blks)
from
v$controlfile
);
set timing on
select to_char(sum(bytes) / 1024 / 1024,'9G999G999D9') "Tamanho MB"
from ( select sum(bytes) bytes
from
dba_data_files
union all
select
sum(bytes) bytes
from
dba_temp_files
union all
select
sum(bytes * members)
from
v\$log
union all
select
sum(block_size * file_size_blks)
from
v$controlfile
);
Verifica Versao do Banco
set pages 200 lines 200
col INSTANCIA for a
col SERVIDOR for a12
col VERSAO for a12
col STATUS for a8
col active_state for a12
col STARTUP_TIME for a12
select
INSTANCE_NAME AS INSTANCIA
,HOST_NAME AS SERVIDOR
,VERSION AS VERSAO
,STATUS
,ACTIVE_STATE
,STARTUP_TIME
from v$instance;
col host_name for a40
col instance_name for a15
col version for a20
col startup_time for a25
col status for a15
set lines 200
set pagesize 80
set timing on
select
inst_id
,instance_name
,host_name
,version
,to_char(startup_time,'dd/mm/yy hh24:mi') as startup_time
,status
from
gv$instance
order by 1;
col INSTANCIA for a
col SERVIDOR for a12
col VERSAO for a12
col STATUS for a8
col active_state for a12
col STARTUP_TIME for a12
select
INSTANCE_NAME AS INSTANCIA
,HOST_NAME AS SERVIDOR
,VERSION AS VERSAO
,STATUS
,ACTIVE_STATE
,STARTUP_TIME
from v$instance;
col host_name for a40
col instance_name for a15
col version for a20
col startup_time for a25
col status for a15
set lines 200
set pagesize 80
set timing on
select
inst_id
,instance_name
,host_name
,version
,to_char(startup_time,'dd/mm/yy hh24:mi') as startup_time
,status
from
gv$instance
order by 1;
Verifica Blocking
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 : Blocking Locks |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (Summary) |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET serveroutput ON FORMAT WRAPPED
SET feedback OFF
DECLARE
CURSOR cur_BlockingLocks IS
SELECT
iw.instance_name AS waiting_instance
, sw.status AS waiting_status
, lw.sid AS waiting_sid
, sw.serial# AS waiting_serial_num
, sw.username AS waiting_oracle_username
, sw.osuser AS waiting_os_username
, sw.machine AS waiting_machine
, pw.spid AS waiting_spid
, SUBSTR(sw.terminal,0, 39) AS waiting_terminal
, SUBSTR(sw.program,0, 39) AS waiting_program
, ROUND(lw.ctime/60) AS waiting_lock_time_min
, DECODE ( lh.type
, 'CF', 'Control File'
, 'DX', 'Distributed Transaction'
, 'FS', 'File Set'
, 'IR', 'Instance Recovery'
, 'IS', 'Instance State'
, 'IV', 'Libcache Invalidation'
, 'LS', 'Log Start or Log Switch'
, 'MR', 'Media Recovery'
, 'RT', 'Redo Thread'
, 'RW', 'Row Wait'
, 'SQ', 'Sequence Number'
, 'ST', 'Diskspace Transaction'
, 'TE', 'Extend Table'
, 'TT', 'Temp Table'
, 'TX', 'Transaction'
, 'TM', 'DML'
, 'UL', 'PLSQL User_lock'
, 'UN', 'User Name'
, 'Nothing-'
) AS waiter_lock_type
, DECODE ( lw.request
, 0, 'None' /* Mon Lock equivalent */
, 1, 'NoLock' /* N */
, 2, 'Row-Share (SS)' /* L */
, 3, 'Row-Exclusive (SX)' /* R */
, 4, 'Share-Table' /* S */
, 5, 'Share-Row-Exclusive (SSX)' /* C */
, 6, 'Exclusive' /* X */
, '[Nothing]'
) AS waiter_mode_request
, ih.instance_name AS locking_instance
, sh.status AS locking_status
, lh.sid AS locking_sid
, sh.serial# AS locking_serial_num
, sh.username AS locking_oracle_username
, sh.osuser AS locking_os_username
, sh.machine AS locking_machine
, ph.spid AS locking_spid
, SUBSTR(sh.terminal,0, 39) AS locking_terminal
, SUBSTR(sh.program,0, 39) AS locking_program
, ROUND(lh.ctime/60) AS locking_lock_time_min
, aw.sql_text AS waiting_sql_text
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
, gv$session sw
, gv$session sh
, gv$process pw
, gv$process ph
, gv$sqlarea aw
WHERE
iw.inst_id = lw.inst_id
AND ih.inst_id = lh.inst_id
AND sw.inst_id = lw.inst_id
AND sh.inst_id = lh.inst_id
AND pw.inst_id = lw.inst_id
AND ph.inst_id = lh.inst_id
AND aw.inst_id = lw.inst_id
AND sw.sid = lw.sid
AND sh.sid = lh.sid
AND lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request = 0
AND lw.lmode = 0
AND (lh.id1, lh.id2) IN ( SELECT id1,id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1,id2
FROM gv$lock
WHERE lmode = 0
)
AND sw.paddr = pw.addr (+)
AND sh.paddr = ph.addr (+)
AND sw.sql_address = aw.address
ORDER BY
iw.instance_name
, lw.sid;
TYPE t_BlockingLockRecord IS RECORD (
WaitingInstanceName VARCHAR2(16)
, WaitingStatus VARCHAR2(8)
, WaitingSid NUMBER
, WaitingSerialNum NUMBER
, WaitingOracleUsername VARCHAR2(30)
, WaitingOSUsername VARCHAR2(30)
, WaitingMachine VARCHAR2(64)
, WaitingSpid VARCHAR2(12)
, WaitingTerminal VARCHAR2(30)
, WaitingProgram VARCHAR2(48)
, WaitingLockTimeMinute NUMBER
, WaiterLockType VARCHAR2(30)
, WaiterModeRequest VARCHAR2(30)
, LockingInstanceName VARCHAR2(16)
, LockingStatus VARCHAR2(8)
, LockingSid NUMBER
, LockingSerialNum NUMBER
, LockingOracleUsername VARCHAR2(30)
, LockingOSUsername VARCHAR2(30)
, LockingMachine VARCHAR2(64)
, LockingSpid VARCHAR2(12)
, LockingTerminal VARCHAR2(30)
, LockingProgram VARCHAR2(48)
, LockingLockTimeMinute NUMBER
, SQLText VARCHAR2(1000)
);
TYPE t_BlockingLockRecordTable IS TABLE OF t_BlockingLockRecord INDEX BY BINARY_INTEGER;
v_BlockingLockArray t_BlockingLockRecordTable;
v_BlockingLockRec cur_BlockingLocks%ROWTYPE;
v_NumBlockingLocksIncidents BINARY_INTEGER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN cur_BlockingLocks;
LOOP
FETCH cur_BlockingLocks INTO v_BlockingLockRec;
EXIT WHEN cur_BlockingLocks%NOTFOUND;
v_NumBlockingLocksIncidents := v_NumBlockingLocksIncidents + 1;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingInstanceName := v_BlockingLockRec.waiting_instance;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingStatus := v_BlockingLockRec.waiting_status;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSid := v_BlockingLockRec.waiting_sid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSerialNum := v_BlockingLockRec.waiting_serial_num;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOracleUsername := v_BlockingLockRec.waiting_oracle_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOSUsername := v_BlockingLockRec.waiting_os_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingMachine := v_BlockingLockRec.waiting_machine;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSpid := v_BlockingLockRec.waiting_spid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingTerminal := v_BlockingLockRec.waiting_terminal;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingProgram := v_BlockingLockRec.waiting_program;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingLockTimeMinute := v_BlockingLockRec.waiting_lock_time_min;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterLockType := v_BlockingLockRec.waiter_lock_type;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterModeRequest := v_BlockingLockRec.waiter_mode_request;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingInstanceName := v_BlockingLockRec.locking_instance;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingStatus := v_BlockingLockRec.locking_status;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSid := v_BlockingLockRec.locking_sid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSerialNum := v_BlockingLockRec.locking_serial_num;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOracleUsername := v_BlockingLockRec.locking_oracle_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOSUsername := v_BlockingLockRec.locking_os_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingMachine := v_BlockingLockRec.locking_machine;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSpid := v_BlockingLockRec.locking_spid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingTerminal := v_BlockingLockRec.locking_terminal;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingProgram := v_BlockingLockRec.locking_program;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingLockTimeMinute := v_BlockingLockRec.locking_lock_time_min;
v_BlockingLockArray(v_NumBlockingLocksIncidents).SQLText := v_BlockingLockRec.waiting_sql_text;
END LOOP;
CLOSE cur_BlockingLocks;
DBMS_OUTPUT.PUT_LINE('Number of blocking lock incidents: ' || v_BlockingLockArray.COUNT);
DBMS_OUTPUT.PUT(chr(10));
FOR RowIndex IN 1 .. v_BlockingLockArray.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('Incident ' || RowIndex);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(' WAITING BLOCKING');
DBMS_OUTPUT.PUT_LINE(' ---------------------------------------- ----------------------------------------');
DBMS_OUTPUT.PUT_LINE('Instance Name : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingInstanceName, 41) || v_BlockingLockArray(RowIndex).LockingInstanceName);
DBMS_OUTPUT.PUT_LINE('Oracle SID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSid, 41) || v_BlockingLockArray(RowIndex).LockingSid);
DBMS_OUTPUT.PUT_LINE('Serial# : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSerialNum, 41) || v_BlockingLockArray(RowIndex).LockingSerialNum);
DBMS_OUTPUT.PUT_LINE('Oracle User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOracleUsername, 41) || v_BlockingLockArray(RowIndex).LockingOracleUsername);
DBMS_OUTPUT.PUT_LINE('O/S User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOSUsername, 41) || v_BlockingLockArray(RowIndex).LockingOSUsername);
DBMS_OUTPUT.PUT_LINE('Machine : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingMachine, 41) || v_BlockingLockArray(RowIndex).LockingMachine);
DBMS_OUTPUT.PUT_LINE('O/S PID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSpid, 41) || v_BlockingLockArray(RowIndex).LockingSpid);
DBMS_OUTPUT.PUT_LINE('Terminal : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingTerminal, 41) || v_BlockingLockArray(RowIndex).LockingTerminal);
DBMS_OUTPUT.PUT_LINE('Lock Time : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingLockTimeMinute || ' minutes', 41) || v_BlockingLockArray(RowIndex).LockingLockTimeMinute ||' minutes');
DBMS_OUTPUT.PUT_LINE('Status : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingStatus, 41) || v_BlockingLockArray(RowIndex).LockingStatus);
DBMS_OUTPUT.PUT_LINE('Program : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingProgram, 41) || v_BlockingLockArray(RowIndex).LockingProgram);
DBMS_OUTPUT.PUT_LINE('Waiter Lock Type : ' || v_BlockingLockArray(RowIndex).WaiterLockType);
DBMS_OUTPUT.PUT_LINE('Waiter Mode Request : ' || v_BlockingLockArray(RowIndex).WaiterModeRequest);
DBMS_OUTPUT.PUT_LINE('Waiting SQL : ' || v_BlockingLockArray(RowIndex).SQLText);
DBMS_OUTPUT.PUT(chr(10));
END LOOP;
END;
/
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 256
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a9 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN sid_serial FORMAT a15 HEADING 'SID / Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Locking Oracle User'
COLUMN object_owner FORMAT a15 HEADING 'Object Owner'
COLUMN object_name FORMAT a25 HEADING 'Object Name'
COLUMN object_type FORMAT a15 HEADING 'Object Type'
COLUMN locked_mode HEADING 'Locked Mode'
CLEAR BREAKS
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | LOCKED OBJECTS |
PROMPT +------------------------------------------------------------------------+
SELECT
i.instance_name instance_name
, l.session_id || ' / ' || s.serial# sid_serial
, s.status session_status
, l.oracle_username locking_oracle_user
, o.owner object_owner
, o.object_name object_name
, o.object_type object_type
, DECODE ( l.locked_mode
, 0, 'None' /* Mon Lock equivalent */
, 1, 'NoLock' /* N */
, 2, 'Row-Share (SS)' /* L */
, 3, 'Row-Exclusive (SX)' /* R */
, 4, 'Share-Table' /* S */
, 5, 'Share-Row-Exclusive (SSX)' /* C */
, 6, 'Exclusive' /* X */
, '[Nothing]'
) locked_mode
FROM
dba_objects o
, gv$session s
, gv$locked_object l
, gv$instance i
WHERE
i.inst_id = l.inst_id
AND s.inst_id = l.inst_id
AND s.sid = l.session_id
AND o.object_id = l.object_id
ORDER BY
i.instance_name
, l.session_id;
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 : Blocking Locks |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (Summary) |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET serveroutput ON FORMAT WRAPPED
SET feedback OFF
DECLARE
CURSOR cur_BlockingLocks IS
SELECT
iw.instance_name AS waiting_instance
, sw.status AS waiting_status
, lw.sid AS waiting_sid
, sw.serial# AS waiting_serial_num
, sw.username AS waiting_oracle_username
, sw.osuser AS waiting_os_username
, sw.machine AS waiting_machine
, pw.spid AS waiting_spid
, SUBSTR(sw.terminal,0, 39) AS waiting_terminal
, SUBSTR(sw.program,0, 39) AS waiting_program
, ROUND(lw.ctime/60) AS waiting_lock_time_min
, DECODE ( lh.type
, 'CF', 'Control File'
, 'DX', 'Distributed Transaction'
, 'FS', 'File Set'
, 'IR', 'Instance Recovery'
, 'IS', 'Instance State'
, 'IV', 'Libcache Invalidation'
, 'LS', 'Log Start or Log Switch'
, 'MR', 'Media Recovery'
, 'RT', 'Redo Thread'
, 'RW', 'Row Wait'
, 'SQ', 'Sequence Number'
, 'ST', 'Diskspace Transaction'
, 'TE', 'Extend Table'
, 'TT', 'Temp Table'
, 'TX', 'Transaction'
, 'TM', 'DML'
, 'UL', 'PLSQL User_lock'
, 'UN', 'User Name'
, 'Nothing-'
) AS waiter_lock_type
, DECODE ( lw.request
, 0, 'None' /* Mon Lock equivalent */
, 1, 'NoLock' /* N */
, 2, 'Row-Share (SS)' /* L */
, 3, 'Row-Exclusive (SX)' /* R */
, 4, 'Share-Table' /* S */
, 5, 'Share-Row-Exclusive (SSX)' /* C */
, 6, 'Exclusive' /* X */
, '[Nothing]'
) AS waiter_mode_request
, ih.instance_name AS locking_instance
, sh.status AS locking_status
, lh.sid AS locking_sid
, sh.serial# AS locking_serial_num
, sh.username AS locking_oracle_username
, sh.osuser AS locking_os_username
, sh.machine AS locking_machine
, ph.spid AS locking_spid
, SUBSTR(sh.terminal,0, 39) AS locking_terminal
, SUBSTR(sh.program,0, 39) AS locking_program
, ROUND(lh.ctime/60) AS locking_lock_time_min
, aw.sql_text AS waiting_sql_text
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
, gv$session sw
, gv$session sh
, gv$process pw
, gv$process ph
, gv$sqlarea aw
WHERE
iw.inst_id = lw.inst_id
AND ih.inst_id = lh.inst_id
AND sw.inst_id = lw.inst_id
AND sh.inst_id = lh.inst_id
AND pw.inst_id = lw.inst_id
AND ph.inst_id = lh.inst_id
AND aw.inst_id = lw.inst_id
AND sw.sid = lw.sid
AND sh.sid = lh.sid
AND lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request = 0
AND lw.lmode = 0
AND (lh.id1, lh.id2) IN ( SELECT id1,id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1,id2
FROM gv$lock
WHERE lmode = 0
)
AND sw.paddr = pw.addr (+)
AND sh.paddr = ph.addr (+)
AND sw.sql_address = aw.address
ORDER BY
iw.instance_name
, lw.sid;
TYPE t_BlockingLockRecord IS RECORD (
WaitingInstanceName VARCHAR2(16)
, WaitingStatus VARCHAR2(8)
, WaitingSid NUMBER
, WaitingSerialNum NUMBER
, WaitingOracleUsername VARCHAR2(30)
, WaitingOSUsername VARCHAR2(30)
, WaitingMachine VARCHAR2(64)
, WaitingSpid VARCHAR2(12)
, WaitingTerminal VARCHAR2(30)
, WaitingProgram VARCHAR2(48)
, WaitingLockTimeMinute NUMBER
, WaiterLockType VARCHAR2(30)
, WaiterModeRequest VARCHAR2(30)
, LockingInstanceName VARCHAR2(16)
, LockingStatus VARCHAR2(8)
, LockingSid NUMBER
, LockingSerialNum NUMBER
, LockingOracleUsername VARCHAR2(30)
, LockingOSUsername VARCHAR2(30)
, LockingMachine VARCHAR2(64)
, LockingSpid VARCHAR2(12)
, LockingTerminal VARCHAR2(30)
, LockingProgram VARCHAR2(48)
, LockingLockTimeMinute NUMBER
, SQLText VARCHAR2(1000)
);
TYPE t_BlockingLockRecordTable IS TABLE OF t_BlockingLockRecord INDEX BY BINARY_INTEGER;
v_BlockingLockArray t_BlockingLockRecordTable;
v_BlockingLockRec cur_BlockingLocks%ROWTYPE;
v_NumBlockingLocksIncidents BINARY_INTEGER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN cur_BlockingLocks;
LOOP
FETCH cur_BlockingLocks INTO v_BlockingLockRec;
EXIT WHEN cur_BlockingLocks%NOTFOUND;
v_NumBlockingLocksIncidents := v_NumBlockingLocksIncidents + 1;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingInstanceName := v_BlockingLockRec.waiting_instance;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingStatus := v_BlockingLockRec.waiting_status;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSid := v_BlockingLockRec.waiting_sid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSerialNum := v_BlockingLockRec.waiting_serial_num;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOracleUsername := v_BlockingLockRec.waiting_oracle_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOSUsername := v_BlockingLockRec.waiting_os_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingMachine := v_BlockingLockRec.waiting_machine;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSpid := v_BlockingLockRec.waiting_spid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingTerminal := v_BlockingLockRec.waiting_terminal;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingProgram := v_BlockingLockRec.waiting_program;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingLockTimeMinute := v_BlockingLockRec.waiting_lock_time_min;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterLockType := v_BlockingLockRec.waiter_lock_type;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterModeRequest := v_BlockingLockRec.waiter_mode_request;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingInstanceName := v_BlockingLockRec.locking_instance;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingStatus := v_BlockingLockRec.locking_status;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSid := v_BlockingLockRec.locking_sid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSerialNum := v_BlockingLockRec.locking_serial_num;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOracleUsername := v_BlockingLockRec.locking_oracle_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOSUsername := v_BlockingLockRec.locking_os_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingMachine := v_BlockingLockRec.locking_machine;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSpid := v_BlockingLockRec.locking_spid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingTerminal := v_BlockingLockRec.locking_terminal;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingProgram := v_BlockingLockRec.locking_program;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingLockTimeMinute := v_BlockingLockRec.locking_lock_time_min;
v_BlockingLockArray(v_NumBlockingLocksIncidents).SQLText := v_BlockingLockRec.waiting_sql_text;
END LOOP;
CLOSE cur_BlockingLocks;
DBMS_OUTPUT.PUT_LINE('Number of blocking lock incidents: ' || v_BlockingLockArray.COUNT);
DBMS_OUTPUT.PUT(chr(10));
FOR RowIndex IN 1 .. v_BlockingLockArray.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('Incident ' || RowIndex);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(' WAITING BLOCKING');
DBMS_OUTPUT.PUT_LINE(' ---------------------------------------- ----------------------------------------');
DBMS_OUTPUT.PUT_LINE('Instance Name : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingInstanceName, 41) || v_BlockingLockArray(RowIndex).LockingInstanceName);
DBMS_OUTPUT.PUT_LINE('Oracle SID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSid, 41) || v_BlockingLockArray(RowIndex).LockingSid);
DBMS_OUTPUT.PUT_LINE('Serial# : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSerialNum, 41) || v_BlockingLockArray(RowIndex).LockingSerialNum);
DBMS_OUTPUT.PUT_LINE('Oracle User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOracleUsername, 41) || v_BlockingLockArray(RowIndex).LockingOracleUsername);
DBMS_OUTPUT.PUT_LINE('O/S User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOSUsername, 41) || v_BlockingLockArray(RowIndex).LockingOSUsername);
DBMS_OUTPUT.PUT_LINE('Machine : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingMachine, 41) || v_BlockingLockArray(RowIndex).LockingMachine);
DBMS_OUTPUT.PUT_LINE('O/S PID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSpid, 41) || v_BlockingLockArray(RowIndex).LockingSpid);
DBMS_OUTPUT.PUT_LINE('Terminal : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingTerminal, 41) || v_BlockingLockArray(RowIndex).LockingTerminal);
DBMS_OUTPUT.PUT_LINE('Lock Time : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingLockTimeMinute || ' minutes', 41) || v_BlockingLockArray(RowIndex).LockingLockTimeMinute ||' minutes');
DBMS_OUTPUT.PUT_LINE('Status : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingStatus, 41) || v_BlockingLockArray(RowIndex).LockingStatus);
DBMS_OUTPUT.PUT_LINE('Program : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingProgram, 41) || v_BlockingLockArray(RowIndex).LockingProgram);
DBMS_OUTPUT.PUT_LINE('Waiter Lock Type : ' || v_BlockingLockArray(RowIndex).WaiterLockType);
DBMS_OUTPUT.PUT_LINE('Waiter Mode Request : ' || v_BlockingLockArray(RowIndex).WaiterModeRequest);
DBMS_OUTPUT.PUT_LINE('Waiting SQL : ' || v_BlockingLockArray(RowIndex).SQLText);
DBMS_OUTPUT.PUT(chr(10));
END LOOP;
END;
/
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 256
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a9 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN sid_serial FORMAT a15 HEADING 'SID / Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Locking Oracle User'
COLUMN object_owner FORMAT a15 HEADING 'Object Owner'
COLUMN object_name FORMAT a25 HEADING 'Object Name'
COLUMN object_type FORMAT a15 HEADING 'Object Type'
COLUMN locked_mode HEADING 'Locked Mode'
CLEAR BREAKS
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | LOCKED OBJECTS |
PROMPT +------------------------------------------------------------------------+
SELECT
i.instance_name instance_name
, l.session_id || ' / ' || s.serial# sid_serial
, s.status session_status
, l.oracle_username locking_oracle_user
, o.owner object_owner
, o.object_name object_name
, o.object_type object_type
, DECODE ( l.locked_mode
, 0, 'None' /* Mon Lock equivalent */
, 1, 'NoLock' /* N */
, 2, 'Row-Share (SS)' /* L */
, 3, 'Row-Exclusive (SX)' /* R */
, 4, 'Share-Table' /* S */
, 5, 'Share-Row-Exclusive (SSX)' /* C */
, 6, 'Exclusive' /* X */
, '[Nothing]'
) locked_mode
FROM
dba_objects o
, gv$session s
, gv$locked_object l
, gv$instance i
WHERE
i.inst_id = l.inst_id
AND s.inst_id = l.inst_id
AND s.sid = l.session_id
AND o.object_id = l.object_id
ORDER BY
i.instance_name
, l.session_id;
Verifica Locks 4
Set pagesize 256
Set linesize 130
Set trimspool on
Set feedback off
prompt **********************************************************
prompt ***** Locks *****
prompt **********************************************************
set serveroutput on size 20000
set pagesize 110
declare
cursor c1 is
select o.OBJECT_ID,substr(o.OBJECT_NAME,1,25) object_name,s.machine,
o.OBJECT_TYPE,substr(o.owner,1,15) owner,
l.OS_USER_NAME,l.ORACLE_USERNAME,l.PROCESS,decode(l.locked_mode,1,'No Lock',2,'Row Share',3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) locked_mode, S.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID and s.sid=l.session_id
order by 2,6;
begin
dbms_output.put_line(rpad('-',130,'-'));
dbms_output.put_line( rpad('OBJECT_NAME',30)||
rpad('OBJECT_TYPE',12)||
rpad('LOGIN_Oracle',30)||
rpad('LOGIN_REDE',15)||
rpad('LOCKED_MODE',15)||
rpad('MAQUINA',15));
dbms_output.put_line(rpad('-',130,'-'));
for t1 in c1 loop
dbms_output.put_line(
rpad(t1.object_name,30)||
rpad(t1.object_type,12)||
rpad(t1.oracle_username|| '(' || t1.sid || ',' || t1.serial# || ')',30)||
rpad(t1.os_user_name,15)||
rpad(t1.locked_mode,15)||
rpad(t1.machine,15));
end loop;
dbms_output.put_line(rpad('-',130,'-'));
end;
/
Set feedback on
ALTER SYSTEM KILL SESSION '&SID,&SERIAL';
Set linesize 130
Set trimspool on
Set feedback off
prompt **********************************************************
prompt ***** Locks *****
prompt **********************************************************
set serveroutput on size 20000
set pagesize 110
declare
cursor c1 is
select o.OBJECT_ID,substr(o.OBJECT_NAME,1,25) object_name,s.machine,
o.OBJECT_TYPE,substr(o.owner,1,15) owner,
l.OS_USER_NAME,l.ORACLE_USERNAME,l.PROCESS,decode(l.locked_mode,1,'No Lock',2,'Row Share',3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) locked_mode, S.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID and s.sid=l.session_id
order by 2,6;
begin
dbms_output.put_line(rpad('-',130,'-'));
dbms_output.put_line( rpad('OBJECT_NAME',30)||
rpad('OBJECT_TYPE',12)||
rpad('LOGIN_Oracle',30)||
rpad('LOGIN_REDE',15)||
rpad('LOCKED_MODE',15)||
rpad('MAQUINA',15));
dbms_output.put_line(rpad('-',130,'-'));
for t1 in c1 loop
dbms_output.put_line(
rpad(t1.object_name,30)||
rpad(t1.object_type,12)||
rpad(t1.oracle_username|| '(' || t1.sid || ',' || t1.serial# || ')',30)||
rpad(t1.os_user_name,15)||
rpad(t1.locked_mode,15)||
rpad(t1.machine,15));
end loop;
dbms_output.put_line(rpad('-',130,'-'));
end;
/
Set feedback on
ALTER SYSTEM KILL SESSION '&SID,&SERIAL';
Verifica Locks 3
SELECT s.sid,s.serial#,s.username, L.* FROM (
SELECT SID,TYPE ||
DECODE(TYPE,
'BL',': Buffer hash table',
'CF',': Control File Transaction',
'CI',': Cross Instance Call',
'CS',': Control File Schema',
'CU',': Bind Enqueue',
'DF',': Data File',
'DL',': Direct-loader index-creation',
'DM',': Mount/startup db primary/secondary instance',
'DR',': Distributed Recovery Process',
'DX',': Distributed Transaction Entry',
'FI',': SGA Open-File Information',
'FS',': File Set',
'IN',': Instance Number',
'IR',': Instance Recovery Serialization',
'IS',': Instance State',
'IV',': Library Cache InValidation',
'JQ',': Job Queue',
'KK',': Redo Log "Kick"',
'LS',': Log Start/Log Switch',
'MB',': Master Buffer hash table',
'MM',': Mount Definition',
'MR',': Media Recovery',
'PF',': Password File',
'PI',': Parallel Slaves',
'PR',': Process Startup',
'PS',': Parallel Slaves Synchronization',
'RE',': USE_ROW_ENQUEUE Enforcement',
'RT',': Redo Thread',
'RW',': Row Wait',
'SC',': System Commit Number',
'SH',': System Commit Number HWM',
'SM',': SMON',
'SQ',': Sequence Number',
'SR',': Synchronized Replication',
'SS',': Sort Segment',
'ST',': Space Transaction',
'SV',': Sequence Number Value',
'TA',': Transaction Recovery',
'TD',': DDL enqueue',
'TE',': Extend-segment enqueue',
'TM',': DML enqueue',
'TS',': Temporary Segment',
'TT',': Temporary Table',
'TX',': Transaction',
'UL',': User-defined Lock',
'UN',': User Name',
'US',': Undo Segment Serialization',
'WL',': Being-written redo log instance',
'WS',': Write-atomic-log-switch global enqueue',
'XA',': Instance Attribute',
'XI',': Instance Registration',
decode(substr(type,1,1),
'L', ': Library Cache ('||substr(type,2,1)||')',
'N', ': Library Cache Pin ('||substr(type,2,1)||')',
'Q', ': Row Cache ('||substr(type,2,1)||')',
': ????')) type,
DECODE(LMODE,
0, '--Waiting--',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>') mode_held,
DECODE(REQUEST,
0, '',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>') mode_requested,
COUNT(*) cnt
FROM GV$LOCK
WHERE TYPE NOT IN ('MR','RT')
GROUP BY SID,TYPE ||
DECODE(TYPE,
'BL',': Buffer hash table',
'CF',': Control File Transaction',
'CI',': Cross Instance Call',
'CS',': Control File Schema',
'CU',': Bind Enqueue',
'DF',': Data File',
'DL',': Direct-loader index-creation',
'DM',': Mount/startup db primary/secondary instance',
'DR',': Distributed Recovery Process',
'DX',': Distributed Transaction Entry',
'FI',': SGA Open-File Information',
'FS',': File Set',
'IN',': Instance Number',
'IR',': Instance Recovery Serialization',
'IS',': Instance State',
'IV',': Library Cache InValidation',
'JQ',': Job Queue',
'KK',': Redo Log "Kick"',
'LS',': Log Start/Log Switch',
'MB',': Master Buffer hash table',
'MM',': Mount Definition',
'MR',': Media Recovery',
'PF',': Password File',
'PI',': Parallel Slaves',
'PR',': Process Startup',
'PS',': Parallel Slaves Synchronization',
'RE',': USE_ROW_ENQUEUE Enforcement',
'RT',': Redo Thread',
'RW',': Row Wait',
'SC',': System Commit Number',
'SH',': System Commit Number HWM',
'SM',': SMON',
'SQ',': Sequence Number',
'SR',': Synchronized Replication',
'SS',': Sort Segment',
'ST',': Space Transaction',
'SV',': Sequence Number Value',
'TA',': Transaction Recovery',
'TD',': DDL enqueue',
'TE',': Extend-segment enqueue',
'TM',': DML enqueue',
'TS',': Temporary Segment',
'TT',': Temporary Table',
'TX',': Transaction',
'UL',': User-defined Lock',
'UN',': User Name',
'US',': Undo Segment Serialization',
'WL',': Being-written redo log instance',
'WS',': Write-atomic-log-switch global enqueue',
'XA',': Instance Attribute',
'XI',': Instance Registration',
decode(substr(type,1,1),
'L', ': Library Cache ('||substr(type,2,1)||')',
'N', ': Library Cache Pin ('||substr(type,2,1)||')',
'Q', ': Row Cache ('||substr(type,2,1)||')',
': ????')),
DECODE(LMODE,
0, '--Waiting--',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>'),
DECODE(REQUEST,
0, '',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>')
) L, V$SESSION S
WHERE S.SID=L.SID
/
SELECT SID,TYPE ||
DECODE(TYPE,
'BL',': Buffer hash table',
'CF',': Control File Transaction',
'CI',': Cross Instance Call',
'CS',': Control File Schema',
'CU',': Bind Enqueue',
'DF',': Data File',
'DL',': Direct-loader index-creation',
'DM',': Mount/startup db primary/secondary instance',
'DR',': Distributed Recovery Process',
'DX',': Distributed Transaction Entry',
'FI',': SGA Open-File Information',
'FS',': File Set',
'IN',': Instance Number',
'IR',': Instance Recovery Serialization',
'IS',': Instance State',
'IV',': Library Cache InValidation',
'JQ',': Job Queue',
'KK',': Redo Log "Kick"',
'LS',': Log Start/Log Switch',
'MB',': Master Buffer hash table',
'MM',': Mount Definition',
'MR',': Media Recovery',
'PF',': Password File',
'PI',': Parallel Slaves',
'PR',': Process Startup',
'PS',': Parallel Slaves Synchronization',
'RE',': USE_ROW_ENQUEUE Enforcement',
'RT',': Redo Thread',
'RW',': Row Wait',
'SC',': System Commit Number',
'SH',': System Commit Number HWM',
'SM',': SMON',
'SQ',': Sequence Number',
'SR',': Synchronized Replication',
'SS',': Sort Segment',
'ST',': Space Transaction',
'SV',': Sequence Number Value',
'TA',': Transaction Recovery',
'TD',': DDL enqueue',
'TE',': Extend-segment enqueue',
'TM',': DML enqueue',
'TS',': Temporary Segment',
'TT',': Temporary Table',
'TX',': Transaction',
'UL',': User-defined Lock',
'UN',': User Name',
'US',': Undo Segment Serialization',
'WL',': Being-written redo log instance',
'WS',': Write-atomic-log-switch global enqueue',
'XA',': Instance Attribute',
'XI',': Instance Registration',
decode(substr(type,1,1),
'L', ': Library Cache ('||substr(type,2,1)||')',
'N', ': Library Cache Pin ('||substr(type,2,1)||')',
'Q', ': Row Cache ('||substr(type,2,1)||')',
': ????')) type,
DECODE(LMODE,
0, '--Waiting--',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>') mode_held,
DECODE(REQUEST,
0, '',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>') mode_requested,
COUNT(*) cnt
FROM GV$LOCK
WHERE TYPE NOT IN ('MR','RT')
GROUP BY SID,TYPE ||
DECODE(TYPE,
'BL',': Buffer hash table',
'CF',': Control File Transaction',
'CI',': Cross Instance Call',
'CS',': Control File Schema',
'CU',': Bind Enqueue',
'DF',': Data File',
'DL',': Direct-loader index-creation',
'DM',': Mount/startup db primary/secondary instance',
'DR',': Distributed Recovery Process',
'DX',': Distributed Transaction Entry',
'FI',': SGA Open-File Information',
'FS',': File Set',
'IN',': Instance Number',
'IR',': Instance Recovery Serialization',
'IS',': Instance State',
'IV',': Library Cache InValidation',
'JQ',': Job Queue',
'KK',': Redo Log "Kick"',
'LS',': Log Start/Log Switch',
'MB',': Master Buffer hash table',
'MM',': Mount Definition',
'MR',': Media Recovery',
'PF',': Password File',
'PI',': Parallel Slaves',
'PR',': Process Startup',
'PS',': Parallel Slaves Synchronization',
'RE',': USE_ROW_ENQUEUE Enforcement',
'RT',': Redo Thread',
'RW',': Row Wait',
'SC',': System Commit Number',
'SH',': System Commit Number HWM',
'SM',': SMON',
'SQ',': Sequence Number',
'SR',': Synchronized Replication',
'SS',': Sort Segment',
'ST',': Space Transaction',
'SV',': Sequence Number Value',
'TA',': Transaction Recovery',
'TD',': DDL enqueue',
'TE',': Extend-segment enqueue',
'TM',': DML enqueue',
'TS',': Temporary Segment',
'TT',': Temporary Table',
'TX',': Transaction',
'UL',': User-defined Lock',
'UN',': User Name',
'US',': Undo Segment Serialization',
'WL',': Being-written redo log instance',
'WS',': Write-atomic-log-switch global enqueue',
'XA',': Instance Attribute',
'XI',': Instance Registration',
decode(substr(type,1,1),
'L', ': Library Cache ('||substr(type,2,1)||')',
'N', ': Library Cache Pin ('||substr(type,2,1)||')',
'Q', ': Row Cache ('||substr(type,2,1)||')',
': ????')),
DECODE(LMODE,
0, '--Waiting--',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>'),
DECODE(REQUEST,
0, '',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>')
) L, V$SESSION S
WHERE S.SID=L.SID
/
Verifica Locks UTL
rem
rem $Header: utllockt.sql,v 1.2 1995/05/05 10:16:23 pgreenwa Exp $ locktree.sql
rem
Rem Copyright (c) 1989 by Oracle Corporation
Rem NAME
REM UTLLOCKT.SQL
Rem FUNCTION - Print out the lock wait-for graph in tree structured fashion.
Rem This is useful for diagnosing systems that are hung on locks.
Rem NOTES
Rem MODIFIED
Rem bnnguyen 01/21/03 - bug2166717
Rem pgreenwa 04/27/95 - fix column definitions for LOCK_HOLDERS
Rem pgreenwa 04/26/95 - modify lock_holders query to use new dba_locks f
Rem glumpkin 10/20/92 - Renamed from LOCKTREE.SQL
Rem jloaiza 05/24/91 - update for v7
Rem rlim 04/29/91 - change char to varchar2
Rem Loaiza 11/01/89 - Creation
Rem
/* Print out the lock wait-for graph in a tree structured fashion.
*
* This script prints the sessions in the system that are waiting for
* locks, and the locks that they are waiting for. The printout is tree
* structured. If a sessionid is printed immediately below and to the right
* of another session, then it is waiting for that session. The session ids
* printed at the left hand side of the page are the ones that everyone is
* waiting for.
*
* For example, in the following printout session 9 is waiting for
* session 8, 7 is waiting for 9, and 10 is waiting for 9.
*
* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
* ----------------- ---- ----------------- ----------------- -------- --------
* 8 NONE None None 0 0
* 9 TX Share (S) Exclusive (X) 65547 16
* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
*
* The lock information to the right of the session id describes the lock
* that the session is waiting for (not the lock it is holding).
*
* Note that this is a script and not a set of view definitions because
* connect-by is used in the implementation and therefore a temporary table
* is created and dropped since you cannot do a join in a connect-by.
*
* This script has two small disadvantages. One, a table is created when
* this script is run. To create a table a number of locks must be
* acquired. This might cause the session running the script to get caught
* in the lock problem it is trying to diagnose. Two, if a session waits on
* a lock held by more than one session (share lock) then the wait-for graph
* is no longer a tree and the conenct-by will show the session (and any
* sessions waiting on it) several times.
*/
/* Select all sids waiting for a lock, the lock they are waiting on, and the
* sid of the session that holds the lock.
* UNION
* The sids of all session holding locks that someone is waiting on that
* are not themselves waiting for locks. These are included so that the roots
* of the wait for graph (the sessions holding things up) will be displayed.
*/
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, 'None', null, null, null, null
from lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
rem $Header: utllockt.sql,v 1.2 1995/05/05 10:16:23 pgreenwa Exp $ locktree.sql
rem
Rem Copyright (c) 1989 by Oracle Corporation
Rem NAME
REM UTLLOCKT.SQL
Rem FUNCTION - Print out the lock wait-for graph in tree structured fashion.
Rem This is useful for diagnosing systems that are hung on locks.
Rem NOTES
Rem MODIFIED
Rem bnnguyen 01/21/03 - bug2166717
Rem pgreenwa 04/27/95 - fix column definitions for LOCK_HOLDERS
Rem pgreenwa 04/26/95 - modify lock_holders query to use new dba_locks f
Rem glumpkin 10/20/92 - Renamed from LOCKTREE.SQL
Rem jloaiza 05/24/91 - update for v7
Rem rlim 04/29/91 - change char to varchar2
Rem Loaiza 11/01/89 - Creation
Rem
/* Print out the lock wait-for graph in a tree structured fashion.
*
* This script prints the sessions in the system that are waiting for
* locks, and the locks that they are waiting for. The printout is tree
* structured. If a sessionid is printed immediately below and to the right
* of another session, then it is waiting for that session. The session ids
* printed at the left hand side of the page are the ones that everyone is
* waiting for.
*
* For example, in the following printout session 9 is waiting for
* session 8, 7 is waiting for 9, and 10 is waiting for 9.
*
* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
* ----------------- ---- ----------------- ----------------- -------- --------
* 8 NONE None None 0 0
* 9 TX Share (S) Exclusive (X) 65547 16
* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
*
* The lock information to the right of the session id describes the lock
* that the session is waiting for (not the lock it is holding).
*
* Note that this is a script and not a set of view definitions because
* connect-by is used in the implementation and therefore a temporary table
* is created and dropped since you cannot do a join in a connect-by.
*
* This script has two small disadvantages. One, a table is created when
* this script is run. To create a table a number of locks must be
* acquired. This might cause the session running the script to get caught
* in the lock problem it is trying to diagnose. Two, if a session waits on
* a lock held by more than one session (share lock) then the wait-for graph
* is no longer a tree and the conenct-by will show the session (and any
* sessions waiting on it) several times.
*/
/* Select all sids waiting for a lock, the lock they are waiting on, and the
* sid of the session that holds the lock.
* UNION
* The sids of all session holding locks that someone is waiting on that
* are not themselves waiting for locks. These are included so that the roots
* of the wait for graph (the sessions holding things up) will be displayed.
*/
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, 'None', null, null, null, null
from lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
Verifica Locks em Tabelas
-- SESSAO 1
-- Lista as Sessão com locks ativos
set serveroutput on size 20000
set lines 200
set pagesize 110
declare
cursor c1 is
select o.OBJECT_ID,substr(o.OBJECT_NAME,1,25) object_name,s.machine,
o.OBJECT_TYPE,substr(o.owner,1,15) owner,
l.OS_USER_NAME,l.ORACLE_USERNAME,l.PROCESS,decode(l.locked_mode,1,'No Lock',2,'Row Share',3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) locked_mode, S.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID and s.sid=l.session_id
order by 2,6;
begin
dbms_output.put_line(rpad('-',180,'-'));
dbms_output.put_line( rpad('OBJECT_NAME',30)||
rpad('OBJECT_TYPE',12)||
rpad('LOGIN_Oracle',30)||
rpad('LOGIN_REDE',30)||
rpad('LOCKED_MODE',15)||
rpad('MAQUINA',25));
dbms_output.put_line(rpad('-',180,'-'));
for t1 in c1 loop
dbms_output.put_line(
rpad(t1.object_name,30)||
rpad(t1.object_type,12)||
rpad(t1.oracle_username|| '(' || t1.sid || ',' || t1.serial# || ')',30)||
rpad(t1.os_user_name,30)||
rpad(t1.locked_mode,15)||
rpad(t1.machine,25));
end loop;
dbms_output.put_line(rpad('-',180,'-'));
end;
/
-- rowid Aluno
select rowid from sa_academico_gnx.&tabela
where
ALU2ANOCAD='&ALU2ANOCAD'
and ALU2SEMCAD='&ALU2SEMCAD'
and ALU2SEQCAD='&ALU2SEQCAD'
/
-- Anotar SID
select distinct sid from v$mystat
/
233 AAAOZcAAbAAAEnfAAD
-- Irá ficar em LOCK
SELECT 1 FROM SA_ACADEMICO_GNX.&TABELA WHERE ROWID='&ROW_ID'
FOR UPDATE
/
-- Abrir nova seção
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, 'None', null, null, null, null
from lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
-- Fazer rollback na sessão 1.
-- Lista as Sessão com locks ativos
set serveroutput on size 20000
set lines 200
set pagesize 110
declare
cursor c1 is
select o.OBJECT_ID,substr(o.OBJECT_NAME,1,25) object_name,s.machine,
o.OBJECT_TYPE,substr(o.owner,1,15) owner,
l.OS_USER_NAME,l.ORACLE_USERNAME,l.PROCESS,decode(l.locked_mode,1,'No Lock',2,'Row Share',3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) locked_mode, S.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID and s.sid=l.session_id
order by 2,6;
begin
dbms_output.put_line(rpad('-',180,'-'));
dbms_output.put_line( rpad('OBJECT_NAME',30)||
rpad('OBJECT_TYPE',12)||
rpad('LOGIN_Oracle',30)||
rpad('LOGIN_REDE',30)||
rpad('LOCKED_MODE',15)||
rpad('MAQUINA',25));
dbms_output.put_line(rpad('-',180,'-'));
for t1 in c1 loop
dbms_output.put_line(
rpad(t1.object_name,30)||
rpad(t1.object_type,12)||
rpad(t1.oracle_username|| '(' || t1.sid || ',' || t1.serial# || ')',30)||
rpad(t1.os_user_name,30)||
rpad(t1.locked_mode,15)||
rpad(t1.machine,25));
end loop;
dbms_output.put_line(rpad('-',180,'-'));
end;
/
-- rowid Aluno
select rowid from sa_academico_gnx.&tabela
where
ALU2ANOCAD='&ALU2ANOCAD'
and ALU2SEMCAD='&ALU2SEMCAD'
and ALU2SEQCAD='&ALU2SEQCAD'
/
-- Anotar SID
select distinct sid from v$mystat
/
233 AAAOZcAAbAAAEnfAAD
-- Irá ficar em LOCK
SELECT 1 FROM SA_ACADEMICO_GNX.&TABELA WHERE ROWID='&ROW_ID'
FOR UPDATE
/
-- Abrir nova seção
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, 'None', null, null, null, null
from lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
-- Fazer rollback na sessão 1.
Verifica Locks Registrados
-- Lista as Sessão com locks ativos
set serveroutput on size 20000
set pagesize 110
declare
cursor c1 is
select o.OBJECT_ID,substr(o.OBJECT_NAME,1,25) object_name,s.machine,
o.OBJECT_TYPE,substr(o.owner,1,15) owner,
l.OS_USER_NAME,l.ORACLE_USERNAME,l.PROCESS,decode(l.locked_mode,1,'No Lock',2,'Row Share',3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) locked_mode, S.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID and s.sid=l.session_id
order by 2,6;
begin
dbms_output.put_line(rpad('-',130,'-'));
dbms_output.put_line( rpad('OBJECT_NAME',30)||
rpad('OBJECT_TYPE',12)||
rpad('LOGIN_Oracle',20)||
rpad('LOGIN_REDE',15)||
rpad('LOCKED_MODE',15)||
rpad('MAQUINA',15));
dbms_output.put_line(rpad('-',130,'-'));
for t1 in c1 loop
dbms_output.put_line(
rpad(t1.object_name,30)||
rpad(t1.object_type,12)||
rpad(t1.oracle_username|| '(' || t1.sid || ',' || t1.serial# || ')',20)||
rpad(t1.os_user_name,15)||
rpad(t1.locked_mode,15)||
rpad(t1.machine,15));
end loop;
dbms_output.put_line(rpad('-',130,'-'));
end;
/
-- Filtra informações da sessão selecionada
select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session where sid =&sessao
/
Exemplo:
SQL> select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
2 from v$session where sid =11;
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---- ------------- -------------- --------------- -------------
11 27139 5 895 1
-- Mostra o registro que esta locado.
DECLARE
SELECT DEPTNO FROM scott.dept
WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,&Row_wait_obj,&Sid,&wait_block,&row_wait)
BEGIN
END
/
SQL> select object_type,owner,object_name from dba_objects where object_id=27139
2 /
OBJECT_TYPE OWNER OBJECT_NAME
------------------ --------- -----------------------------------------------------------------------
TABLE INFBMJLP DEPT
SQL> SELECT DEPTNO FROM infbmjlp.EMP
2 WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1);
SELECT DEPTNO FROM infbmjlp.EMP
*
ERRO na linha 1:
ORA-01410: invalid ROWID
SQL> SELECT DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAGoDAALAAAAN/AAB
SQL> select DEPTNO FROM infbmjlp.EMP
2 WHERE rowid in (SELECT DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1) from dual);
não há linhas selecionadas
######################
SQL> select * from v$lock
2 where id1=31712
3 /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --------- -- --------- --------- --------- --------- --------- ---------
16D85D30 16D85D44 28 TM 31712 0 3 0 2386 0
16D85CAC 16D85CC0 46 TM 31712 0 3 0 2396 0
16D85A9C 16D85AB0 47 TM 31712 0 3 0 12 0
SQL> select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
2 from v$session WHERE ROW_WAIT_OBJ#=31712;
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
--------- ------------- -------------- --------------- -------------
28 31712 6 14 1
47 31712 6 14 1
######################
-- Mostra sessão em espera de liberação de locks
select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session WHERE ROW_WAIT_OBJ# IN (
select object_id
from dba_objects
where object_name = '&Obj_name')
/
-- Mostra as sessões que estão locando um registro --
Entrada: OWNER,ID_OBJET0,ROWID
Saida: Usuario/Terminal
-- Retorno o rowid da Chave de pesquisa
Entrada:OWNER,OBJETO,CAMPO(S),
Saida: ROWID
select DEPTNO FROM infbmjlp.EMP
WHERE rowid in (SELECT DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1) from dual);
create or replace function F_ROW_ID(OWNER In ALL_OBJECTS.OWNER%TYPE,
OBJETO ALL_OBJECTS.OBJECT_NAME%TYPE,
CHAVE VARCHAR2) return varchar2 is
Result varchar2(100);
Row_id varchar2(18);
Object_id all_objects.object_id%type;
begin
execute immediate 'Select rowid from '||OWNER||'.'||OBJETO||' where '
||CHAVE into Row_Id;
execute immediate 'Select object_id from all_objects'
||' where owner='||OWNER||' and OBJECT_NAME='||OBJETO into OBJECT_Id;
return(Result+OBJECT_ID);
end F_ROW_ID;
--
Verifica objetos com Locks
-- Verifica qual objeto está aguardando a liberação do Lock
select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype and w.kgllkhdl
= h.kgllkhdl and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
- Mostra objeto que está em Lock
select distinct to_name from v$object_dependency where to_address = '&Adress';
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = &sid_WAITING_SESSION);
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = sid_HOLDING_SESSION);
set term on;
set lines 150;
set linesize 255;
column sid_ser format a12 heading 'Session,|Serial#';
column username format a30 heading 'OS User/|DB User';
column spid format a7 heading 'OS|Process';
column owner_object format a35 heading 'Owner.Object';
column locked_mode format a13 heading 'Locked|Mode';
column status format a8 heading 'Status';
column logon format a18 heading 'Date|Logon';
select
substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) Owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) Locked_mode,
substr(s.status,1,8) Status,
to_char(s.LOGON_TIME,'dd/mm/yyyy hh24:mi') logon
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
/
#####################################
# Para ver o sql de um lock #
#####################################
set pagesize 60
set linesize 132
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address=s.sql_address
and t.hash_value=s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
/
##############################################
# Para ver quem esta aguardando um lock #
##############################################
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request, m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
spool off
clear breaks
select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype and w.kgllkhdl
= h.kgllkhdl and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
- Mostra objeto que está em Lock
select distinct to_name from v$object_dependency where to_address = '&Adress';
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = &sid_WAITING_SESSION);
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = sid_HOLDING_SESSION);
set term on;
set lines 150;
set linesize 255;
column sid_ser format a12 heading 'Session,|Serial#';
column username format a30 heading 'OS User/|DB User';
column spid format a7 heading 'OS|Process';
column owner_object format a35 heading 'Owner.Object';
column locked_mode format a13 heading 'Locked|Mode';
column status format a8 heading 'Status';
column logon format a18 heading 'Date|Logon';
select
substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) Owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) Locked_mode,
substr(s.status,1,8) Status,
to_char(s.LOGON_TIME,'dd/mm/yyyy hh24:mi') logon
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
/
#####################################
# Para ver o sql de um lock #
#####################################
set pagesize 60
set linesize 132
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address=s.sql_address
and t.hash_value=s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
/
##############################################
# Para ver quem esta aguardando um lock #
##############################################
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request, m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
spool off
clear breaks
Assinar:
Postagens (Atom)