No image

Oracle Monitoring Scripts 3

1. dblink

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

col Ascii_GTXID format a50
col Machine format a40
set linesize 200
set pagesize 100
select /*+ ORDERED */
	s.indx "SID",
	g.K2GTITID_ORA "Ascii_GTXID",
	s.ksusemnm "Machine",
	decode(bitand(ksuseidl,11),
			1, 'ACTIVE',
			0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
          		2,'SNIPED',
          		3,'SNIPED', 'KILLED') "STATUS"
from  x$k2gte g, x$ktcxb t, x$ksuse s
where	g.K2GTDXCB =t.ktcxbxba
and	g.K2GTDSES=t.ktcxbses
and	s.addr=g.K2GTDSES;

2. ddl

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

set verify off feedback off timi off head off pages 0

col DATA_TYPE    for a30
col dummy for a1 trunc new_value usr noprint 
col dummy1 for a1 trunc new_value obj noprint 

set verify off
accept usr prompt 'Please enter schema name ( default : PVGD ) : '
accept obj prompt 'Please enter object name ( ex : CMTBKS01  ) : '

select
 nvl(upper('&usr'),'PVGD') dummy
,upper('&obj') dummy1
from dual;

spool &usr._&obj..sql
prompt set echo on
select * from table(smid.ddl_create_tab.GET_TAB    ('&usr','&obj')) where '&obj' like '__TB____';
select * from table(smid.DDL_CREATE_INDEX.GET_INDEX('&usr','&obj')) where '&obj' like '__I_____';
select * from table(smid.DDL_CREATE_VIEW.GET_VIEW('&usr','&obj')) where '&obj' like '__VW____';
prompt set echo off
spool off
set verify on feedback on timi on head on pages 9999

3. ddl_i

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

col value1    for a80
select * from table(pkg_cr_ddl.get_index_ddl('PVGD',upper('&1'),'TS_MVPDB_INDX_011',''))
;
col value1    clear

4. de

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

col COLUMN_NAME  for a25
col type         for a16
col "null?"      for a10
col created      for a19
col object_name  for a25
col DEFAULT      for a25 truncate
col comments     for a50 truncate
col owner        for a15
col grantee      for a18
col grantor      for a15
col table_name   for a15
col PRIVILEGE    for a15
set feedback off verify off echo off timi off termout on

var v_owner           varchar2(20);
var v_table_name      varchar2(30);
exec :v_owner         := upper(NVL(SUBSTR('&&1', 1,INSTRB('&&1','.')-1), 'PVGD'));
exec :v_table_name    := upper( SUBSTR('&&1', INSTRB('&&1','.')+1));

set head off

select 'Object_name : '||o.owner||'.'||o.object_name||chr(10)|| 
       'Created     : '||to_char(o.created, 'YYYY/MM/DD HH24:MI:SS')||chr(10)||
       'PCTFREE     : '||t.PCT_FREE||chr(10)||
       'PCTUSED     : '||t.PCT_USED||chr(10)||
       'INITRANS    : '||t.INI_TRANS||chr(10)||
       'IOT_TYPE    : '||t.IOT_TYPE||chr(10)||
       'degree      : '||ltrim(t.degree,' ')||chr(10)||
       'Logging     : '||logging||chr(10)||
       'num_rows    : '||num_rows||chr(10)||
       'Comments    : '||c.comments
from   dba_objects o,
       dba_tables t,
       dba_tab_comments c--, dba_segments s
where  c.owner      = :v_owner
and    c.table_name = :v_table_name
and    o.owner      = :v_owner
and    o.object_name = :v_table_name
and    o.object_type = 'TABLE'
--and    s.owner      = :v_owner
--and    s.segment_name = :v_table_name
--and    s.segment_type = 'TABLE'
and    t.owner      = :v_owner
and    t.table_name = :v_table_name
/


--set pause 'please enter ... '
--set pause on
set head on

select
       a.COLUMN_NAME,
       a.DATA_TYPE||'('||DECODE( data_type, 'NUMBER', data_precision||decode(data_scale,0,null,','||data_scale), data_length )||')' "TYPE",
       --DATA_PRECISION,
       decode(a.NULLABLE,'N','NOT NULL') "NULL?",
       a.DATA_DEFAULT "DEFAULT",
       --(select comments from dba_col_comments where table_name = a.table_name and column_name = a.COLUMN_NAME) "COMMENTS"
       b.comments "COMMENTS"
from   dba_tab_columns a,
       dba_col_comments b
where  a.OWNER = b.OWNER
and    a.table_name = b.table_name
and    a.column_name = b.column_name
and    a.owner = :v_owner
and    a.table_name = :v_table_name
order by a.OWNER, a.TABLE_NAME, a.COLUMN_ID
/

set pause off

select
       *
from   dba_tab_privs a
where  a.owner = :v_owner
and    a.table_name = :v_table_name
/

prompt

set feedback on verify on echo off timi on
col COLUMN_NAME  clear
col type         clear
col "null?"      clear
col created      clear
col object_name  clear
col DEFAULT      clear
col comments     clear
col owner        clear
col grantee      clear
col grantor      clear
col table_name   clear
col PRIVILEGE    clear

5. dec2hex

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

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

6. degree

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

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

7. depen

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

col REFERENCED_NAME for a20
col sql for a70
col SNAPSHOT_SITE for a40
col owner for a20

set verify off
prompt dba_dependencies         ------------------------------------------>>

SELECT /*+ ALL_ROWS */
       distinct owner,referenced_name,
      (SELECT status FROM dba_objects WHERE owner = a.owner AND object_name = a.name AND object_type = a.TYPE) status,
       'ALTER '||DECODE(TYPE, 'PACKAGE BODY','PACKAGE',TYPE)||' '||OWNER||'.'||NAME||
       DECODE(TYPE, 'PACKAGE', ' COMPILE PACKAGE;', 'PACKAGE BODY', ' COMPILE BODY;', ' COMPILE;') SQL
FROM   dba_dependencies a
WHERE  referenced_name IN UPPER('&&1')
and    type <> 'UNDEFINED'
/

prompt
prompt
prompt dba_registered_snapshots ------------------------------------------>>
select OWNER,NAME,SNAPSHOT_SITE,SNAPSHOT_ID from dba_registered_snapshots where NAME like '%'||substr(upper('&&1'),-8)||'%'
/

set verify on

col REFERENCED_NAME clear
col SNAPSHOT_SITE clear
col sql clear
col owner clear

8. desc

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

set    feedback off verify off echo off timi off termout on
set    numformat "9,999,999,999"

col    COLUMN_NAME      for a30
col    type             for a16
col    NULLABLE         for a10 heading "NULL?"
col    created          for a19
col    object_name      for a25
col    DEFAULT          for a15 truncate
col    comments         for a30 truncate
col    owner            for a15
col    grantee          for a22
col    grantor          for a15
col    PRIVILEGE        for a19
col    TABLE_NAME       for a26
col    histogram        for a10 truncate
col    tab_info1        for a70
col    tab_info2        for a50
col    column_id        for 999 heading "SEQ"

start  arg1

var    v_owner          varchar2(20);
var    v_table_name     varchar2(30);
exec   :v_owner         := upper(NVL(SUBSTR('&&1', 1,INSTRB('&&1','.')-1), '&DFT_OWNER.'));
exec   :v_table_name    := upper( SUBSTR('&&1', INSTRB('&&1','.')+1));

--     db version 별 dictionary column 처리
set    termout off
col    col_ro           new_value col_ro
col    col_cmp          new_value col_cmp
col    col_cmp_for      new_value col_cmp_for
col    col_histogram    new_value col_histogram

select
       case when version  >= 11 then 'READ_ONLY'    else 'NULL' end as col_ro
      ,case when version  >= 11 then 'COMPRESSION'  else 'NULL' end as col_cmp
      ,case when version  >= 11 then 'COMPRESS_FOR' else 'NULL' end as col_cmp_for
      ,case when version  >= 10 then 'HISTOGRAM'    else 'NULL' end as col_histogram
from  (select to_number(substr(version,1,instr(version,'.',1,2)-1)) as version from v$instance)
/

col    col_ro           clear
col    col_cmp          clear
col    col_cmp_for      clear
col    col_histogram    clear
alter  session set nls_date_format='yyyy-mm-dd hh24:mi:ss' ;
set    termout on

select
      'OBJECT_NAME : '  || o.owner||'.'||o.object_name       ||chr(10)||
      'CREATED     : '  || o.created                         ||chr(10)||
      'LAST_DDL    : '  || o.LAST_DDL_TIME                   ||chr(10)||
      'PCTFREE     : '  || t.PCT_FREE                        ||chr(10)||
      'PCTUSED     : '  || t.PCT_USED                        ||chr(10)||
      'INITRANS    : '  || t.INI_TRANS                       ||chr(10)||
      'IOT_TYPE    : '  || t.IOT_TYPE                        ||chr(10)||
      'COMMENTS    : '  || c.comments
       as tab_info1,
      'DEGREE        : '|| ltrim(t.degree,' ')               ||chr(10)||
      'LOGGING       : '|| logging                           ||chr(10)||
      'NUM_ROWS      : '|| to_char(num_rows,'999,999,999')   ||chr(10)||
      'BLOCKS        : '|| to_char(BLOCKS,'999,999,999')     ||CHR(10)||
      'AVG_ROW_LEN   : '|| AVG_ROW_LEN                       ||chr(10)||
      'COMPRESSION   : '|| &col_cmp || '-' || &col_cmp_for   ||chr(10)||
      'LAST_ANALYZED : '|| LAST_ANALYZED                     ||chr(10)||
      'READ ONLY     : '|| &col_ro                           ||chr(10)||
      'BUFFER_POOL   : '|| BUFFER_POOL
       as tab_info2
from   dba_objects o,
       dba_tables t,
       dba_tab_comments c--, dba_segments s
where  1=1
and    c.owner        = :v_owner
and    c.table_name   = :v_table_name
and    o.owner        = :v_owner
and    o.object_name  = :v_table_name
and    o.object_type  = 'TABLE'
--and    s.owner        = :v_owner
--and    s.segment_name = :v_table_name
--and    s.segment_type = 'TABLE'
and    t.owner        = :v_owner
and    t.table_name   = :v_table_name
/

select a.COLUMN_ID
     , a.COLUMN_NAME
     --, a.DATA_TYPE||'('||DECODE( data_type, 'NUMBER', data_precision||decode(data_scale,0,null,','||data_scale), data_length )||')' "TYPE"
     , a.DATA_TYPE || case substr(data_type,1,9)
                          when 'NUMBER'     then '(' || data_precision||decode(data_scale,0,null,','||data_scale) || ')'
                          when 'TIMESTAMP'  then ''
                          when 'DATE'       then ''
                          when 'ROWID'      then ''
                          else                   '(' || data_length || ')'
                      end "TYPE"
     --, DATA_PRECISION
     , decode(a.NULLABLE,'N','NOT NULL') NULLABLE
     , a.DATA_DEFAULT "DEFAULT"
     --,(select comments from dba_col_comments where table_name = a.table_name and column_name = a.COLUMN_NAME) "COMMENTS"
     , b.comments "COMMENTS"
     , a.num_distinct
     , a.density
     , &col_histogram
from   dba_tab_columns  a
    ,  dba_col_comments b
  --,  dba_tab_col_statistics c
where  1=1
and    a.OWNER       = b.OWNER
and    a.table_name  = b.table_name
and    a.column_name = b.column_name
and    a.owner       = :v_owner
and    a.table_name  = :v_table_name
order  by a.OWNER, a.TABLE_NAME, a.COLUMN_ID
/

set pause off

select
       *
from   dba_tab_privs a
where  1=1
and    a.owner      = :v_owner
and    a.table_name = :v_table_name
/

select OWNER       
      ,SYNONYM_NAME
      ,TABLE_OWNER 
      ,TABLE_NAME  
from   dba_synonyms a
where  1=1
and    a.table_owner = :v_owner
and    a.table_name  = :v_table_name
/

select OWNER                      
      ,TABLE_NAME                 
      ,PARTITIONING_TYPE          
      ,SUBPARTITIONING_TYPE       
      ,PARTITION_COUNT            
      ,DEF_SUBPARTITION_COUNT     
      ,PARTITIONING_KEY_COUNT     
      ,SUBPARTITIONING_KEY_COUNT  
--      ,STATUS                     
from   dba_part_tables a
where  1=1
and    a.owner      = :v_owner
and    a.table_name = :v_table_name
/

select
       OWNER                                                 
      ,NAME                                                  
     ,'PARTITION' PTYPE
      ,OBJECT_TYPE                                           
      ,COLUMN_NAME                                           
      ,COLUMN_POSITION                                       
from   dba_part_key_columns a
where  1=1
and    a.owner = :v_owner
and    a.name  = :v_table_name
union  all
select
       OWNER                                                 
      ,NAME                                                  
     ,'SUBPARTITION' PTYPE
      ,OBJECT_TYPE                                           
      ,COLUMN_NAME                                           
      ,COLUMN_POSITION                                       
from   dba_subpart_key_columns a
where  1=1
and    a.owner = :v_owner
and    a.name  = :v_table_name
/

prompt

alter  session set nls_date_format='&dft_nls_date_format' ;

set    feedback on verify on echo off timi on
set    numformat ""

col    COLUMN_NAME  clear
col    type         clear
col    NULLABLE     clear
col    created      clear
col    object_name  clear
col    DEFAULT      clear
col    comments     clear
col    owner        clear
col    grantee      clear
col    grantor      clear
col    PRIVILEGE    clear
col    TABLE_NAME   clear
col    histogram    clear
col    column_id    clear
col    tab_info1    clear
col    tab_info2    clear

9. dg_stat

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

col name          for a24
col value         for a24
col UNIT          for a31
col TIME_COMPUTED for a35

SELECT * FROM V$DATAGUARD_STATS
/

col name          clear
col value         clear
col UNIT          clear
col TIME_COMPUTED clear

10. diff_stats

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

col DBMS_USER_NM  for a20
col tb_eng_nm  for a20
col select_cnt for 999,999,999
col stats_cnt  for 999,999,999

select 
       DBMS_USER_NM 
      ,a.tb_eng_nm
      ,a.tb_dt_cnt select_cnt
      ,b.num_rows stats_cnt
  from tb_cissd07 a, dba_tables b
 where 1=1
   and a.dbms_user_nm like '_______ADM'
   and a.dbms_user_nm = b.owner 
   and a.tb_eng_nm = b.table_name 
   and a.tb_dt_cnt > 0
   and b.num_rows = 0
union
select 
       DBMS_USER_NM 
      ,a.tb_eng_nm
      ,a.tb_dt_cnt select_cnt
      ,b.num_rows stats_cnt
  from tb_cissd07 a, dba_indexes b
 where 1=1
   and a.dbms_user_nm like '_______ADM'
   and a.dbms_user_nm = b.owner 
   and a.tb_eng_nm = b.table_name 
   and a.tb_dt_cnt > 0
   and b.num_rows = 0
;


col tb_eng_nm  clear
col select_cnt clear
col stats_cnt  clear
col DBMS_USER_NM  clear

11. diff_time_model

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

set serveroutput on

declare
  TYPE SysTimeModelType IS TABLE OF v$sys_time_model%ROWTYPE INDEX BY PLS_INTEGER;
  
  sys_time_res1 SysTimeModelType;
  sys_time_res2 SysTimeModelType;
  sys_time_diff SysTimeModelType;

begin
  for i in 1..19 loop
     select stat_id, stat_name, value
     into   sys_time_res1(i)
     from   (select stat_id, stat_name, value, rownum rnum from v$sys_time_model)
     where  rnum=i;
  end loop;

  dbms_lock.sleep(10);

  for i in 1..19 loop
     select stat_id, stat_name, value
     into   sys_time_res2(i)
     from   (select stat_id, stat_name, value, rownum rnum from v$sys_time_model)
     where  rnum=i;
  end loop;

  for i in 1..19 loop
     dbms_output.put_line(rpad(sys_time_res1(i).stat_name,50,' ')||' => '||round ((sys_time_res2(i).value-sys_time_res1(i).value)/1000000,2));
  end loop;
 
end;
/

12. dml

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

set verify off
select 'connect '||USERNAME||'/'||PASSWD command, CHGDT
from zzdmlusr
where USERNAME = upper('&USERNAME');
set verify on

13. dn_grade

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

select sid,s.username,p.sql_id,s.program
from v$session s, v$sql_plan p 
where OPERATION like 'PX COORDINATOR%'
and s.sql_id = p.sql_id
and child_number = 0
and not exists (select 1 from v$px_session where sid = s.sid)
/

14. documnet_about_DB

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

1	Requires strict user-specific permissions and usage restrictions.
       - admin: Object owner with DBA rights, thorough P/W management
       - dev : User for developers and Tuxedo. Work centered on DML
       - Sel : User for inquiry only (create session, altern session, select any table only)
       - dbl : a user dedicated to dblink (create session, select any table only)

2	Use Special User
	; Some tables may be required to prevent general developers from doing DML.
        e.g.) Billing's rate table
        Create another user, such as CM01, to grant DML privileges, 
        and grant dev users only select privilege (select only on Tuxedo)

3	Need to pay attention to refresh and method when creating snapshots
	- fast or complete
	- primary key or rowid
	- whether or not mlogs are required
	- Decide refresh time (sysdate + 1 refrain from using)
	- Consider the amount of data because storage options for P.K cannot be 
          granted when creating it. (primary key only)

4	Uniformization of DBLINK (Compliance with Naming Rules)
       - DBLINK is limited to Select Only
       - Please be aware of the opportunity to be linked (OPS usage needs to be 
         considered)

5	Precautions for Tablespace Creation
      - consider default storage options
      - The default table space inherited by the dev user is highly likely to be used as a 
         place to make temporary tables for workability, so it is necessary to check the 
         default storage option.
        (In the case of customers, initials are preferable from 64K to 128K and next 256K)
        (In the case of billing, 1M initial is preferred, and 1M or more is preferred for next.)
      - Object type and Tablespace configuration should be consistent with I/O distribution
        (Building is large in datafile for Tablespace for Partition and small in size for customer except in part)

6	Precautions for Table Creation
      - Freelist groups 2 because it is an OPS environment
      - In case of freelists, default 1 is fine, but about 10 is recommended, and in case of large capacity, it should be 20
      - Some tables in the billing system have initials of 10 degrees due to concurrent DML (Batch job)
      - Default pctfree 10 pctused 40, it is okay to raise the pctused to 60 to 80 degrees for hysteresis tables (Insert-oriented), 
        and for billing, the raw copy table is set to pctfree 5 pctused 90 and the intex is set to 5.
      - For maxextents, the customer set 505 billing to 1017 (reflective glass in the default storage option)

7	Precautions for Object Change
      - When adding column, adding/deleting index, and adding/deleting partition, the plan may suddenly change, so make sure to apply analyze.
      - Plan changes frequently after large-capacity DML work, so analyze is performed after batch work.

15. e

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

REM **** Make sure to create and perform the hextodec function
!date
set pause off heading off linesize 250
col csid     for a28 trunc
col pid      for a25
col event    for a100
col osuser   for a10
col program  for a20
col comments for a200
col npid     for 999999999
select a.sid||'-'||a.serial#||'('||a.osuser||'/'||lower(a.status)||')' csid,
       c.event||' {'||
       decode(event,
              'enqueue', c.p1text||'='||chr(c.p1),
--              'enqueue', c.p1text||'='||chr(hextodec(c.p1raw)),
                         c.p1text||'='||c.p1)|| '} [' || c.p2text||'='||c.p2||c.p3text||'='||c.p3||']'||-- event,
       a.process||'-'||b.spid||-- pid,
       decode(instrb(a.program,'@'),
              0,a.program,
              substrb(a.program,1,instrb(a.program,'(')-2))||-- program,
       'command->' ||
       decode(a.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') comments
from   v$session a, v$process b, v$session_wait c
where  a.paddr = b.addr
and    a.sid = c.sid
and    event != 'SQL*Net message from client'
and    event != 'rdbms ipc message'
and    event != 'pmon timer'
and    event != 'smon timer'
and    event != 'lock manager wait for remote message'
and    event not like 'Null%'
and not exists ( select 1 from v$mystat where sid = a.sid )
order by c.event, a.sid
/

select /*+ rule */
       to_number(sid)||'   [ '|| type || ' ]   [ '|| id1 || ' ]   [ '|| id2 || ' ]   [ ' ||
       decode(lmode,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              to_char(lmode)) || ' ]   [ ' ||
       decode(request,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              to_char(request)) || ' ]   [ ' ||
       decode(block,
              0, 'NONE',
              1, 'Blocking',
              2, 'Global',
              to_char(block)) || ' ]'
from  v$lock
where type in ( 'ST', 'DX', 'TM', 'TX' , 'SQ' )
and   request > 0 or block = 1
order by type, id1, sid, lmode
/
set heading on linesize 140
col csid     clear
col pid      clear
col event    clear
col osuser   clear
col program  clear
col comments clear
col npid     clear

16. e2

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

select s.sid, s.serial#, substr(s.program,1,20) program ,
 status, p.spid, osuser, process,
  q.sql_text, rows_processed,event
  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
  --ands.program like '%cok%'
/

17. e3

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

col event for a27
col P1 for a20
col P2 for a20

set pages 0 
select sid,event,p1text||'=>'||p1 "P1",p2text||'=>'||p2 "P2" from v$session_wait
where event not like 'SQL%'
and   event not like 'Null%'
/
exit

18. ee

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

set pages 100

select b.sid||'-'||b.serial# as "S_INFO", b.username, b.osuser, b.machine,
b.process, b.program, a.sql_text SQL
from v$sql a, v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and b.sid = &sid
/

19. event

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

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

select s.sid,s.serial#,substr(s.program,1,20) program,p.spid LOCAL,s.process REMOTE
from v$session s,v$process p
where s.sid=&sid
and   s.paddr=p.addr
/

select s.sid, s.serial#, substr(s.program,1,20) program ,
 status, p.spid, osuser, process,
 q.sql_text, rows_processed,event
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
--ands.program like '%cok%'
/

20. event_s2

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

REM **** Make sure to create and perform the hextodec function
!date
store set /tmp/.env.sql replace
set pause off heading off linesize 250
col csid     for a28 trunc
col pid      for a25
col event    for a100
col osuser   for a10
col program  for a20
col comments for a200
col npid     for 999999999
select a.sid||'-'||a.serial#||'('||a.osuser||'/'||lower(a.status)||')' csid,
       c.event||' {'||
       decode(c.event,
              'enqueue', c.p1text||'='||chr(c.p1),
--              'enqueue', c.p1text||'='||chr(hextodec(c.p1raw)),
                         c.p1text||'='||c.p1)|| '} [' || c.p2text||'='||c.p2||c.p3text||'='||c.p3||']'||-- event,
       a.process||'-'||b.spid||-- pid,
       decode(instrb(a.program,'@'),
              0,a.program,
              substrb(a.program,1,instrb(a.program,'(')-2))||-- program,
       'command->' ||
       decode(a.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') comments
from   v$session a, v$process b, v$session_wait c
where  a.paddr = b.addr
and    a.sid = c.sid
and    a.event != 'SQL*Net message from client'
and    a.event != 'rdbms ipc message'
and    a.event != 'pmon timer'
and    a.event != 'smon timer'
and    a.event != 'gcs remote message'
and    a.event != 'ges remote message'
and    a.event != 'jobq slave wait'
and    a.event != 'lock manager wait for remote message'
and    a.event not like 'Streams AQ%'
and    a.event not like 'Null%'
and    a.status = 'ACTIVE'
and not exists ( select 1 from v$mystat where sid = a.sid )
order by c.event, a.sid
/

select /*+ rule */
       to_number(sid)||'   [ '|| type || ' ]   [ '|| id1 || ' ]   [ '|| id2 || ' ]   [ ' ||
       decode(lmode,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              to_char(lmode)) || ' ]   [ ' ||
       decode(request,
              0, 'None',           /* Mon Lock equivalent */
              1, 'Null',           /* N */
              2, 'Row-S (SS)',     /* L */
              3, 'Row-X (SX)',     /* R */
              4, 'Share',          /* S */
              5, 'S/Row-X (SSX)',  /* C */
              6, 'Exclusive',      /* X */
              to_char(request)) || ' ]   [ ' ||
       decode(block,
              0, 'NONE',
              1, 'Blocking',
              2, 'Global',
              to_char(block)) || ' ]'
from  v$lock
where type in ( 'ST', 'DX', 'TM', 'TX' , 'SQ' )
and   request > 0 or block = 1
order by type, id1, sid, lmode
/
set heading on linesize 140
col csid     clear
col pid      clear
col event    clear
col osuser   clear
col program  clear
col comments clear
col npid     clear
@/tmp/.env.sql
!rm -f /tmp/.env.sql

21. ex

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

exec :&variable   := '&value';

22. ext

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

select *
from  (
       select TABLESPACE_NAME, FILE_ID, BLOCK_ID, BYTES/1024 "Bytes(M)"
       from   dba_free_space
       where  tablespace_name like upper('&tablespace_name')||'%'
       order by bytes desc
      )
where  rownum <= 50
/

23. flb

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

col   name   for   a40
col   SPACE_FREE   for   999,999,999
col   SPACE_LIMIT  for   999,999,999
col   space_reclaimable  for   999,999,999
col   FLASHBACK_SIZE  for   999,999,999
col   ESTIMATED_FLASHBACK_SIZE for   999,999,999

--alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

prompt Unit = MB
SELECT
       name,
       space_limit/1024/1024                SPACE_LIMIT,
       (space_limit-a.SPACE_USED)/1024/1024 SPACE_FREE,
       space_reclaimable/1024/1024 space_reclaimable,
       number_of_files
FROM v$recovery_file_dest a
/

SELECT *
FROM V$FLASH_RECOVERY_AREA_USAGE a
/

SELECT OLDEST_FLASHBACK_SCN, to_char(OLDEST_FLASHBACK_TIME,'yyyy-mm-dd hh24:mi:ss') OLDEST_FLASHBACK_TIME, RETENTION_TARGET, 
FLASHBACK_SIZE/1024/1024 FLASHBACK_SIZE, ESTIMATED_FLASHBACK_SIZE/1024/1024 ESTIMATED_FLASHBACK_SIZE
FROM V$FLASHBACK_DATABASE_LOG a
/

col name clear
col SPACE_FREE   clear
col SPACE_LIMIT  clear
col space_reclaimable clear
col FLASHBACK_SIZE clear
col ESTIMATED_FLASHBACK_SIZE clear
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]