Recently I came across an issue where there was a certain text being displayed in the application which was incorrect. This data was stored in the database, however we were unable to trace out which table/column had that entry. How I wish that I could just grep the whole oracle database for that string. Googled up everywhere for some ready made code to do that... But couldn't find any...Finally had to come up with my own pl/sql code...Here it goes..
set serveroutput on size 1000000
declare
TYPE QueryCurType is REF CURSOR;
query1 QueryCurType ;
cursor c1 is select owner,table_name from dba_tables where owner not in ('SYS','SYSTEM') and table_name not like '%$%';
cursor c2(t1 varchar2) is select column_name from dba_tab_columns where table_name=t1 and DATA_TYPE in ('NVARCHAR2','VARCHAR2','CHAR');
temp_var varchar2(3000);
query varchar2(3000);
begin
for tab1 in c1 loop
for col in c2(tab1.table_name) loop
query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ''%Ciudad%definida%''';
--dbms_output.put_line('executing..'||query);
open query1 for query;
loop
fetch query1 into temp_var;
if concat('a',temp_var) != 'a' then
dbms_output.put_line('Found String: "'||temp_var||'"# Column:'||col.column_name||'# Table:'||tab1.table_name);
end if;
exit when query1%NOTFOUND;
end loop;
end loop;
end loop;
end;
/
The text in red is what we are searching for. Replace it with the text you would like to search.
Showing posts with label Scripts. Show all posts
Showing posts with label Scripts. Show all posts
Friday, November 13, 2009
Sunday, November 8, 2009
Find Details of a session using the SPID - unix.sql
set echo off
set serveroutput on size 50000
set verify off
set feedback off
accept uxproc prompt 'Enter Unix process id: '
accept inst_id prompt 'Enter instance id: '
DECLARE
v_sid number;
vs_cnt number;
s sys.gv_$session%ROWTYPE;
p sys.gv_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc') and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc') and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id for the instance number '||'&inst_id');
dbms_output.put_line('=====================================================================');
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.gv_$lock l, sys.obj$ o
where sid = s.sid
and inst_id='&inst_id'
and l.id1 = o.obj#(+) )
loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id for the instance number '||'&inst_id');
dbms_output.put_line('Please scroll up to see details of all the sessions.');
dbms_output.put_line('=====================================================================');
close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&uxproc for the instance number '||'&inst_id'||' !!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef uxproc
set heading on
set verify on
set feedback on
set echo on
set serveroutput on size 50000
set verify off
set feedback off
accept uxproc prompt 'Enter Unix process id: '
accept inst_id prompt 'Enter instance id: '
DECLARE
v_sid number;
vs_cnt number;
s sys.gv_$session%ROWTYPE;
p sys.gv_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc') and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc') and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id for the instance number '||'&inst_id');
dbms_output.put_line('=====================================================================');
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.gv_$lock l, sys.obj$ o
where sid = s.sid
and inst_id='&inst_id'
and l.id1 = o.obj#(+) )
loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id for the instance number '||'&inst_id');
dbms_output.put_line('Please scroll up to see details of all the sessions.');
dbms_output.put_line('=====================================================================');
close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&uxproc for the instance number '||'&inst_id'||' !!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef uxproc
set heading on
set verify on
set feedback on
set echo on
Check Wait events
set pages 1000
set lines 120
col inst format 9999
col sid format 9999
col event format a29 trunc
col program format a20 trunc
col module format a20
col username format A11
col secs format 99999
select w.inst_id inst, w.sid, w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait Secs
from gv$session_wait w, gv$session s
where w.inst_id = s.inst_id and
w.sid=s.sid and w.state='WAITING' and
w.event not in ('pmon timer',
'smon timer',
'rdbms ipc message',
'pipe get',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'SQL*Net more data from client',
'wakeup time manager',
'slave wait',
'SQL*Net more data to client') and w.event not like '%slave wait'
group by w.inst_id, w.sid,w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait
order by 1,3;
set lines 120
col inst format 9999
col sid format 9999
col event format a29 trunc
col program format a20 trunc
col module format a20
col username format A11
col secs format 99999
select w.inst_id inst, w.sid, w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait Secs
from gv$session_wait w, gv$session s
where w.inst_id = s.inst_id and
w.sid=s.sid and w.state='WAITING' and
w.event not in ('pmon timer',
'smon timer',
'rdbms ipc message',
'pipe get',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'SQL*Net more data from client',
'wakeup time manager',
'slave wait',
'SQL*Net more data to client') and w.event not like '%slave wait'
group by w.inst_id, w.sid,w.event,s.module,s.username,w.p1,w.p2,w.p3,w.seconds_in_wait
order by 1,3;
Subscribe to:
Posts (Atom)