dba 常用管理脚本收集

ALLSTARS_ORACLE

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;

rongshiyuan

好东西啊  顶

jxztj2010

感谢分享

dba_sky

好东西，谢谢分享。

strugglejiang

不错！学习

laughing

收藏收藏

masicong11

这个可以有

peanutshell

收藏，感谢分享

robin

谢谢分享

sunqch

thanks  good

gavintgh

Thx for sharing

brucewoo

谢谢分享

gdf

thank you ！

devinailletian

这个厉害，收藏慢慢研究

kisswolf

非常好的帖子,必须顶！！！

s_chrison

这个好，抱走了！

梦想自由

不错！收藏下

manwuqk

mark。。收藏下

rocky_miko

Thank you for share!

gululu

Thank you for share!

echo

谢谢！！！！

justinleisure

感谢分享！

davetobe

非常好，谢谢！

zengmuansha

来点说明下会更好些，另外说明下哪个版本有效？

desert_xu

谢了，！！