script：dba常用管理脚本收集(六)
loop
v_role := y.role;
v_tab := y.table_name;
v_tab_priv := y.PRIVILEGE ;
--dbms_output.put_line(v_grantee||'|'||v_role||'|'||v_tab||'|'||v_tab_priv );
insert into oasis.privs values (v_grantee,v_role,v_tab,v_tab_priv);
end loop;
end loop;
commit;
end;
/
spool off;
exit;
-------------------------------------------------------------------------------------------------------------------------------------------
proc.sql
create or replace procedure db_rajesh.table_x_case_sms_proc (p_x_date date ) as
del_row_count number(15):=0;
v_cnt number:=0;
v_x_date date:=p_x_date;
begin
select cnt into v_cnt
from db_rajesh.sms_temp_raj
where x_date=v_x_date;
for x in 1..10
loop
delete from sa.table_x_case_sms where X_SMS_DATE <= v_x_date and rownum <5000;
del_row_count:=nvl(del_row_count,0)+sql%rowcount;
update db_rajesh.sms_temp_raj set COMP_CNT=del_row_count where X_DATE=v_x_date;
commit;
if del_row_count>=v_cnt then
exit;
end if;
end loop;
commit;
end;
/
----------------------------------------------------------------------------------------------------------------------------------------------
library_pin.sql
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,h1.username,
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
/
SELECT /*+ rule */ 'alter system kill session ' ||chr(39)||sid||','||SERIAL#||chr(39)||' immediate ;'
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl in (select P1RAW from gv$session_wait where state='WAITING'
and event like 'library cache lock' or event like 'library cache pin' )
/
--------------------------------------------------------------------------------------------------------------------------------------------
fts.sql
set linesize 132
break on hash_value skip 1 dup
col child_number format 9999 heading 'CHILD'
col operation format a55
col cost format 99999
col kbytes format 999999
col object format a25
select hash_value,
child_number,
lpad(' ',2*depth)||operation||' '||options||decode(id, 0, substr(optimizer,1,
6)||' Cost='||to_char(cost)) operation,
object_name object,
cost,
cardinality,
round(bytes / 1024) kbytes
from v$sql_plan
where hash_value in (select a.sql_hash_value
from v$session a, v$session_wait b
where a.sid = b.sid
and b.event = 'db file scattered read')
order by hash_value, child_number, id;