dba 常用管理脚本收集
dba 常用管理脚本收集Space.sql
set time on
set lines 500
set pages 100
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999 Heading "Size (Mb)"
col free_ts_size form 99999999 Heading "Free (Mb)"
col used_ts_size form 99999999 Heading "Used (Mb)"
col used_pct form 99999 Heading "% Used"
col free_pct form 99999 Heading "% Free"
col warning form a10 Heading "Message"
break on report
compute sum label total of tot_ts_size on report
compute sum label total of used_ts_size on report
compute sum label total of free_ts_size on report
(select df.tablespace_name tspace
, round(sum(fs.bytes_free + fs.bytes_used) / 1024 / 1024, 2) tot_ts_size
, round(sum(fs.Bytes_used) / 1024 / 1024, 2) used_ts_size
, round(sum(fs.bytes_free) / 1024 / 1024, 2) free_ts_size
, round(sum(fs.Bytes_used ) * 100 / sum((fs.bytes_free + fs.bytes_used))) used_pct
, round(sum(fs.Bytes_free ) * 100 / sum((fs.bytes_free + fs.bytes_used))) free_pct
, decode(sign(sum(round(((fs.bytes_free + fs.bytes_used)-fs.bytes_free)*100/(fs.bytes_free + fs.bytes_used))) - 80), 1, '
!ALERT', '') warning
from SYS.V_$TEMP_SPACE_HEADER fs
, dba_temp_files df
where fs.tablespace_name(+) = df.tablespace_name
and fs.file_id(+) = df.file_id
group by df.tablespace_name
union
SELECT df.tablespace_name tspace
, df.bytes/(1024*1024) tot_ts_size
, round((df.bytes-sum(fs.bytes))/(1024*1024)) used_ts_size
, sum(fs.bytes)/(1024*1024) free_ts_size
, round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct
, round(sum(fs.bytes)*100/df.bytes) free_pct
, decode(sign(round((df.bytes-sum(fs.bytes))*100/df.bytes) - 80), 1, '!ALERT', '') warning
FROM dba_free_space fs
, (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name
) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes)
union
(select tablespace_name tspace,
1,1,0 free_ts_size,100 used_pct,0 free_pct,'!' warning from dba_data_files
group by tablespace_name
minus
select tablespace_name tspace,1,1,0 free_ts_size,100 used_pct,0 free_pct,'!' warning
from dba_free_space
group by tablespace_name)
order by 4
;
---------------------------------------------------------------------------------------------------------------------------------------------
Lock.sql
set lines 150
set pages 2000
col OBJECT_NAME format a30
col OBJECT_TYPE format a10
col LOCKED_MODE format 99
col ORACLE_USERNAME format a20
col OS_USER_NAME format a20
col PROCESS format a20
SELECT /*+ rule */ DECODE(request, 0,'HOLDER','WAITER'), sid , lmode, TYPE, ctime FROM v$LOCK
WHERE (id1, id2, TYPE ) IN (SELECT id1, id2, TYPE FROM v$LOCK WHERE request>0)
ORDER BY id1, request ;
--------------------------------------------------------------------------------------------------------------------------------------------------
lo.sql
col USERNAME for a15
col SQL_TEXT for a40
set line 130
set pages 400
select sesion.sid,sesion.serial#,
sesion.username,-- optimizer_mode,
hash_value, address, osuser,--cpu_time, elapsed_time,osuser,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
and sesion.sid =&sid
/
--------------------------------------------------------------------------------------------------------------------------------------------------
Sid.sql
select spid,sid,a.serial#,b.program,osuser,machine,process from v$session a,v$process b
where a.paddr=b.addr and b.spid in (&spid)
/
---------------------------------------------------------------------------------------------------------------------------------------------
Spid.sql
select spid,sid,a.serial#,b.program,osuser,machine,process,status from v$session a,v$process b
where a.paddr=b.addr and a.sid in (&sid)
/
----------------------------------------------------------------------------------------------------------------------------------------
SW.sql
col event for a34
set pages 100
set lines 130
select sid,event,p1,p2,p3 from v$session_wait where state='WAITING' and event not like '%messag%' order by event
/
select event,count(*) from v$session_wait group by event
/
----------------------------------------------------------------------------------------------------------------------------------------------
lops.sql
set line 150;
col target for a35
col EST_COMPLETION_TIME for a20
col SOFAR for a14
col sid_serial for a10
set pages 1000
SELECT /*+ rule */ a.sid||','||a.serial# "sid_serial", b.status, a.target || a.opname target , a.TOTALWORK, a.SOFAR SOFAR, a.TIME_REMAINING "TIME_REMAIN",
to_char(start_time+(sysdate-start_time) /(a.sofar/a.totalwork),'dd-mon-yy:hh:mi:ss') Est_completion_time,
round((a.sofar/a.totalwork)*100,3) pct_complete,
ELAPSED_SECONDS "ELAPS_SECS" FROM V$SESSION_LONGOPS a, V$SESSION b
where a.time_remaining > 0
and a.sid=b.sid and b.status='ACTIVE'
order by a.time_remaining desc
/
-----------------------------------------------------------------------------------------------------------------------------------------------
rollback_info.sql
SELECT * FROM V$FAST_START_TRANSACTIONS
/
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
--------------------------------------------------------------------------------------------------------------------------------------------------------------
top_sqls
SET LINESIZE 500
SET PAGESIZE 100
col sid_serial for a10
col sql_text for a30
col osuser for a10
SELECT *
FROM (SELECT /*+ rule */ sid||','||serial# sid_serial,Substr(a.sql_text,1,550) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) read_pr_exe,
a.buffer_gets buff_g,
a.disk_reads disk_r,
a.executions exec,
a.sorts,
a.address,osuser
FROM v$sqlarea a,v$session b
where a.address = b.sql_address and b.status = 'ACTIVE'
ORDER BY 3 DESC)
WHERE rownum <= &1
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
time_remaining.sql
SELECT SID,
DECODE (totalwork,
0, 0,
ROUND (100 * sofar / totalwork, 2)
) "Percent",
MESSAGE "Message", start_time, elapsed_seconds, time_remaining/60 "Time Remaining-Mins"
FROM v$session_longops WHERE time_remaining > 0 and sid=&sid
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
top_sessions.sql
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
undo_size.sql
SELECT SUM(a.bytes)/1024/1024/1024 "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
/
------------------------------------------------------------------------------------------------------------------------------------------------------
undo_used.sql
set pages 10000 lines 200
col username heading "User"
col name format a22 wrapped heading "Undo Segment Name"
col xidusn heading "Undo|Seg #"
col xidslot heading "Undo|Slot #"
col xidsqn heading "Undo|Seq #"
col ubafil heading "File #"
col ubablk heading "Block #"
col start_time format a20 word_wrapped heading "Started"
col status format a8 heading "Status"
col blk format 999,999,999 heading "KBytes"
col used_urec heading "Rows"
select /*+ rule */ start_time, username, r.name,
ubafil, ubablk, t.status, (used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r, v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name='db_block_size'
order by 1;
--------------------------------------------------------------------------------------------------------------------------------------------------------
temp_ts_space.sql
col sid_serial for a20
col username for a12
set pages 500
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total
/
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY module
/
---------------------------------------------------------------------------------------------------------------------------------------------------------
sync.sql
set time on
set timing on
set echo on
set feedback on
set linesize 1000
SELECT s.sid, s.serial#, s.username,
s.program,i.block_changes FROM v$session s,
v$sess_io i WHERE s.sid = i.sid and s.status='ACTIVE' and i.block_changes>10000
ORDER BY 5,1, 2, 3, 4;
----------------------------------------------------------------------------------------------------------------------------------------------------------
cpu_cost.sql
SELECT /*+ Rule */ 'SID : '||sid||chr(10)||
'Serial# '||serial#||chr(10)||
'Username : '||username||chr(10)||
'Logon Time : '||logon_time||chr(10)||
'Last Called (in Secs) :'||last_call_et||chr(10)||
'Status : '||status||chr(10)||
'SQL Address : '||address||chr(10)||
'HASH Value : '||hash_value||chr(10)||
'Buffer Gets : '||buffer_gets||chr(10)||
'Executions : '||executions||chr(10)||
'Buffer Gets / Execution :'||buffer_gets/executions||chr(10)||
'Text of SQL : '||sql_text
from ( select sid, serial#, username, logon_time, last_call_et, address, hash_value, buffer_gets, executions,
buffer_gets/executions,sql_text, status
FROM v$sqlarea, v$session
WHERE buffer_gets > 50000 and executions>0
and sql_address = address
and sql_hash_value = hash_value
order by 8
)
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------
db_growth.sql
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN month FORMAT a7 HEADING 'Month'
COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (Bytes)'
BREAK ON report
COMPUTE SUM OF growth ON report
SELECT
TO_CHAR(creation_time, 'RRRR-MM') month
, SUM(bytes)/1024/1024/1024 growth
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
-------------------------------------------------------------------------------------------------------------------------------------------------------
db_size.sql
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.poo / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.poo / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as poo
from dba_free_space) free
group by free.poo
/
-------------------------------------------------------------------------------------------------------------------------------------------------------
compile.sql
set echo on
set time on
set timing on
spool compile_procedure.sql
Select 'Alter Procedure '||owner||'.'||object_name ||' compile ;' from dba_objects where object_type='PROCEDURE'
and owner in ('SA') and status='INVALID';
Select 'Alter PACKAGE '||owner||'.'||object_name ||' compile ;' from dba_objects where object_type='PACKAGE' and owner
in ('SA') and status='INVALID';
Select 'Alter TRIGGER '||owner||'.'||object_name ||' compile ;' from dba_objects where object_type='TRIGGER' and owner
in ('SA') and status='INVALID';
spool off
------------------------------------------------------------------------------------------------------------------------------------------------------
inactive.sql
select count(*) from v$session where status='INACTIVE' and last_call_et >3600 ;
--------------------------------------------------------------------------------------------------------------------------------------------------
mv_refresh.sql
set time on
set timing on
set echo on
set feedback on
spool mv_refresh.log
alter session set db_file_multiblock_read_count=128;
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','C');
commit;
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
spool off
exit
-------------------------------------------------------------------------------------------------------------------------------------------------------------
pin_objects.sql
Exec Sys.Dbms_Shared_Pool.Keep('owner.object_name','q');-- sequeence
Exec Sys.Dbms_Shared_Pool.keep('owner.object_name','P'); - Procedure
------------------------------------------------------------------------------------------------------------------------------------------------------------
privs.sql--------------> To find the User privileges details by using this sql
set time on;
set timing on;
set echo on;
spool priv.log
declare
cursor c1 is
select * from (
select /*+ Rule */ GRANTEE,GRANTED_ROLE priv from dba_role_privs
union
select /*+ Rule */ GRANTEE,PRIVILEGE priv from dba_sys_privs )
where grantee not in(
'SYS','OUTLN','WMSYS','DBSNMP','SYSTEM','PERFSTAT',
'OEM_MONITOR', 'JAVADEBUGPRIV','AQ_USER_ROLE',
'LOGSTDBY_ADMINISTRATOR','PUBLIC','AQ_ADMINISTRATOR_ROLE','CONNECT',
'DBA','IBSORA','ITBOSS','OEM_MONITOR','ORDPLUGINS','JAVADEBUGPRIV',
'ORDSYS','RECOVERY_CATALOG_OWNER','RESOURCE','oasis',
'XDK','MDSYS','OUTLN','AQ_ADMINISTRATOR_ROLE','CONNECT','CTXSYS','DBSNMP','EXP_FULL_DATABASE','IMP_FULL_DATABASE'
) order by 1
;
cursor c2 (v_priv varchar2,v_grantee varchar2) is
select /*+ Rule */ distinct role, TABLE_NAME ,'OWNER' owner, PRIVILEGE
from role_tab_privs where role=v_priv and PRIVILEGE <> 'SELECT'
union
select /*+ Rule */ GRANTEE,TABLE_NAME,OWNER,PRIVILEGE from dba_tab_privs where grantee =v_grantee and PRIVILEGE <> 'SELECT';
v_grantee varchar2(50);
v_priv varchar2(50);
v_role varchar2(50);
v_Tab varchar2(100);
v_tab_priv varchar2(100);
Begin
--dbms_output.put_line('UserName|Priv/Role|TableName|Grants');
execute immediate ('truncate table oasis.privs');
for x in c1
loop
v_grantee := x.grantee;
v_priv := x.priv;
--dbms_output.put_line(v_grantee||'|'||v_priv);
insert into oasis.privs (grantee,priv) values (x.grantee,x.priv);
--dbms_output.put_line(x.grantee||'|'||x.priv);
for y in c2(x.priv,x.grantee)
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; 好东西啊 顶 感谢分享 好东西，谢谢分享。 不错！学习
收藏收藏 这个可以有 收藏，感谢分享 谢谢分享 thanks good Thx for sharing 谢谢分享 thank you ！ 这个厉害，收藏慢慢研究 非常好的帖子,必须顶！！！ 这个好，抱走了！ 不错！收藏下 mark。。收藏下 Thank you for share! Thank you for share! 谢谢！！！！ 感谢分享！ 非常好，谢谢！ 来点说明下会更好些，另外说明下哪个版本有效？ 谢了，！！
页:
[1]