No image

Oracle Monitoring Scripts 6

1. new

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

select  /*+  DLTBDC14S1004 index(DLTBDC14 DLIDC142)  +*/ DC14_SYS_ORG_C ,DC14_RCMD_NO 
,DC14_CNO ,DC14_ACCNO ,DC14_PD_C ,DC14_CDNO ,DC14_RRNO ,
TO_CHAR(DC14_COL_TF_DT,'YYYY-MM-DD') ,DC14_APL_AM ,DC14_PC_AM ,
TO_CHAR(DC14_PC_DT,'YYYY-MM-DD') ,DC14_RVO_DC ,DC14_RVO_IT_R ,DC14_RVO_BIL_R ,
DC14_RVO_PB_DC ,DC14_GURP_T ,DC14_BL_LIM_RTYN ,DC14_PCP_BRC ,DC14_PENO ,
TO_CHAR(DC14_LT_CH_DTTI,'YYYY-MM-DD-HH24.MI.SS.FF6') ,DC14_LT_CH_USID ,
TO_CHAR(DC14_CAN_DT,'YYYY-MM-DD') ,DC14_CAP_ENO 
into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,
:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22  
from DLTBDC14 where ((DC14_SYS_ORG_C=:b23 and DC14_RRNO=:b24) 
and DC14_COL_TF_DT<=TO_DATE(:b25,'YYYY-MM-DD')) 
order by DC14_SYS_ORG_C asc ,DC14_RRNO asc ,DC14_COL_TF_DT desc 
/

2. noindex

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

# DOC

Name : noindex.sql
인덱스가 존재하지 않은 테이블에 대한 정보를 보여준다.

ttitle 'Report on all Tables Without Indexes'

column owner format a15 heading 'User'
break on owner skip 1

select owner, table_name
from all_tables
where owner not in ('SYS', 'SYSTEM', 'APTEST', 'MIGADMIN', 'ORACLE8')
  and table_name not like '%PLAN_TABLE%'
 MINUS
select owner, table_name
from all_indexes
where owner not in ('SYS', 'SYSTEM', 'APTEST', 'MIGADMIN', 'ORACLE8')
  and table_name not like '%PLAN_TABLE%'
/

clear columns
clear breaks
ttitle off

3. noparallel

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

col stmt   for a80

SELECT 'alter index '||owner||'.'||index_name||' noparallel;' stmt
FROM dba_indexes a
WHERE 1=1
AND trim(DEGREE) not in ('1','0','DEFAULT')
UNION ALL
SELECT 'alter table '||owner||'.'||table_name||' noparallel;' stmt
FROM dba_tables a
WHERE 1=1
AND trim(DEGREE) not in ('1','0','DEFAULT')
/

col stmt   clear

4. nopq

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

col stmt   for a80

SELECT 'alter index '||owner||'.'||index_name||' noparallel;' stmt
FROM dba_indexes a
WHERE owner = 'PMVP'
AND TO_NUMBER(DEGREE) > 1
UNION ALL
SELECT 'alter table '||owner||'.'||table_name||' noparallel;' stmt
FROM dba_tables a
WHERE owner = 'PMVP'
AND TO_NUMBER(DEGREE) > 1
/

col stmt   clear

5. ol1

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

set echo off
conn /as sysdba
accept SQL_ID      prompt 'Please enter the value for SQL_ID if known     : '
set termout off
set long 9999 lines 1000 pages 0 timing off feedback off verify off serveroutput on trimspool on
col sql_fulltext for a1000
spool org.sql

select sql_fulltext
from v$sql a 
where sql_id = '&SQL_ID.'
and rownum = 1
;

prompt /
spool off
set termout on

get org.sql

--accept OL_NAME      prompt 'Please enter the value for OUTLINE NAME if known     : '

set termout off
--declare
--  v_sql varchar2(4000);
--begin
--  select sql_fulltext
--  into v_sql
--  from v$sql a 
--  where sql_id = '&SQL_ID.'
--  and rownum = 1
--  ;
--
--  v_sql := 'create or replace public outline &OL_NAME for category PVGD_ONLINE on ' ||
--           v_sql;
--  execute immediate v_sql;
--
--  DBMS_OUTPUT.put_line (v_sql);
--end;
--/

col sql_fulltext clear
set lines 140 pages 9999 timing on feedback on verify on serveroutput off
set termout on
get org.sql nolist

6. ol2

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

set echo off verify off 

save new.sql repl
get new.sql
prompt
accept OUTLN_NAME      prompt 'Please enter the value for outline name : '
set termout off
1
ch //create or replace public outline &OUTLN_NAME._NEW for category PVGD_ONLINE on /
/
get new.sql nolist

conn /as sysdba

declare
  v_sql varchar2(4000);
begin
  select sql_fulltext
  into v_sql
  from v$sql a
  where sql_id = '&&SQL_ID.'
  and rownum = 1
  ;

  v_sql := 'create or replace public outline &&OUTLN_NAME for category PVGD_ONLINE on ' ||
           v_sql;
  execute immediate v_sql;

  DBMS_OUTPUT.put_line (v_sql);
end;
/

UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'&OUTLN_NAME.','&OUTLN_NAME._NEW','&OUTLN_NAME._NEW','&OUTLN_NAME.')
WHERE OL_NAME IN ('&OUTLN_NAME._NEW','&OUTLN_NAME.');

drop outline &OUTLN_NAME._NEW;
commit;

set echo off verify on termout on

7. p

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

col col1   for a25 heading "Session info1" 
col col2   for a50 heading "Session info2"
col sql_text   for a70 wrap

set verify off long 400


select            'sid      : '           ||b.sid||','||b.SERIAL#
       ||chr(10)||'status   : '        ||b.status
       ||chr(10)||'username : '      ||b.username
       ||chr(10)||'process  : ' ||b.process
       ||chr(10)||'spid     : ' ||p.spid
       ||chr(10)||'osuser   : '        ||b.osuser
       ||chr(10)||'sql_id   : ' ||sql_id
       col1,
                  'terminal : ' ||b.terminal
       ||chr(10)||'module : ' ||b.module
       ||chr(10)||'program : ' ||b.program
       ||chr(10)||'LOGON_TIME : ' ||to_char(b.LOGON_TIME,'mmdd hh24:mi:ss')
       ||chr(10)||'physical_reads : '||si.physical_reads 
       ||chr(10)||'block          : '||si.block_gets 
       ||chr(10)||'consistent     : '||si.consistent_gets 
       ||chr(10)||'event : ' ||w.event
       ||chr(10)||'session logical reads : ' ||st.value
       ||chr(10)||'command : ' ||decode(b.command,
              0,'n/a', 1,'create cwtable', 2,'insert', 3,'select', 4,'create cluster', 5,'alter cluster', 6,'update',
              7,'delete', 8,'drop cluster', 9,'create index', 10,'drop index', 11,'alter index', 12,'drop table',
              13,'create sequence', 14,'alter sequence', 15,'alter table', 16,'drop sequence', 17,'grant',
              18,'revoke', 19,'create synonym', 20,'drop synonym', 21,'create view', 22,'drop view', 23,'validate index',
              24,'create procedure', 25,'alter procedure', 26,'lock table', 27,'no operation', 28,'rename',
              29,'comment', 30,'audit', 31,'noaudit', 32,'create database link', 33,'drop database link',
              34,'create database', 35,'alter database', 36,'create rollback segment', 37,'alter rollback segment',
              38,'drop rollback segment', 39,'create tablespace', 40,'alter tablespace', 41,'drop tablespace',
              42,'alter session', 43,'alter user', 44,'commit', 45,'rollback', 46,'savepoint', 47,'pl/sql execute',
              48,'set transaction', 49,'alter system switchlog', 50,'explain', 51,'create user', 52,'create role',
              53,'drop user', 54,'set role', 55,'set role', 56,'create schema', 57,'create control file',
              58,'alter tracing', 59,'create trigger', 60,'alter trigger', 61,'drop trigger',
              62,'analyze table', 63,'analyze index', 64,'analyze cluster', 65,'create profile', 66,'drop profile',
              67,'alter profile', 68,'drop procedure', 69,'drop procedure', 70,'alter resource cost', 71,'create snapshot log', 
              72,'alter snapshot log', 73,'drop snapshot log', 74,'create snapshot', 75,'alter snapshot', 76,'drop snapshot',
              79,'alter role', 85,'truncate table', 86,'truncate cluster', 88,'alter view', 91,'create function',
              92,'alter function', 93,'drop function', 94,'create package', 95,'alter package',
              96,'drop package', 97,'create package body', 98,'alter package body', 99,'frop package body', 'UNKNOWN')
       col2,
      (select sql_fulltext from v$sqlarea where address = b.sql_address and hash_value = b.sql_hash_value) sql_text
from v$session b, v$session_wait w, v$sess_io si, v$process p, v$sesstat st
where b.sid=(select s.sid from v$session s where s.process='&1')
and b.paddr=p.addr (+)
and b.sid = w.sid
and b.sid = si.sid
and b.sid = st.sid (+)
and st.STATISTIC# (+) = 9
/

set verify on
col col1   clear
col col2   clear
col sql_text   clear

8. par

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

# Oracle Parameter

select name, value from v$parameter
where name = 'shared_pool_size'
  or  name = 'db_block_buffers'
  or  name = 'db_block_size'
  or  name = 'log_buffer'
  or  name = 'sort_area_size'
  or  name = 'sort_area_retained_size'
  or  name = 'lm_locks'
  or  name = 'lm_ress'
order by name;

9. pgm

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

col pgm       for  a70
col WORK_DESC for  a90
col PGM_ID    for  a15
col ENTRY_DATE    for  a15
set verify off timi off feedback off
select lower(PGM_ID)||'('||PGM_NM||')-'||
      (select WRKR_NM from OAWRKMAN where WRKR_ID = OAPGMRSC.WRKR_ID) pgm
from   OAPGMRSC where PGM_ID = upper('&&1');

select /*+ index_desc ( a onsvctim_idx0 ) */ SERVICE_ID,WRK_DT,CALL_CNT,AVG_TIME
from onsvctim a
where SERVICE_ID = upper('&&1')
and rownum <= 20;

select PGM_ID,NEW_GB, WORK_DESC, UPLOAD_DATE, to_char(ENTRY_DATE,'yyyymmdd hh24miss') ENTRY_DATE
from OAMTPGMH
where PGM_ID = upper('&&1')
order by PGM_ID,UPLOAD_DATE;

col pgm       cle
col WORK_DESC cle
col PGM_ID    cle
col ENTRY_DATE    cle
set verify on timi on feedback on

10. pid

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

set verify off
set feedback off
set echo off

column sid	heading 'SID'		format 9990
column ser	heading 'Serial#' 	format 99990 just left
column pid	heading 'PID'		format a6
column usr 	heading 'User'		format a10
column prg	heading 'Program'	format a31
column stu	heading 'Status'	format a8
column lck	heading 'Lock'		format a4

select 	s.sid		sid,
	s.serial#	ser,
	p.spid		pid,
	s.username	usr,
	s.program	prg,
	s.status	stu,
	decode(s.lockwait, null, '', 'WAIT') lck
from 	v$session s, v$process p
where	s.paddr = p.addr
and	type <> 'BACKGROUND'
and     p.spid = &pid
/

11. pid

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

set verify off
set feedback off
set echo off

column sid	heading 'SID'		format 9990
column ser	heading 'Serial#' 	format 99990 just left
column pid	heading 'PID'		format a6
column usr 	heading 'User'		format a10
column prg	heading 'Program'	format a31
column stu	heading 'Status'	format a8
column lck	heading 'Lock'		format a4

select 	s.sid		sid,
	s.serial#	ser,
	p.spid		pid,
	s.username	usr,
	s.program	prg,
	s.status	stu,
	decode(s.lockwait, null, '', 'WAIT') lck
from 	v$session s, v$process p
where	s.paddr = p.addr
and	type <> 'BACKGROUND'
and     p.spid = &pid
/

12. plan

-- writer : ezis.cloud (noname)
-- Description  :  execute explain plan ( list sql )
-- Requirements : 

set    echo off timi off feedback off verify off termout off

define statement_id = dba03
define plan_table   = plan_table
save   .toto.sql repl

set    termout off
col    v_format       new_value v_format
select
       case when version  >= 10 then 'advanced allstats -projection -outline -alias'
--       case when version  >= 10 then 'advanced allstats -outline'
            else                     'all'
       end as v_format
from  (select to_number(substr(version,1,instr(version,'.',1,2)-1)) as version from v$instance)
;
col    v_format       clear

--select '&_user'||'_'||sys_context('userenv','sid') dummy from dual;
--select '&_user'||'_'||(select sid from v$mystat where rownum <=1) dummy from dual;
delete from &plan_table where statement_id='&statement_id';
commit ;
get   .toto.sql nolist
1
ch     //explain plan set statement_id = '&statement_id' into &plan_table for /
/

set    termout on head off

select * from table(dbms_xplan.display(null,'&statement_id','&v_format'));

prompt

get   .toto.sql nolist

set    timi on feedback on verify on termout on head on
undef  statement_id
undef  plan_table

13. pmgt

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

conn system/[email protected] 

14. pq_stat

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

set feedback off timi off

prompt
prompt < Current Stat Infomation >
prompt sys : v$sysstat ( value )
prompt ses : v$sesstat ( sum(value) )

col sys_ses  head SYS-SES
col USERNAME  for  a12
col PROGRAM   for  a26 trunc

select name
, sum(case gbn when 'sys' then value end) sys
, nvl(sum(case gbn when 'ses' then value end),0) ses
, nvl(sum(case gbn when 'sys' then value end),0)
- nvl(sum(case gbn when 'ses' then value end),0) sys_ses
from (
select 'sys' gbn,statistic#, name , value
from v$sysstat a
where statistic# between 319 and 323
and value > 0
union all
select 'ses' gbn,st.statistic#,max(sn.name),nvl(sum(st.value),0)
from v$sesstat st,v$statname sn
where st.statistic# between 319 and 323
and st.statistic# = sn.statistic#
and st.value > 0
group by st.statistic#
)
group by name 
;

prompt
prompt < v$sesstat Infomation >

select ss.type,ss.username, sum(value) value, max(program) program
from v$sesstat st,v$statname sn, v$session ss
where st.statistic# between 319 and 323
and st.statistic# = sn.statistic#
and st.sid = ss.sid
and value > 0
group by ss.type,ss.username
;

prompt
prompt < v$sesstat Infomation ( not BACKGROUND not 'SYS','ERADM' ) >

select ss.sid, serial#, ss.username, value, program, sql_id,status
from v$sesstat st,v$statname sn, v$session ss
where st.statistic# between 319 and 323
and st.statistic# = sn.statistic#
and st.sid = ss.sid
and value > 0
and type <> 'BACKGROUND'
and username not in ( 'SYS','ERADM' )
;

prompt
prompt < Awr Stat Infomation >

select stat_name,to_char(ss.begin_interval_time,'yyyy-mm-dd hh24:mi') begin_interval_time, value
from dba_hist_sysstat sh, dba_hist_snapshot ss
where ss.dbid = sh.dbid
and ss.instance_number = sh.instance_number
and ss.snap_id = sh.snap_id
and ss.begin_interval_time
between sysdate - 4/24 and sysdate
and stat_name like 'Parallel operations downgraded%'
and sh.instance_number = ( select instance_number from v$instance )
and value > 0
order by sh.stat_name,ss.snap_id
;

col USERNAME  clear
col PROGRAM   clear
col sys_ses   clear
set feedback on timi on

15. prc

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

col program    for a30
col username   for a20
select s.sid, s.serial#,s.process,p.spid,s.program, s.username,s.terminal
from   v$session s,v$process p
where  s.paddr=p.addr
and    s.process in ('&1','&2')
/

16. prc_sql

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

select b.osuser,b.process,b.terminal,
      (select sql_text from v$sqlarea where address = b.sql_address and hash_value = b.sql_hash_value) sql_text
from   v$session b
where  b.process='&SID'
/

17. print_table

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

set serveroutput on verify off

Accept p_sql prompt "Execute SQL : "
prompt 

declare
l_theCursor     integer default dbms_sql.open_cursor;
l_columnValue   varchar2(4000);
l_status        integer;
l_descTbl       dbms_sql.desc_tab;
l_colCnt        number;
l_cs            varchar2(255);
l_date_fmt      varchar2(255);
p_query         varchar2(4000);
p_date_fmt      varchar2(30) default 'yyyy-mm-dd.hh24.mi.ss';
p_timestamp_fmt varchar2(30) default 'yyyy-mm-dd.hh24.mi.ss.ff6';

-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them

procedure restore is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate 'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate 'alter session set nls_date_format='''|| l_date_fmt ||'''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;

begin
-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.

execute immediate 'alter session set nls_date_format=''' || p_date_fmt || '''';
execute immediate 'alter session set nls_timestamp_format=''' || p_timestamp_fmt || '''';

p_query := '&p_sql';
p_query := replace(TRIM(p_query),';','');

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;

-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);

-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
dbms_output.put_line( '---------------------------------' );
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.column_value( l_theCursor, i, l_columnValue );
dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )||': '||substr( l_columnValue, 1, 4000 ) );
end if;
end loop;
dbms_output.put_line( '---------------------------------' );
end loop;
exception
when others then
restore;
raise;
end;
/

set verify on

18. privs

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

col    GRANTEE    for a15
col    PRIVILEGE  for a45
col    ROLES      for a20

set    verify off
SELECT GRANTEE, privilege, admin_option, DECODE(grantee, 'PUBLIC', 'PUBLIC', ' ') roles
FROM   SYS.dba_sys_privs
WHERE  GRANTEE IN (upper('&&1'), 'PUBLIC')
UNION
SELECT dba_role_privs.GRANTEE,dba_sys_privs.privilege, dba_sys_privs.admin_option, dba_role_privs.granted_role
FROM   SYS.dba_sys_privs , SYS.dba_role_privs 
WHERE  dba_sys_privs.grantee=dba_role_privs.granted_role
AND    dba_role_privs.GRANTEE IN (upper('&&1'), 'PUBLIC')
ORDER  BY 1,2
/
set    verify on

col    GRANTEE    clear
col    PRIVILEGE  clear
col    ROLES      clear

19. product

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

column   version ON format   a12
column   product ON format   a35

select product, version  
from product_component_version ;

20. ptbl

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

SELECT owner, table_name
FROM dba_tables t
WHERE owner LIKE '%ADMIN'
AND PARTITIONED <> 'NO'
AND EXISTS (
    SELECT 1
    FROM dba_part_tables
    WHERE owner = t.owner
    AND table_name = t.table_name
    AND PARTITIONING_TYPE = 'RANGE')
AND NOT EXISTS (
    SELECT 1
    FROM dba_tab_partitions
    WHERE table_owner = t.owner
    AND table_name = t.table_name
    AND partition_name LIKE '%200508%') ;

21. ptpm

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

conn system/[email protected]

22. purge_sql

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

accept _sql_id prompt 'Please enter sql_id : '
select 'exec sys.dbms_shared_pool.purge ('''||address||','||hash_value||''',''C'');' 
stmt from v$sql where sql_id = '&_sql_id'
/
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]