No image

Oracle Monitoring Scripts 4

1. flb_file

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

col name for a60
col kbytes for 999,999,999
alter session set  nls_date_format='yyyy-mm-dd.hh24.mi.ss';

select name,log#,sequence#,first_change#,bytes/1024 kbytes,FIRST_TIME from v$flashback_database_logfile;

alter session set  nls_date_format='yyyy-mm-dd';
col name clear
col kbytes clear

2. flb_old

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

col oldest_flashback_scn for 999999999999999999
col FLASHBACK_SIZE for 999,999,999 head FLASHBACK_SIZE(KB)
col ESTIMATED_FLASHBACK_SIZE for 999,999,999 head ESTIMATED_FLASHBACK_SIZE(KB)

select oldest_flashback_scn 
     , to_char(OLDEST_FLASHBACK_TIME,'yyyy-mm-dd hh24:mi:ss') OLDEST_FLASHBACK_TIME 
     , RETENTION_TARGET
     , FLASHBACK_SIZE/1024 FLASHBACK_SIZE
     , ESTIMATED_FLASHBACK_SIZE/1024 ESTIMATED_FLASHBACK_SIZE
from v$flashback_database_log;

col oldest_flashback_scn clear
col FLASHBACK_SIZE clear
col ESTIMATED_FLASHBACK_SIZE clear

3. flb_rp

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

col TIME for a28
col RESTORE_POINT_TIME for a12
col STORAGE_SIZE for 999,999,999 head STORAGE_SIZE(KB)
col name for a16

select 
SCN
,DATABASE_INCARNATION#
,GUARANTEE_FLASHBACK_DATABASE
,STORAGE_SIZE/1024  STORAGE_SIZE
,TIME
,RESTORE_POINT_TIME
,PRESERVED
,NAME
 from v$restore_point;

col TIME clear
col RESTORE_POINT_TIME clear
col STORAGE_SIZE clear
col name clear

4. flb_stat

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

col FLASHBACK_DATA for 999,999,999 head FLASHBACK_DATA(KB)
col DB_DATA for 999,999,999,999 head DB_DATA(KB)
col REDO_DATA for 999,999,999 head REDO_DATA(KB)
col ESTIMATED_FLASHBACK_SIZE for 999,999,999 head ESTIMATED_FLASHBACK_SIZE(KB)

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

select 
 BEGIN_TIME
, END_TIME
, FLASHBACK_DATA/1024 FLASHBACK_DATA
, DB_DATA/1024 DB_DATA
, REDO_DATA/1024 REDO_DATA
, ESTIMATED_FLASHBACK_SIZE/1024 ESTIMATED_FLASHBACK_SIZE
from v$flashback_database_stat;

alter session set nls_date_format='yyyy-mm-dd';

col FLASHBACK_DATA clear
col DB_DATA clear
col REDO_DATA clear
col ESTIMATED_FLASHBACK_SIZE clear

5. flb_use

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

col name for a40
col SPACE_USED for 999,999,999 head SPACE_USED(KB)
col SPACE_LIMIT for 999,999,999 head SPACE_LIMIT(KB)

select * from V$FLASH_RECOVERY_AREA_USAGE;

select 
NAME
,SPACE_LIMIT/1024 SPACE_LIMIT
,SPACE_USED/1024 SPACE_USED
,SPACE_RECLAIMABLE
,NUMBER_OF_FILES
 from V$RECOVERY_FILE_DEST;

col name clear
col SPACE_USED clear
col SPACE_LIMIT clear

6. frext

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

set verify off
col KBytes   for 9999999999

SELECT /*+ rule */TABLESPACE_NAME, to_char(FILE_ID) file_id, ROUND(BYTES/1024) "KBytes"
FROM   dba_free_space
WHERE  tablespace_name LIKE upper('&1')||'%'
AND    bytes >= 4*1024*1024
--union all
--SELECT /*+ rule */TABLESPACE_NAME, 'Total_sum', ROUND(sum(BYTES)/1024) "KBytes"
--FROM   dba_data_files
--WHERE  tablespace_name LIKE upper('&1')||'%'
--group by TABLESPACE_NAME
ORDER BY 1, 3 DESC
/

col KBytes   clear

set verify on

7. get_seg

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

SELECT segment_name FROM sys.dba_extents WHERE file_id = &1 AND &2 between block_id and (block_id + (blocks - 1))
/

8. gplan

-- writer : ezis.cloud (noname)
-- Description  : gather_plan_statistics (statistics_level=all)
-- Requirements : 

set    echo off verify off serveroutput off

set    termout off
def    src_f=src.sql
alter  session set statistics_level               =all   ;
--alter  session set optimizer_use_invisible_indexes=false ;
alter  session set optimizer_use_invisible_indexes=true  ;
alter  session set timed_os_statistics            =0  ;
def    format='advanced allstats last'
set    termout on

start  &src_f.

set    head off
select * from table(dbms_xplan.display_cursor(null,null,'&format'));
set    head on

set    termout off
truncate table rslt_v1 ;
alter  session set statistics_level              =typical;
alter  session set optimizer_use_invisible_indexes=false ;
alter  session set timed_os_statistics            =0  ;
set    termout on

set    verify on

9. hex2dec

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

set verify off
select '&1 => '||to_number('&1','XXXXXXXXXXXXXXXXXX') hex2dec from dual;
set verify on

10. hidden

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

col NAME           for  a50
col DESCRIPTION    for  a63 truncate
col SESSION_VALUE  for  a15
col SYSTEM_VALUE   for  a15

select a.ksppinm NAME,
a.ksppdesc DESCRIPTION,
b.ksppstvl SESSION_VALUE,
c.ksppstvl SYSTEM_VALUE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and lower(ksppinm) like '%' || lower('&1') || '%'
;

col NAME           clear
col DESCRIPTION    clear
col SESSION_VALUE  clear
col SYSTEM_VALUE   clear

11. idx

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

set    verify off

COL    TABLE_NAME      FORMAT A29
COL    INDEX_NAME      FORMAT A27
COL    COLUMN_NAME     FORMAT A20
COL    COLUMN_POSITION FORMAT 999 HEADING "POS"
COL    COLUMN_LENGTH   FORMAT 999 HEADING "LEN"
COL    UNIQUENESS      FORMAT A9  HEADING "UNIQUE"
COL    STATUS          FORMAT A8
COL    BLVL            FORMAT 9999
COL    PCT_FREE        FOR    999 HEADING "PCT"
BREAK  ON TABLE_NAME ON INDEX_NAME

@arg1

set    termout off
var    v_owner          varchar2(20);
var    v_index_name     varchar2(30);
exec   :v_owner         := upper(NVL(SUBSTR('&&1', 1,INSTRB('&&1','.')-1), '&DFT_OWNER.'));
exec   :v_index_name    := upper( SUBSTR('&&1', INSTRB('&&1','.')+1));
set    termout on

SELECT
       a.table_owner||'.'||A.TABLE_NAME TABLE_NAME
     , A.INDEX_NAME
     , SUBSTR(A.COLUMN_NAME,1,20) COLUMN_NAME
     , A.COLUMN_POSITION
     , A.COLUMN_LENGTH
     , B.UNIQUENESS
     , B.STATUS
     ,(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 blvl
     , LEAF_BLOCKS leafcnt
     , DISTINCT_KEYS distkey
     --,AVG_LEAF_BLOCKS_PER_KEY
     --,AVG_DATA_BLOCKS_PER_KEY
     , CLUSTERING_FACTOR clufac
FROM  (
       select * from dba_IND_COLUMNS
       where TABLE_OWNER = :v_owner
       and index_name = :v_index_name
      ) A,
      (
       select * from dba_INDEXES
       where TABLE_OWNER = :v_owner
       and index_name = :v_index_name
      ) B
WHERE  A.INDEX_NAME  = B.INDEX_NAME
ORDER  BY 1, 2, 4;

COL    TABLE_NAME      clear
COL    INDEX_NAME      clear
COL    COLUMN_NAME     clear
COL    COLUMN_POSITION clear
COL    COLUMN_LENGTH   clear
COL    UNIQUENESS      clear
COL    STATUS          clear
COL    BLVL            clear
COL    PCT_FREE        clear

set    verify on

12. idx_info

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

column index_name format a15
column tablespace_name format a10
column col format a30
column uniqueness format a8
column bytes format 999,999
		
select /*+ RULE */ index_name,
       max(decode(column_position,'1','('||column_name,'')) ||
	   max(decode(column_position,'2',','||column_name,'')) ||
	   max(decode(column_position,'3',','||column_name,'')) ||
	   max(decode(column_position,'4',','||column_name,'')) ||
	   max(decode(column_position,'5',','||column_name,'')) ||
	   max(decode(column_position,'6',','||column_name,'')) ||
	   max(decode(column_position,'7',','||column_name,'')) ||
	   max(decode(column_position,'8',','||column_name,'')) ||
	   max(decode(column_position,'9',','||column_name,'')) ||
	   max(decode(column_position,'10',','||column_name,'')) ||
	   max(decode(column_position,'11',','||column_name||')',')')) col,
	   tablespace_name,decode(uniqueness,'UNIQUE','UNIQUE','NON/U') uniqueness,bytes
  from (  select a.index_name, column_name,column_position,b.tablespace_name,b.uniqueness,c.bytes/1024/1024 bytes
		from user_Ind_columns a, user_indexes b, user_segments c
		where a.index_name = b.index_name and b.index_name = c.segment_name and b.table_name ='&TABLE_NAME')
group by index_name,tablespace_name,uniqueness,bytes;

13. in_jobs

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

INSERT INTO jobs
SELECT TO_CHAR(SYSDATE,'yyyymmdd') wrk_dt, JOB, LAST_DATE, NEXT_DATE, TOTAL_TIME, WHAT
FROM dba_jobs;
commit;

14. ins

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

SET TIMING OFF VERIFY OFF

COL DUMMY         FOR A1 TRUNC NEW_VALUE DUMMY NOPRINT
COL INSTANCE_NAME FOR A15
COL USER          FOR A12
COL HOST_NAME     FOR A30
COL PROCESS       FOR A12
COL SPID          FOR A12

SELECT INSTANCE_NAME
     , HOST_NAME
     , USER
     , '['||INSTANCE_NAME||'-&_CONNECT_IDENTIFIER.]' DUMMY
     , S.SID
     , S.PROCESS
     , P.SPID
FROM   V$INSTANCE
      ,V$SESSION S
      ,V$PROCESS P
WHERE  S.AUDSID = USERENV('SESSIONID')
AND    S.SID    = USERENV('SID')
AND    S.PADDR  = P.ADDR
/

--SET SQLPROMPT '&&DUMMY SQL> '
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

COL DUMMY         CLEAR
COL INSTANCE_NAME CLEAR
COL USER          CLEAR
COL HOST_NAME     CLEAR
COL PROCESS       CLEAR
COL SPID          CLEAR
UNDEFINE DUMMY

SET TIMING ON VERIFY ON

 

15. invalid

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

SELECT owner,index_name, '' partition_name
FROM dba_indexes
WHERE status NOT IN ( 'VALID','N/A' )
UNION ALL 
SELECT INDEX_OWNER,index_name, partition_name
FROM dba_ind_partitions
WHERE status NOT LIKE 'US%'
;

16. job

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

column last     heading 'LAST DATE'    format a20;
column next     heading 'NEXT DATE'    format a20;
column log_user heading 'LOG_USER'     format a15;
column what     heading 'NAME'         format a49 truncate

SELECT j.job, j.log_user, 
       TO_CHAR(j.last_date,'yy-mm-dd ')||j.last_sec last,
       TO_CHAR(j.next_date,'yy-mm-dd ')||j.next_sec next,
       j.broken,j.failures,
       j.what what
FROM dba_jobs j, dba_refresh r
where j.job = r.job (+)
ORDER BY 7
--ORDER BY next_date,what
/

column last     clear
column next     clear
column what     clear
column log_user clear

17. k_usr

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

col sc  for a40
col machine  for a40
SELECT 'alter system kill session '''||sid||','||SERIAL#||''';' SC,MACHINE, USERNAME, status
FROM   v$session WHERE USERNAME LIKE 'O%'
AND    USERNAME <> 'ONADMIN'
AND    MODULE = 'T.O.A.D.'
order by USERNAME
/

18. kill

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

set verify off
accept sid prompt 'Please enter sid,serial# ( ex : 111,12 ) : '

ALTER SYSTEM KILL SESSION '&sid.' immediate
/
set verify on

 

19. lc_sql

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

SELECT a.sid, a.username, a.PROGRAM,
      (select sql_text from v$sqlarea where address = a.sql_address and hash_value = a.sql_hash_value) sql_text
FROM   v$session a, v$process b
WHERE  a.paddr = b.addr
AND   (b.background IS NULL OR b.background != '1')
AND    a.sid IN (SELECT sid
                 FROM   x$kglob x, v$session_wait w
                 WHERE  KGLHDADR=P1RAW AND event LIKE '%library%')
/

20. ldl

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

col object_name for a20
col object_type for a10
col ddl_dt for a20
col ctr_dt for a20
select object_name,object_type,to_char(last_ddl_time,'yy-mm-dd-hh24-mi') ddl_dt,to_char(created,'yy-mm-dd-hh24-mi') crt_dt  from dba_objects 
where owner='ONADMIN' and to_char(last_ddl_time,'yyyymmdd') = to_char(sysdate - 1,'yyyymmdd') order by object_type, ddl_dt desc;

 

21. ldmp

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

prompt dump library_cache

alter session set tracefile_identifier='lib_cache_&1';
oradebug setmypid
oradebug dump library_cache 10

22. link

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

col name for a20
col host  for a50 truncate
SELECT NAME, USERID, PASSWORD, HOST--, AUTHUSR
FROM sys.LINK$
order by name;
col name clear
col host  clear

23. list_event

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

set serveroutput on

    DECLARE
        l_level NUMBER;
    BEGIN
        FOR l_event IN 10000..10999
        LOOP
            sys.dbms_system.read_ev (l_event,l_level);
            IF l_level > 0 THEN
                dbms_output.put_line ('Event '||TO_CHAR (l_event)||
                ' is set at level '||TO_CHAR (l_level));
            END IF;
        END LOOP;
    END;
    /

set serveroutput off
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]