No image

Oracle Monitoring Scripts 2

1. bh

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

SELECT a.*, b.name,c.name 
FROM ( 
SELECT objd,COUNT(*) 
FROM v$bh -- 
GROUP BY objd 
ORDER BY 2 DESC 
) a,sys.obj$ b, sys.USER$ c 
WHERE a.objd = b.obj# 
AND b.owner# = c.USER# 
AND ROWNUM <= 10

2. bytes

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

select owner, round(bytes/1024/1024) byte from dba_segments where segment_name = '&segment_name';

3. check_stat

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

COL GLOBAL        FOR 999,999,999,999
COL PARTITION     FOR 999,999,999,999
COL TABLE_NAME    FOR A20
COL INDEX_NAME    FOR A26
COL PCT           FOR 999
COL T_ROWS        FOR 999,999,999,999
COL I_ROWS        FOR 999,999,999,999

SELECT *
FROM  (
       SELECT G.INDEX_NAME
             ,G.NUM_ROWS GLOBAL
             ,P.NUM_ROWS PARTITION
             ,CASE WHEN P.NUM_ROWS = 0 THEN 999
                   ELSE                     G.NUM_ROWS*100/P.NUM_ROWS
              END PCT
       FROM  (SELECT INDEX_NAME
                    ,COUNT(*) CNT
                    ,SUM(NUM_ROWS) NUM_ROWS
              FROM   DBA_IND_PARTITIONS 
              WHERE  INDEX_OWNER = 'CORE_P_ADM' 
              GROUP BY INDEX_NAME ORDER BY 1) P
            ,(SELECT INDEX_NAME
                    ,NUM_ROWS 
              FROM   DBA_INDEXES 
              WHERE  OWNER = 'CORE_P_ADM' 
              AND    PARTITIONED = 'YES') G
       WHERE  G.INDEX_NAME = P.INDEX_NAME
       AND    NVL(P.NUM_ROWS,0) <> NVL(G.NUM_ROWS,0)
      )
WHERE PCT NOT BETWEEN 90 AND 110
ORDER BY 4
;


SELECT *
FROM  (
       SELECT T.TABLE_NAME
             ,I.INDEX_NAME
             ,T.NUM_ROWS T_ROWS
             ,I.NUM_ROWS I_ROWS
             ,CASE WHEN T.NUM_ROWS = 0 THEN 999
                   ELSE                     I.NUM_ROWS*100/T.NUM_ROWS
              END PCT
       FROM   DBA_TABLES T
             ,DBA_INDEXES I
       WHERE  T.TABLE_NAME = I.TABLE_NAME
       AND    I.INDEX_NAME LIKE '%_PK'
       AND    T.OWNER LIKE 'CORE_P_ADM'
       AND    NVL(T.NUM_ROWS,0) <> NVL(I.NUM_ROWS,0)
      )
WHERE PCT NOT BETWEEN 90 AND 110
ORDER BY 5
;


COL GLOBAL        CLEAR
COL PARTITION     CLEAR
COL PCT           CLEAR
COL TABLE_NAME    CLEAR
COL INDEX_NAME    CLEAR
COL T_ROWS        CLEAR
COL I_ROWS        CLEAR

4. check_syn

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

-- view synonym check
select --owner, view_name 
'create public synonym '
||view_name
||' for '
||owner
||'.'
||view_name
||';' stmt
from dba_views v 
where 1=1
and substr(owner,-6) in ('_D_ADM','_P_ADM')
and not exists ( select * from dba_synonyms 
                  where table_owner = v.owner 
                    and table_name = v.view_name 
                    and synonym_name = v.view_name 
                    and owner = 'PUBLIC' )
union all
-- table synonym check
select --owner, table_name, 
'create public synonym '
||table_name
||' for '
||owner
||'.'
||table_name
||';' stmt
from dba_tables t
where 1=1
and substr(owner,-6) in ('_D_ADM','_P_ADM')
and table_name like 'TB________%'
and table_name not in ('TB_PFPIF03','TB_CISSS99')
and not exists ( select * from dba_synonyms 
                  where table_owner = t.owner 
                    and table_name = t.table_name 
                    and synonym_name = t.table_name 
                    and owner = 'PUBLIC' )

5. chkobj#

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

select owner, object_name, object_type, created, status ,
       case status
        when 'INVALID'
        then chr(27)||'[1;30;47m Object Invalid ALERT'||chr(27)||'[0m'||chr(7)||chr(7)
       end as "alert message"
from dba_objects 
where status = 'INVALID'
 and owner in ('PUBLIC','SYS','SYSTEM','PVGD','PVGDONL','PVGDBAT','ORADBA','DBA03');
 
select index_owner, index_name, partition_name, status,
       case
        when status = 'UNUSABLE'
        then chr(27)||'[1;30;47m Part-Index Unusable ALERT'||chr(27)||'[0m'||chr(7)||chr(7)
       end as "alert message"
from dba_ind_partitions 
where index_owner IN ('PVGD','TVGD','DBA03') 
and  status = 'UNUSABLE' ;

select owner, table_name, index_name, status,
       case
        when status = 'UNUSABLE'
        then chr(27)||'[1;30;47m Normal-Index Unusable ALERT'||chr(27)||'[0m'||chr(7)||chr(7)
       end as "alert message"
from dba_indexes 
where owner IN ('PVGD','TVGD','DBA03') 
and status = 'UNUSABLE' ;

6. chkobj

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

select 'CHECK OBJ:(object invaild)' || to_char(systimestamp,'YYYY/MM/DD-HH24:MI:SS') || ' ' || object_type || ' ' || owner || ' ' || object_name || ' ' || status || ' ' || created
from dba_objects 
where status = 'INVALID'
 and owner in ('PUBLIC','SYS','SYSTEM','PVGD','PVGDONL','PVGDBAT','ORADBA','DBA03');

select 'CHECK OBJ:(index unusable)' || to_char(systimestamp,'YYYY/MM/DD-HH24:MI:SS') || ' ' || owner || '.' || index_name || ' ' || table_name || ' ' || status
from dba_indexes 
where owner IN ('PVGD','TVGD','DBA03') 
and status = 'UNUSABLE' ;

select 'CHECK OBJ:(ind_part unusable)' || to_char(systimestamp,'YYYY/MM/DD-HH24:MI:SS') || ' ' || index_owner || '.' || index_name || ' ' || partition_name || ' ' || status
from dba_ind_partitions 
where index_owner IN ('PVGD','TVGD','DBA03') 
and  status = 'UNUSABLE' ;

select 'CHECK OBJ:(index not analyed)' || to_char(systimestamp,'YYYY/MM/DD-HH24:MI:SS') || ' ' ||
       index_name,table_name,status,num_rows,last_analyzed  
 from dba_indexes
where owner = 'PVGD' and index_type='NORMAL' 
  AND INDEX_NAME NOT LIKE 'QUEST%'
  and last_analyzed is null
order by 2;

select 'CHECK OBJ:(index_part not analyed)' || to_char(systimestamp,'YYYY/MM/DD-HH24:MI:SS') || ' ' || index_name 
  from dba_ind_partitions
 where index_owner='PVGD'
   AND INDEX_NAME NOT LIKE 'QUEST%'
   AND INDEX_NAME NOT IN ('BVIBV110','BVIBV120','BVIBV130','BVIBV140','BVIBV150',
                          'BVIBV160','BVIBV170','BVIBV180','BVIBV190','BVIBV200',
                          'BVIBV510','BVIBV520','BVIBV530','BVIBV540','BVIBV550')
   and last_analyzed is null
   group by index_name;

7. CISSD01

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

COL DBMS_USER_NM    FOR A16
COL HT_NM           FOR A12
COL DBMS_WK_KND_CD  FOR A6
COL DBMS_OWNER_NM   FOR A16
COL OBJT_ID         FOR A22
COL AVL_STR_YMD     FOR A12
COL AVL_END_YMD     FOR A12
COL APL_DTM         FOR A32


SELECT 
HT_NM
,DBMS_OWNER_NM
,DBMS_WK_KND_CD
,OBJT_ID
,DBMS_USER_NM
,AVL_STR_YMD
,AVL_END_YMD
,APL_DTM
FROM TB_CISSD01
WHERE OBJT_ID LIKE UPPER('&1.')||'%'
/

COL DBMS_USER_NM    CLEAR
COL HT_NM           CLEAR
COL DBMS_WK_KND_CD  CLEAR
COL DBMS_OWNER_NM   CLEAR
COL OBJT_ID         CLEAR
COL AVL_STR_YMD     CLEAR
COL AVL_END_YMD     CLEAR
COL APL_DTM         CLEAR

8. CISSD04

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

COL DBMS_OWNER_NM   FOR A16
COL OBJT_ID         FOR A22
COL DBMS_AUTH_NM    FOR A12
COL DBMS_GRNTE_NM   FOR A12
COL DBMS_USER_NM    FOR A16
COL TRT_DTM         FOR A32

SELECT 
DBMS_OWNER_NM
,OBJT_ID
,DBMS_AUTH_NM
,DBMS_GRNTE_NM
,DBMS_USER_NM
,TRT_DTM
FROM TB_CISSD04
WHERE OBJT_ID LIKE UPPER('&1.')||'%'
ORDER BY TRT_DTM
/

COL DBMS_OWNER_NM   CLEAR
COL OBJT_ID         CLEAR
COL DBMS_AUTH_NM    CLEAR
COL DBMS_GRNTE_NM   CLEAR
COL DBMS_USER_NM    CLEAR
COL TRT_DTM         CLEAR

9. CISSD05

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

COL DBMS_OWNER_NM   FOR A12
COL OBJT_ID         FOR A22
COL DBMS_AUTH_NM    FOR A12
COL DBMS_GRNTE_NM   FOR A12
COL DBMS_USER_NM    FOR A12
COL TRT_DTM         FOR A32
COL CHG_DTM         FOR A32
COL DBMS_AUTH_CNTL_SC_CD FOR A2
COL DBMS_AUTH_MSTK_SC_CD FOR A2

SELECT 
DBMS_OWNER_NM
,OBJT_ID
,CHG_DTM
,DBMS_AUTH_NM
,DBMS_GRNTE_NM
,DBMS_USER_NM
,TRT_DTM
,DBMS_AUTH_CNTL_SC_CD CNTL
,DBMS_AUTH_MSTK_SC_CD MSTK
FROM TB_CISSD05
WHERE OBJT_ID LIKE UPPER('&1.')||'%'
AND CHG_DTM > SYSDATE - 7
ORDER BY CHG_DTM
/


COL DBMS_OWNER_NM   CLEAR
COL OBJT_ID         CLEAR
COL DBMS_AUTH_NM    CLEAR
COL DBMS_GRNTE_NM   CLEAR
COL DBMS_USER_NM    CLEAR
COL TRT_DTM         CLEAR
COL CHG_DTM         CLEAR
COL DBMS_AUTH_CNTL_SC_CD CLEAR
COL DBMS_AUTH_MSTK_SC_CD CLEAR

10. coal

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

alter tablespace &ts coalesce;

11. col

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

column "allocated(k)"  for  99999999999
column "client user"   for a8
column "db user"       for a8
column "free byte(k)"  for  99999999999
column "null?"         for a10
column "serial#"       for 999999
column "used(%)"       for 990.00
column "used(k)"       for  99999999999
column ascii_gtxid     for a50
column blank1          for a13
column blocking_others for a12
column bytes           for 999,999
column cmd             for a100
column col             for a30
column column_length   for 999
column column_name     for a25
column column_position for 999
column comments        for a60 truncate
column cost            for a10
column created         for a19
column csid            for a28
column ctr_dt          for a20
column ddl_dt          for a20
column default         for a25 truncate
column defined         for 9,999,999,999
column description     for a30
column dummy           for a1 trunc new_value prom 
column entry_date      for  a15
column event           for a30 trunc
column extents         for 999,999
column file_name       for a30
column fname           for a15     heading 'file name' 
column free            for 999,999
column freesize        for 999,999.9 heading "free size(m)"
column fsize           for 999999  heading 'm bytes' 
column host_name       for a50
column hwmsize         for 9999999999999
column id1             for 9999999
column id2             for 9999999
column in/out          for a6
column index_name      for a20
column kbytes          for 9999999999
column last heading 'last date'       for a10
column last_ddl_time                  for a30
column last_sec    heading 'last sec' for a10
column latchname                      for a40
column lck	heading 'lock'		      for a4
column lock_id1                       for a8
column lock_id2                       for a8
column lock_type                      for a20
column log_user heading 'log_user'    for a15
column machine                        for a20 truncate
column max_ext                        for 9999999
column mode_held                      for a15
column mode_requested                 for a10
column module                         for a14
column name                           for a20
column next     heading 'next date'   for a20;
column next_sec heading 'next sec'    for a10;
column npid                           for 999999999
column object                         for a40
column object_name                    for a25
column object_type                    for a10
column operation                      for a74
column osuser                         for a10     truncate
column owner                          for a15
column p1                             for a20
column p2                             for a20
column p_name                         for a15
column path                           for a20     heading 'path' 
column pgm                            for  a70
column pgm_id                         for  a15
column pid                            for a25
column pr                             for 999999 heading 'phy. reads' 
column prg	heading 'program'	      for a31
column process                        for a10
column program                        for a18 truncate
column pstart                         for a16
column pstop                          for a5
column pw                             for 999999 heading 'phy. writes' 
column referenced_name                for a20
column rows                           for a5
column rssize for 99,999,999,999
column sc  for a40
column schema_user for a10
column script   for a100
column segment_name           for a30
column segment_type    for a20
column ser	heading 'serial#' 	for 99990 just left
column sid        for 9999
column sid_serial for a13
column slot    for 99
column snapshot_site for a40
column sql_text   for a140
column status     for a13
column stu	heading 'status'	for a8
column t_name heading 'tables' for a15;
column table_name      for a22
column tablespace_name for a20
column tblsp for a30     heading 'tablespace name' 
column term    for a20
column terminal   for a10 truncate
column text  for a100
column this_sec    for a8
column totalsize for 999,999.9
column tq           for a5
column ts_name                for a15
column tt_name heading 'tablespace' for a10;
column type         for a15
column uniqueness for a8
column unused for 9,999,999,999
column urec    for 99999999
column usedsize for 999,999.9
column username   for a10     truncate
column usn for 9999999999
column usr 	heading 'user'		for a10
column what        for a48 truncate
column work_desc for  a90
column DB_LINK for a30
column HOST for a30
break on QCSID
break on TABLE_NAME on INDEX_NAME
set verify off

 12. cr_snap

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

exec dbms_workload_repository.CREATE_SNAPSHOT();

13. db_unchain

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

REM 
REM SCCS
REM   File Name     : @(#)db_unchain.sql	1.2
REM   Check-in Date : 30 Mar 1995 @ 16:44:44
REM   SCCS Version  : 1.2
REM   Path          : SCCS/s.db_unchain.sql
REM
REM File:
REM    db_unchain.sql
REM
REM Description
REM  Remove on chained tables
REM
REM Notes:
REM  This assumes that tables and indexes have been analyzes 
REM  and are relatively current.
REM
REM Revision History:
REM ------------------------------------------------
REM   30-Oct-94   C. Choi    -- Created
REM   30-MAR-95   C. Choi (Oracle Corp) --   Change directory of spool file
REM

REM
REM  Reanalyze the tables
@db_analyze
column cmd format a100

set feedback off
set pagesize 0
set termout off
drop table chained_rows;

create table CHAINED_ROWS (
  owner_name    varchar2(30),
  table_name    varchar2(30),
  cluster_name  varchar2(30),
  head_rowid    rowid,
  timestamp     date
);

spool /tmp/db_listchain.sql

Select 'Analyze table ' || owner||'.'|| table_name
|| ' list chained rows into chained_rows;' cmd
from all_tables
where chain_cnt > num_rows * .15
;
spool off
REM @/tmp/db_listchain

REM
REM  Now that all chains have been listed, 
REM  Save data into temp tables.
REM
spool /tmp/db_tempchain.sql
select distinct 'Create table CHN$' || table_name
   || ' as select * from ' || owner_name || '.' || table_name 
   || ' t where exists select 1 from ||  owner_name || '.' || table_name
   || ' o where o.rowid = t.head_rowid);' cmd
from chained_rows;
spool off

REM @/tmp/db_tempchain

14. dba_hist_active_sess

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

SELECT aa.session_id, aa.session_serial#, aa.sample_time, aa.event, aa.xid,
       bb.sql_text, aa.blocking_session, aa.blocking_session_serial#,
       aa.blocking_session_status, bb.sql_id
  FROM (SELECT   *
            FROM dba_hist_active_sess_history
           WHERE sample_time BETWEEN TO_TIMESTAMP ('2008-07-07 08:40:00',
                                                   'YYYY-MM-DD HH24:MI:SS'
                                                  )
                                 AND TO_TIMESTAMP ('2008-07-07 09:40:00',
                                                   'YYYY-MM-DD HH24:MI:SS'
                                                  )
             AND event like '%PX%'
        --      AND session_id in ( 4245, 6495)
        --      AND session_serial# in (16, 1825) 
        ORDER BY sample_time ASC) aa,
       (SELECT *
          FROM dba_hist_sqltext) bb
 WHERE aa.sql_id = bb.sql_id(+)
/


--SELECT aa.module, count(*)
--  FROM (SELECT   *
--            FROM dba_hist_active_sess_history
--           WHERE sample_time BETWEEN TO_TIMESTAMP ('2008-07-08 08:40:00',
--                                                   'YYYY-MM-DD HH24:MI:SS'
--                                                  )
--                                 AND TO_TIMESTAMP ('2008-07-08 09:40:00',
--                                                   'YYYY-MM-DD HH24:MI:SS'
--                                                  )
--             AND event like '%enq%'
--        --      AND session_id in ( 4245, 6495)
--        --      AND session_serial# in (16, 1825) 
--        ORDER BY sample_time ASC) aa,
--       (SELECT *
--          FROM dba_hist_sqltext) bb
-- WHERE aa.sql_id = bb.sql_id(+)
--group by aa.module
--/

15. dba_plant_hist

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

set echo off verify off feedback off timing off head off
set lines 200
col plan_table_output for a130
break on timest nodup
accept SQL_ID      prompt 'Please enter the value for SQL_ID   : '

SELECT to_char(s.timestamp,'YYYY-MM-DD HH24:MI:SS') timest, plan_table_output
  FROM (select * from dba_hist_sql_plan WHERE sql_id = '&&SQL_ID' AND ID = 0) s,
       TABLE (DBMS_XPLAN.display_awr (s.sql_id, s.plan_hash_value, s.dbid, NULL)) t
/
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]