No image

Oracle Monitoring Scripts 10

1. ts_file

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set verify off
col tablespace_name for a30
col file_name       for a60
col tot_mb          for 999,999,999
col free_mb         for 999,999,999
col pct_used        for 999 heading "PCT|USED"
col AUTOEXTENSIBLE  for a7 heading "AUTO|EXTEND"

break on tablespace_name

select t.tablespace_name
      ,d.file_id
      ,file_name
      ,d.mbytes tot_mb
      ,f.mbytes free_mb
      ,(d.mbytes-f.mbytes) * 100 / d.mbytes pct_used
      ,AUTOEXTENSIBLE
  from dba_tablespaces t,
       (
       select tablespace_name
             ,file_id
             ,max(file_name) file_name
             ,max(AUTOEXTENSIBLE) AUTOEXTENSIBLE
             ,sum(bytes)/1024/1024 mbytes
         from dba_data_files
        where tablespace_name like upper('&&1')||'%'
        group by tablespace_name,file_id
       union all
       select tablespace_name
             ,file_id
             ,max(file_name) file_name
             ,max(AUTOEXTENSIBLE) AUTOEXTENSIBLE
             ,sum(bytes)/1024/1024 mbytes
         from dba_temp_files
        where tablespace_name like upper('&&1')||'%'
        group by tablespace_name,file_id
       ) d,
       (
       select tablespace_name
             ,file_id
             ,sum(bytes)/1024/1024 mbytes
         from dba_free_space
        where tablespace_name like upper('&&1')||'%'
        group by tablespace_name,file_id
       ) f
 where t.tablespace_name = d.tablespace_name (+)
   and d.tablespace_name = f.tablespace_name (+)
   and d.file_id = f.file_id (+)
   and t.tablespace_name like upper('&&1')||'%'
 order by 1,2
/

clear breaks

col tablespace_name clear
col file_name       clear
col tot_mb          clear
col free_mb         clear
col pct_used        clear
col AUTOEXTENSIBLE  clear

set verify on

2. ts_seg

-- writer : ezis.cloud (noname)
-- Description  :  tablespace segments
-- Requirements : 

set    echo off
set    verify off

start  arg1
col    owner           for a14
col    segment_name    for a32
col    tablespace_name for a22
col    segment_type    for a16
col    partition_name  for a15
col    mbytes          for 999,999.99

select owner, segment_name, tablespace_name, segment_type, partition_name, bytes/1024/1024 mbytes
from   dba_segments
where  tablespace_name like upper('&1.') || '%'
;

col    owner           clear
col    segment_name    clear
col    tablespace_name clear
col    segment_type    clear
col    partition_name  clear
col    mbytes          clear

set    verify on

3. ts_used

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

# prompt name : TABLESPACE STORAGE 

set pages 1000 echo off 
col tablespace_name format a20
col bytes           format 999,999
col free            format 999,999
col "Used(%)"       format 990.00

# prompt 1. For large-capacity TS, it is recommended that the Used(%) be kept below 80 to 90%.

select d.tablespace_name, 
       d.bytes "Allocated(K)", 
       f.bytes "Free Byte(K)", 
       round((d.bytes - f.bytes)/d.bytes*100,1) "Used(%)", 
       f.Max_Free "Max Free(K)", 
       s.Max_Ext "Max Ext(K)"
  from ( select sum(bytes)/1024 bytes, tablespace_name 
           from dba_data_files 
          group by tablespace_name) d, 
       ( select sum(bytes)/1024 bytes, tablespace_name, max(bytes)/1024 Max_Free
           from dba_free_space 
          group by tablespace_name) f,
       ( select tablespace_name, max(next_extent)/1024 Max_Ext
           from dba_segments
          group by tablespace_name) s 
 where  d.tablespace_name = f.tablespace_name(+)
   and  d.tablespace_name = s.tablespace_name
   and (  round((d.bytes - f.bytes)/d.bytes*100,1) >= 80 )
 order by f.Max_Free
/

# prompt 2. Max Free(K) must be at least greater than Max Ext(K) * 3.

select d.tablespace_name, 
       d.bytes "Allocated(K)", 
       f.bytes "Free Byte(K)", 
       round((d.bytes - f.bytes)/d.bytes*100,1) "Used(%)", 
       f.Max_Free "Max Free(K)", 
       s.Max_Ext "Max Ext(K)"
  from ( select sum(bytes)/1024 bytes, tablespace_name 
           from dba_data_files 
          group by tablespace_name) d, 
       ( select sum(bytes)/1024 bytes, tablespace_name, max(bytes)/1024 Max_Free
           from dba_free_space 
          group by tablespace_name) f,
       ( select tablespace_name, max(next_extent)/1024 Max_Ext
           from dba_segments
          group by tablespace_name) s 
 where  d.tablespace_name = f.tablespace_name(+)
   and  d.tablespace_name = s.tablespace_name
       and ( s.Max_Ext*3 > f.Max_Free )
 order by f.Max_Free
/

4. ts_warning

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

# prompt "A:Lack of continuous free space by the size of required extents"
prompt "B:In case of being allocated by user but exceeding quota"
prompt "C:If expected to exceed maxextents"
col Type form a6
col tablespace_name form a15
col Segment_name form a15
col Segment_Type form a10
col Description form a30

select 'A' type, s.tablespace_name, s.segment_name, s.segment_type,
       to_char(v2.last_extent*(1+s.pct_increase/100))||' > '||to_char(v1.max_byte) description 
from dba_segments s,
    (select tablespace_name, max(bytes) max_byte
     from dba_free_space
     group by tablespace_name) v1,
    (select tablespace_name, owner, segment_name, bytes last_extent
     from dba_extents ss
     where extent_id = (select max(extent_id) 
                        from dba_extents 
                        where ss.tablespace_name = tablespace_name and 
                              ss.owner = owner and 
                              ss.segment_name = segment_name)) v2
where s.tablespace_name = v1.tablespace_name and
      s.tablespace_name = v2.tablespace_name and
      s.owner = v2.owner and
      s.segment_name = v2.segment_name and
      decode(s.pct_increase, 0, s.next_extent, v2.last_extent * ( 1 + s.pct_increase/100)) > v1.max_byte
union all
select 'B', s.tablespace_name, s.segment_name, s.segment_type,
       to_char(q.max_bytes-q.bytes)||' < '||to_char(v2.last_extent*(1+s.pct_increase/100))
from dba_segments s, dba_ts_quotas q, 
    (select tablespace_name, owner, segment_name, max(bytes) last_extent
     from dba_extents
     group by tablespace_name, owner, segment_name) v2
where s.tablespace_name = q.tablespace_name and
      s.owner = q.username and
      s.tablespace_name = v2.tablespace_name and
      s.owner = v2.owner and
      s.segment_name = v2.segment_name and
      q.max_bytes - q.bytes < v2.last_extent * ( 1 + s.pct_increase/100) and
      q.max_bytes > 0
union all
select 'C', s.tablespace_name, s.segment_name, s.segment_type, 
       to_char(s.extents)||'/'||to_char(s.max_extents)||' > 60%'
from dba_segments s
where s.extents/max_extents > 60/100 and
s.max_extents <> 0
/

5. ts30

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set echo off

col Allocated for  99,999,999,999  heading "Allocated(M)"
col Used      for  99,999,999,999  heading "Used(M)"
col Free      for  99,999,999,999  heading "Free Byte(M)"
col Max_free  for  99,999,999,999  heading "Max Free(M)"
col PCT_Used  for  99.99        heading "Used(%)"
col TABLESPACE_NAME for  a30

set feedback off verify off

SELECT TABLESPACE_NAME
       ,SUM(t_mbytes)                                   AS      Allocated
       ,SUM(t_mbytes)-SUM(f_mbytes)                     AS      Used
       ,SUM(f_mbytes)                                   AS      Free
       ,(SUM(t_mbytes)-SUM(f_mbytes))/SUM(t_mbytes)*100 AS      PCT_Used
       ,sum(mf_mbytes)                                  AS      Max_free
FROM (
SELECT TABLESPACE_NAME, SUM(bytes/1024/1024) f_mbytes,0 t_mbytes, max(bytes/1024/1024) mf_mbytes
FROM dba_free_space a
WHERE a.TABLESPACE_NAME LIKE upper('&&1.%')
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT TABLESPACE_NAME, 0,SUM(bytes/1024/1024) t_mbytes,0
FROM dba_data_files a
WHERE a.TABLESPACE_NAME LIKE upper('&&1.%')
GROUP BY TABLESPACE_NAME
)
GROUP BY rollup(TABLESPACE_NAME)
having       (SUM(t_mbytes)-SUM(f_mbytes))/SUM(t_mbytes)*100 < 30
order BY TABLESPACE_NAME
/

set feedback on verify on

col Allocated clear
col Used      clear
col Free      clear
col Max_free  clear
col PCT_Used  clear
col TABLESPACE_NAME clear

6. ts80

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set echo off

col Allocated for  99,999,999,999  heading "Allocated(M)"
col Used      for  99,999,999,999  heading "Used(M)"
col Free      for  99,999,999,999  heading "Free Byte(M)"
col Max_free  for  99,999,999,999  heading "Max Free(M)"
col PCT_Used  for  99.99        heading "Used(%)"
col TABLESPACE_NAME for  a30

set verify off

SELECT TABLESPACE_NAME
       ,SUM(t_mbytes)                                   AS      Allocated
       ,SUM(t_mbytes)-SUM(f_mbytes)                     AS      Used
       ,SUM(f_mbytes)                                   AS      Free
       ,(SUM(t_mbytes)-SUM(f_mbytes))/SUM(t_mbytes)*100 AS      PCT_Used
       ,sum(mf_mbytes)                                  AS      Max_free
FROM (
SELECT TABLESPACE_NAME, SUM(bytes/1024/1024) f_mbytes,0 t_mbytes, max(bytes/1024/1024) mf_mbytes
FROM dba_free_space a
WHERE 1=1
--and a.TABLESPACE_NAME LIKE upper('&&1.%')
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT TABLESPACE_NAME, 0,SUM(bytes/1024/1024) t_mbytes,0
FROM dba_data_files a
WHERE 1=1
--and a.TABLESPACE_NAME LIKE upper('&&1.%')
GROUP BY TABLESPACE_NAME
)
GROUP BY rollup(TABLESPACE_NAME)
having (SUM(t_mbytes)-SUM(f_mbytes))/SUM(t_mbytes)*100 > &1.
order BY 5 desc
/

set verify on

col Allocated clear
col Used      clear
col Free      clear
col Max_free  clear
col PCT_Used  clear
col TABLESPACE_NAME clear

7. tseg

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col owner         for a15
col segment_name  for a25
col PARTITION_NAME  for a25
col tablespace_name  for a25
select /*+ rule */owner, segment_name, partition_name, tablespace_name ,bytes/1024/1024 mbytes, extents
from dba_segments
where segment_type = 'TEMPORARY'
and owner <> 'SYS'
order by TABLESPACE_NAME
/
col owner         clear
col segment_name  clear
col PARTITION_NAME  clear
col tablespace_name  clear

8. tt

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col event for a27
col P1 for a20
col P2 for a20
set pages 45
set pause on
select sid,event,p1text||'=>'||p1 "P1",p2text||'=>'||p2 "P2" from v$session_wait
where event not like 'SQL%'
and   event not like 'Null%'
/

col "DB User" for a8
col "Client User" for a8
col "Machine" for a12
col "Terminal" for a12
col "Program" for a20

SELECT 
  S.USERNAME "DB User",
  S.OSUSER "Client User",
  S.MACHINE "Machine",
  S.TERMINAL "Terminal",
  S.PROGRAM "Program"
FROM 
  V$SESSION S,
  V$PROCESS P,
  sys.V_$SESS_IO si
WHERE 
  S.paddr = P.addr  and
  si.sid(+)=s.sid   and
  (s.USERNAME is not null) and
  (s.osuser<>'SYSTEM')  and
  (s.type<>'BACKGROUND') and
  si.sid = &sid
/


select s.sid, s.serial#, substr(s.program,1,20) program , q.sql_text
from  v$session s, v$process p, v$sqlarea q,v$session_wait w
where s.paddr = p.addr
and   s.sql_address = q.address
and   s.sid = w.sid
and   s.sid= &sid
and   s.sql_hash_value = q.hash_value
/

select sql_text
from V$open_cursor
where sid= &sid
/

set pause off

9. ttt

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set pages 45 pause on

col "DB User"     for a8
col "Client User" for a8
col "Machine"     for a12
col "Terminal"    for a12
col "Program"     for a20

SELECT
       S.USERNAME "DB User",
       S.OSUSER   "Client User",
       S.MACHINE  "Machine",
       S.TERMINAL "Terminal",
       S.PROGRAM  "Program"
FROM
       V$SESSION S,
       V$PROCESS P,
       SYS.V_$SESS_IO SI
WHERE  S.PADDR = P.ADDR
AND    SI.SID(+)=S.SID
AND   (S.USERNAME IS NOT NULL)
AND   (S.OSUSER<>'SYSTEM')
AND   (S.TYPE<>'BACKGROUND')
AND    P.SPID = &SPID
/

SELECT S.SID, S.SERIAL#, SUBSTR(S.PROGRAM,1,20) PROGRAM, Q.SQL_TEXT
FROM   V$SESSION S, V$PROCESS P, V$SQLAREA Q, V$SESSION_WAIT W
WHERE  S.PADDR = P.ADDR
AND    S.SQL_ADDRESS = Q.ADDRESS
AND    S.SID = W.SID
AND    P.SPID = &SPID
/

set pause off

10. undo1

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col module format a30

select instance_number, session_id, session_serial#, sql_id, module from DBA_HIST_ACTIVE_SESS_HISTORY 
where session_id in (select st.sid from v$sesstat st , v$session ss
where st.statistic# = 177 
and ss.sid = st.sid
and ss.module is not null
and st.value > 0)
and p1 in (select file_id from dba_data_files where file_name like '%UNDO%')
and instance_number = 3
and event_id = 2652584166
and snap_id in ( select max(snap_id) from dba_hist_snapshot);

11. undo2

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select bb.begin_interval_time ||' ~ '||aa.begin_interval_time "TIME", aa.instance_number, aa.value - bb.value from
(select sh.begin_interval_time, ss.instance_number, ss.value from dba_hist_sysstat ss, dba_hist_snapshot sh
where ss.stat_id = 1054055970
and ss.instance_number = sh.instance_number
and ss.dbid = sh.dbid
and sh.snap_id = ss.snap_id
and ss.snap_id = (select max(snap_id) from dba_hist_snapshot)) aa,
(select sh.begin_interval_time, ss.instance_number, ss.value from dba_hist_sysstat ss, dba_hist_snapshot sh
where ss.stat_id = 1054055970
and ss.instance_number = sh.instance_number
and ss.dbid = sh.dbid
and sh.snap_id = ss.snap_id
and ss.snap_id = (select max(snap_id) -1 from dba_hist_snapshot)) bb
where aa.instance_number = bb.instance_number;

12. unused

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col tablespace_name format a15
col file_name format a30
col defined format 9,999,999,999
col unused format 9,999,999,999

spool unused.txt
select TABLESPACE_NAME, sum(bytes)/(1024*1024) unused
from dba_free_space
group by tablespace_name
/
spool off

13. upd_test

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set timing on;
set serveroutput on;
declare
        v_count number default 0;
begin
        for dc in ( select /*+ parallel (a 10)*/ telno,telst,rowid row_id from cosmgaiw a)
        loop
                update cosmgaiw
                set telno = dc.telno,
		    telst = dc.telst
                where rowid = dc.row_id;
        if sql%found then v_count := v_count + 1;
        end if;
                if mod ( v_count , 10000 ) = 0 then commit;
                end if;
        end loop;
        commit;
        dbms_output.put_line ( v_count ||' ROW 가 update 되었습니다.');
end;
/
exit

14. used_urec

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select t.used_urec
from v$transaction t, v$session s
where t.addr = s.taddr and s.sid = '&sid'
/

15. user_privs

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col    username           for  a15
col    default_tablespace for  a35
col    privilege          for  a35

break  on username on default_tablespace on CREATED

select username,default_tablespace, CREATED,p.privilege 
  from dba_users u,
      (select grantee,privilege from dba_sys_privs
       union all
       select grantee,granted_role from dba_role_privs) p
 where u.username = p.grantee (+)
   and u.username like '&1'||'%'
   and u.username not in 
    ( 'SYS','SYSTEM','DBSNMP','DIP','OUTLN','TSMSYS','WMSYS','ORACLE_OCM','FLOWS_030000'
     ,'FLOWS_FILES','SI_INFORMTN_SCHEMA','XDB','XS$NULL'
     ,'ORDPLUGINS', 'ORDSYS','ANONYMOUS','APEX_PUBLIC_USER'
     ,'EXFSYS','MDSYS','MGMT_VIEW','SYSMAN'
      )
order  by username,p.privilege 
/

clear  breaks

col    username           clear
col    default_tablespace clear
col    privilege          clear

16. usr

-- writer : ezis.cloud (noname)
-- Description  :  session info. by username
-- Requirements : 

col    sid       for a11
col    username  for a12
col    osuser    for a15 truncate
col    machine   for a20 truncate
col    terminal  for a10 truncate
col    program   for a20 truncate
col    action    for a20 truncate
col    process   for a18
col    status    for a10
col    event     for a28 truncate
col    spid      for a19

set    verify off
start  arg1

select s.SID||','||s.SERIAL# SID,
       s.USERNAME, s.STATUS, s.OSUSER, 'kill -9 '||p.spid spid, s.TERMINAL, s.program, s.action, s.process, w.event
from   v$session s, v$session_wait w ,v$process p
where  s.sid = w.sid
and    s.paddr=p.addr
and    s.username = nvl(upper('&&1'),'ONSEL')
order  by s.TERMINAL,s.osuser
/

set    verify on

col    sid       clear
col    username  clear
col    osuser    clear
col    machine   clear
col    terminal  clear
col    program   clear
col    action    clear
col    process   clear
col    event     clear
col    spid      clear
col    status    clear

17. var

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set verify off termout off timing off feedback off
spool v.sql
select 'var &&1 varchar2(30);' from dual;
spool off
set verify on termout on timing on feedback on
!grep "^var" v.sql > v1.sql;\rm -f v.sql
@v1.sql
!\rm -f v1.sql
exec :&&1 := '&&2';

18. vidx

-- writer : ezis.cloud (noname)
-- Description  :  index description
-- Requirements : 

set    verify off feedback off timi off

COL    table_name           FOR A26 trunc
COL    INDEX_NAME           FOR A30
COL    COLUMN_NAME          FOR A26
COL    COLUMN_POSITION      FOR 999 trunc heading "POS"
COL    COLUMN_LENGTH        FOR 9999 heading "LEN"
COL    index_owner          FOR a15
COL    UNIQUENESS           FOR a9
COL    PCT_FREE             FOR 999 heading "PCT"
COL    BLEVEL               FOR 999 heading BLVL
col    COLUMN_EXPRESSION    for a32

break  on TABLE_NAME on INDEX_OWNER on INDEX_NAME

start  arg2

VAR    V_OWNER         VARCHAR2(20);
VAR    V_TABLE_NAME    VARCHAR2(30);
EXEC   :V_OWNER        := CASE WHEN INSTRB('&&1','.') > 0 THEN UPPER(SUBSTR('&&1', 1,INSTRB('&&1','.')-1)) ELSE '&dft_owner' END ;
EXEC   :V_TABLE_NAME   := UPPER( SUBSTR('&&1', INSTRB('&&1','.')+1));

set    termout off

alter  session set nls_date_format='yyyy-mm-dd hh24:mi:ss' ;

--     db version 별 dictionary column 처리
col    col_visibility   new_value col_visibility

select
       case when version  >= 11 then 'VISIBILITY'    else 'NULL' end as col_visibility
from  (select to_number(substr(version,1,instr(version,'.',1,2)-1)) as version from v$instance)
/

col    col_visibility   clear

set    termout on

SELECT
       a.index_owner||'.'||A.TABLE_NAME TABLE_NAME
     , A.INDEX_NAME
     , A.COLUMN_NAME
     , A.COLUMN_POSITION
     , A.COLUMN_LENGTH
     , B.UNIQUENESS
     ,(select constraint_type
       from   dba_constraints
       where  owner = b.owner 
       and    CONSTRAINT_NAME = b.INDEX_NAME 
       and    CONSTRAINT_TYPE = 'P') constraint_type
     , b.pct_free
     , BLEVEL
     , LEAF_BLOCKS leafcnt
     , DISTINCT_KEYS distkey
     , partitioned
     , &col_visibility as VISIBILITY
     --,AVG_LEAF_BLOCKS_PER_KEY 
     --,AVG_DATA_BLOCKS_PER_KEY 
     , CLUSTERING_FACTOR clufac
     ,(select created from dba_objects where owner = b.owner and object_name = b.index_name and object_type like 'INDEX%' and rownum <= 1) as created
FROM ( select * from dba_IND_COLUMNS
       where  TABLE_OWNER = :V_OWNER
       and    table_name  = :V_TABLE_NAME
     ) A,
     ( select * from dba_INDEXES
       where  TABLE_OWNER = :V_OWNER
       and    table_name  = :V_TABLE_NAME
       and    INDEX_TYPE not like 'LOB%'
     ) B
WHERE  1=1
and    A.INDEX_NAME  = B.INDEX_NAME
and    a.index_owner = b.OWNER
ORDER  BY 1, 2, 4
;


select INDEX_OWNER
     , INDEX_NAME
     , COLUMN_POSITION
     , COLUMN_EXPRESSION
from   DBA_IND_EXPRESSIONS
WHERE  1=1
and    TABLE_OWNER = :V_OWNER
and    table_name  = :V_TABLE_NAME
ORDER  BY 1,2,3
;

COL    PARTITIONING_TYPE           HEADING "PARTITIONING|TYPE"
COL    SUBPARTITIONING_TYPE        HEADING "SUBPARTITIONING|TYPE"
COL    PARTITION_COUNT             HEADING "PARTITION|COUNT"
COL    DEF_SUBPARTITION_COUNT      HEADING "DEF|SUBPARTITION|COUNT"
COL    PARTITIONING_KEY_COUNT      HEADING "PARTITIONING|KEY_COUNT"
COL    SUBPARTITIONING_KEY_COUNT   HEADING "SUBPARTITIONING|KEY_COUNT"

SELECT OWNER||'.'|| TABLE_NAME AS TABLE_NAME
     , INDEX_NAME
     , PARTITIONING_TYPE
     , SUBPARTITIONING_TYPE
     , PARTITION_COUNT
     , DEF_SUBPARTITION_COUNT
     , PARTITIONING_KEY_COUNT
     , SUBPARTITIONING_KEY_COUNT
     , LOCALITY
FROM   DBA_PART_INDEXES
WHERE  1=1
and    OWNER = :V_OWNER
and    table_name  = :V_TABLE_NAME
;

clear  breaks

COL    TABLE_NAME                  clear
COL    INDEX_NAME                  clear
COL    COLUMN_NAME                 clear
COL    COLUMN_POSITION             clear
COL    COLUMN_LENGTH               clear
COL    index_owner                 clear
COL    UNIQUENESS                  clear
COL    pct_free                    clear
COL    BLEVEL                      clear
col    COLUMN_EXPRESSION           clear
COL    PARTITIONING_TYPE           clear
COL    SUBPARTITIONING_TYPE        clear
COL    PARTITION_COUNT             clear
COL    DEF_SUBPARTITION_COUNT      clear
COL    PARTITIONING_KEY_COUNT      clear
COL    SUBPARTITIONING_KEY_COUNT   clear

set    verify on feedback on timi on

19. warning

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

# prompt "A:Lack of continuous free space as much as required extent "
prompt "B:In case of exceeding the quota allocated per user "
prompt "C:If maxextents are expected to be exceeded."

col Type            form a6
col tablespace_name form a25
col Segment_name    form a30
col Segment_Type    form a20
col Description     form a30
 
select 'A' type, s.tablespace_name, s.segment_name, s.segment_type,
       to_char(v2.last_extent*(1+s.pct_increase/100))||' > '||to_char(v1.max_byte) description 
from   dba_segments s,
      (select tablespace_name, max(bytes) max_byte
       from   dba_free_space
       group by tablespace_name) v1,
      (select tablespace_name, owner, segment_name, bytes last_extent
       from   dba_extents ss
--       where  owner = 'ONADMIN'
       where  extent_id = (select max(extent_id) 
                           from   dba_extents 
                           where  ss.tablespace_name = tablespace_name
                           and    ss.owner = owner
                           and    ss.segment_name = segment_name)) v2
where  s.tablespace_name = v1.tablespace_name
and    s.tablespace_name = v2.tablespace_name
and    s.owner = v2.owner
and    s.segment_name = v2.segment_name
--and    s.owner = 'ONADMIN'
and    decode(s.pct_increase, 0, s.next_extent, v2.last_extent * ( 1 + s.pct_increase/100)) > v1.max_byte
union all
select 'B', s.tablespace_name, s.segment_name, s.segment_type,
       to_char(q.max_bytes-q.bytes)||' < '||to_char(v2.last_extent*(1+s.pct_increase/100))
from   dba_segments s,
       dba_ts_quotas q, 
      (select tablespace_name, owner, segment_name, max(bytes) last_extent
       from   dba_extents
       group by tablespace_name, owner, segment_name) v2
where  s.tablespace_name = q.tablespace_name
and    s.owner = q.username
and    s.tablespace_name = v2.tablespace_name
and    s.owner = v2.owner
and    s.segment_name = v2.segment_name
--and    s.owner = 'ONADMIN'
and    q.max_bytes - q.bytes < v2.last_extent * ( 1 + s.pct_increase/100)
and    q.max_bytes > 0
union all
select 'C', s.tablespace_name, s.segment_name, s.segment_type, 
       to_char(s.extents)||'/'||to_char(s.max_extents)||' > 60%'
from   dba_segments s
where  s.extents/max_extents > 60/100
--and    s.owner = 'ONADMIN'
and    s.max_extents <> 0
/

20. workarea

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col mem     for 999,999,999 heading "ACTUAL_MEM(K)"
col MAX_MEM for 999,999,999 heading "MAX MEM(K)"
col TSIZE   for 999,999,999 heading "TEMPSEG_SIZE(K)"


SELECT to_number(decode(SID, 65535, NULL, SID)) sid, QCSID,operation_type  OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE, 
trunc(ACTUAL_MEM_USED/1024) MEM,  
trunc(MAX_MEM_USED/1024) MAX_MEM, 
NUMBER_PASSES PASS,  trunc(TEMPSEG_SIZE/1024) TSIZE  
FROM V$SQL_WORKAREA_ACTIVE
;
--SELECT INST_ID,to_number(decode(SID, 65535, NULL, SID)) sid, QCSID,operation_type  OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE, 
--trunc(ACTUAL_MEM_USED/1024) MEM,  
--trunc(MAX_MEM_USED/1024) "MAX MEM", 
--NUMBER_PASSES PASS,  trunc(TEMPSEG_SIZE/1024) TSIZE  
--FROM GV$SQL_WORKAREA_ACTIVE
--;

col mem     clear
col MAX_MEM clear
col TSIZE   clear

21. X

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col LAST_DDL_TIME for a20
col script   for a110

select to_char(LAST_DDL_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_DDL_TIME,
       case
        when (object_type = 'SYNONYM' and owner = 'PUBLIC') then
           'select count(*) from ' || object_name || ' where 1=2 ;'
--           (select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' 
--              from dba_synonyms
--             where 1=1
--               and owner = 'PUBLIC'
--               and synonym_name = object_name )
        when (object_type = 'SYNONYM' and owner <> 'PUBLIC') then
           'select count(*) from ' || owner || '.' || object_name || ' where 1=2 ;'
        when (object_type = 'PACKAGE BODY') then
           'alter package '||owner||'.'||object_name||' compile body;'
        when (object_type = 'PACKAGE') then
           'alter package '||owner||'.'||object_name||' compile package;'
        else
           'alter '||object_type||' '||owner||'.'||object_name||' compile;'
       end as script
from   dba_objects
where  status = upper('invalid')
and    owner not like '%SYS%' and    owner not in ('TOAD')
and    object_type not in ('JAVA CLASS', 'UNDEFINED')
order by object_name,owner
/

col LAST_DDL_TIME clear
col script   clear

22. x_ksuse

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col    sid  for 99999
select
indx   as sid
,a.* 
from   x$ksuse a 
where  rownum <= 5
;

23. xcol

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set termout off
column "allocated(k)"  clear
column "client user"   clear
column "db user"       clear
column "free byte(k)"  clear
column "null?"         clear
column "serial#"       clear
column "used(%)"       clear
column "used(k)"       clear
column ascii_gtxid clear
column blank1     clear
column blocking_others clear
column bytes clear
column cmd clear
column col clear
column column_length   clear
column column_name  clear
column column_position clear
column comments     clear
column cost         clear
column created      clear
column csid     clear
column ctr_dt clear
column ddl_dt clear
column default      clear
column defined clear
column description clear
column dummy clear
column entry_date    clear
column event    clear
column extents clear
column file_name clear
column fname clear
column free            clear
column freesize clear
column fsize clear
column host_name clear
column hwmsize clear
column id1     clear
column id2     clear
column in/out       clear
column index_name clear
column kbytes   clear
column last clear
column last_ddl_time clear
column last_sec    clear
column latchname clear
column lck	clear
column lock_id1       clear
column lock_id2       clear
column lock_type      clear
column log_user clear
column machine   clear
column max_ext                clear
column mode_held       clear
column mode_requested clear
column module     clear
column name  clear
column next     clear
column next_sec clear
column npid clear
column object     clear
column object_name  clear
column object_type clear
column operation    clear
column osuser     clear
column owner         clear
column p1 clear
column p2 clear
column p_name                 clear
column path  clear
column pgm       clear
column pgm_id    clear
column pid clear
column pr    clear
column prg	clear
column process   clear
column program    clear
column pstart       clear
column pstop        clear
column pw    clear
column referenced_name clear
column rows         clear
column rssize clear
column sc  clear
column schema_user clear
column script   clear
column segment_name           clear
column segment_type    clear
column ser	clear
column sid        clear
column sid_serial clear
column slot    clear
column snapshot_site clear
column sql_text   clear
column status     clear
column stu	clear
column t_name clear
column table_name      clear
column tablespace_name clear
column tblsp clear
column term    clear
column terminal   clear
column text  clear
column this_sec    clear
column totalsize clear
column tq           clear
column ts_name                clear
column tt_name clear
column type clear
column uniqueness clear
column unused clear
column urec    clear
column usedsize clear
column username   clear
column usn clear
column usr 	clear
column what        clear
column work_desc clear
column DB_LINK clear
column HOST clear
clear bre
set verify on termout on

24. xidx

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

SELECT 'alter index '||owner||'.'||index_name||' rebuild nologging parallel 4;' stmt
SELECT 'alter index '||owner||'.'||index_name||' rebuild nologging ;' stmt
FROM dba_indexes a
WHERE status <> 'VALID'
AND owner not in ('SYS','SYSTEM','MIG_CORE_ADM','MIG_CUST_ADM')
and partitioned <> 'YES'
union all
SELECT 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' ;' stmt
--SELECT 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' nologging parallel 4;' stmt
FROM dba_ind_partitions a
WHERE status = 'UNUSABLE'
AND index_owner not in ('SYS','SYSTEM','MIG_CORE_ADM','MIG_CUST_ADM')
order by 1
/
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]