isitdevops.com/databases

— free spc.sql
— mdw – check free space for (possibly) stated tablespace
—
set veri off
set pause on
set pause ‘Any Key…>’
set pages 24
spool free_spc.lst
break on t_name skip 1
select tablespace_name t_name
      ,blocks          blocks
      ,bytes           bytes
      ,to_char(bytes/1024,’9,999,999′) k_free
from sys.dba_free_space
where tablespace_name like upper(‘&tab_space’||’%’)
and    blocks > nvl(to_number(‘&blk_lim’),0)
order by tablespace_name,blocks desc
/
spool off

— par_size.sql
— mdw 10/03/97
— show current allocated space for (by default tables) specifed objects
— mdw 24/04/01
— adding in the checking of user to look at – sanger uses multiple schemas
—
clear break
break on o_type nodup on owner nodup on obj_name nodup skip 1 on tablespace nodup
set veri off
set pause on
set pages 24
set lines 80
col o_type form a8 truncate
col owner form a8 word wrap
col obj_name form a22 word wrap
col tablespace form a14 word wrap
— mdw temp change to spool file
spool par_siz.lst
select segment_type                                 o_type
,owner                                        owner
,segment_name||’ ‘||partition_name            obj_name
,tablespace_name                              tablespace
,substr(extent_id,1,3)                        num
,substr(to_char(bytes/1024,’99,999,999′),1,11) K_bytes                 
,substr(to_char(blocks,’999,999′),1,8)        blocks
from sys.dba_extents
where segment_name like upper(nvl(‘&nme’,’WHOOPS’)||’%’)
and   segment_type like upper(nvl(‘&typ’,’TABLE’)||’%’)
and   partition_name like upper(‘&ptn’||’%’)
and   owner like upper(nvl(‘&own’,user)||’%’)
order by 1,2,3,5
/
clear colu
spool off

— rbs_dets.sql
— mdw 02/02/00
— list of rollback seg details
set pause on pages 30 pause ‘Any Key..> ‘
col owner form a7
col instance_num form a1 head I
col rb_name form a15 wrap
col ts_name form a12 wrap
col ini_e_k form 999,999
col nxt_e_k form 999,999
col min_e form 999
col max_e form 99,999
col stat  form A7
spool rbs_dets.lst
—
select
owner
,instance_num
,segment_name     rb_name
,tablespace_name   ts_name
,initial_extent/1024    ini_e_k
,next_extent/1024       nxt_e_k
,min_extents       min_e
,max_extents       max_e
,status            stat
from dba_rollback_segs
order by owner,segment_name
/
spool off
clear col
— EOF

— chk_gv_res.sql
— mdw 01/08/01
— quick check on the gv$resource entries. NB most of use on OPS
col inst_id    form 999 head inst
col resource_name form a25 word wrap
col current_utilization head curr_util
col max_utilization     head max_util
col initial_allocation  head ini_alloc
col limit_value         head  Limit_val
spool chk_gv_res.lst
select * from gv$resource_limit where upper(resource_name) like upper(‘&res_name’)||’%’ order by inst_id,resource_name
/
clear colu
spool off

— chk_waits.sql
spool chk_waits.lst
set timi on pause on pages 32
col event form a40 wrap
col total_waits form 999999,999
col total_timeouts form 999999,999 head tot_timouts
select event,total_waits,total_timeouts,time_waited
from v$system_event
order by time_waited desc
/
clear colu
spool off

— sid_sga.sql
— mdw 11/97
— pinched from ‘bee’ book
— list all sql in SGA attached to a given sid.
—
set pause on
set pages 24
set pause ‘Any Key>’
spool sid_sga.lst
select s.process fground
,p.spid bkgrnd,
x.disk_reads,
x.rows_processed,x.users_executing
,x.sql_text
from sys.v_$session s
,sys.v_$process p
,sys.v_$sqlarea x
where s.sid = &sid
and  s.paddr = p.addr
and s.type != ‘BACKGROUND’
and s.sql_address=x.address
and s.sql_hash_value = x.hash_value
order by s.sid
/
spool off
— EOF

— show_awr_metrics
— quickly pull up the metrics being collected by AWR and the units
—
spool show_awr_metrics.lst
col grp form 999
col mtr_id for 99999
col group_name form a20 wrap
col metric_name form a22 wrap
col metric_unit form a22 wrap
select –dbid,
group_id      grp
,group_name
,metric_id   mtr_id
,metric_name
,metric_unit
from dba_hist_metric_name
where lower(metric_name) like ‘&metric’||’%’
order by 2,3,4,5
/
spool off
—

— get_text.sql
— get the sqltext for a stated hash_value (which may or may not be
— unique)
set pages 42
set pause on
set trim on
spool get_text.lst
select sql_text
from v$sqltext
where hash_value = ‘&hash_val’
order by address,piece
/
spool off

–os_chk.sql
— mdw 99
— quick check of what server you are on. Wrote before V$instance available?
— NB Gives full OS info – no available from v$instance
set serveroutput on;
begin
declare
v_temp varchar2(255) := ‘hello world’;
begin
  v_temp := dbms_utility.port_string;
  dbms_output.put_line(v_temp);
end;
end;
/

— explain template
set pages 5000
set pause off
undef statement
set verify off  feedback off
explain plan
set statement_id = ‘mdw’
into plan_table
for
— insert sql here
—
—
spool exp1.lst
select operation||’ ‘||to_char(timestamp,’DD/MM HH24:MI:SS’)||’ ‘||optimizer||’ cst:’||to_char(cost)||’ crd:’||to_char(cardinality) Plan
from plan_table
where statement_id = ‘mdw’
and id = 0
/
select rpad(to_char(id),2*level)||operation||’ ‘||options||’ ‘||object_name
  ||decode(cost,null,decode(partition_start,null,null
                           ,    ‘ strt ‘||partition_start
                               ||’ stp ‘||partition_stop)
               ,      decode(partition_id,null,”
                       ,     ‘ P’||partition_id)
                      ||’ cst:’||to_char(cost)||’ rws:’||cardinality
          )
            Plan
from plan_table
where statement_id = ‘mdw’
connect by prior id = parent_id
and        prior statement_id = ‘mdw’
start with id = 1
/
spool off
delete from plan_table
where statement_id = ‘mdw’
/
set verify on  feedback on  pause on  pause ‘Any Key…>’
—
— End of File
—

— cre_stats_tab.sql
— create it owned by oradba in the dbaadmin_dat TS for now.
drop public synonym stattab;
EXEC dbms_stats.create_stat_table(‘SYSTEM’,’CMC_STATS’,’DBAADMIN_DAT’);
create public synonym cmc_stats for system.cmc_stats;
grant all on system.cmc_stats to oradba;
grant all on system.cmc_stats to orasup;

— tab_chain.sql
— mdw 10/07/01
— check for any analysed tables with significant chaining
set pages 24
set pause on
set pause ‘Any Key…’
col table_name form a25 word wrap
col owner      form a15 word wrap
col num_rows   form 999,999,999
col chain_cnt  form 9,999,999
col pct_chn  form 99.99
accept tab_name char prompt ‘which tables are to be checked ? >’
prompt only works on analyzed tables/partitioned tables
spool tab_chain.lst
select table_name
      ,owner
      ,num_rows
      ,chain_cnt
      ,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tables
— NB keep greatest for num_rows and  percentage in line else a table with
— (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1)
     /greatest(nvl(num_rows,1),1000)   > 0.001
and table_name like upper(‘&tab_name’||’%’)
and owner not in (‘SYS’,’SYSTEM’)
union
select table_name||’ ‘||partition_name table_name
      ,table_owner                     owner
      ,num_rows
      ,chain_cnt
      ,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tab_partitions
— NB keep greatest for num_rows and  percentage in line else a table with
— (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1)
     /greatest(nvl(num_rows,1),1000)   > 0.001
and table_name like upper(‘&tab_name’||’%’)
and table_owner not in (‘SYS’,’SYSTEM’)
order by 1,2
/
spool off
clear colu

SET ECHO off
REM NAME:   TFSLKILL.SQL
REM USAGE:”@path/tfslkill”
REM ————————————————————————
REM REQUIREMENTS:
REM    SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ————————————————————————
REM PURPOSE:
REM    The report generated by this script gives information on sessions
REM    which are holding locks and gives the information needed to kill
REM    using the ALTER SYSTEM KILL SESSION command.
REM ————————————————————————
REM Main text of script follows:
 
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading ‘Kill String’ format a13
column res heading ‘Resource Type’ format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading ‘Lock Held’ format a20
column request heading ‘Lock Requested’ format a20
column serial# format 99999
column username  format a10  heading “Username”
column terminal heading Term format a6
column tab format a35 heading “Table Name”
column owner format a9
column Address format a18
select nvl(S.USERNAME,’Internal’) username,
 nvl(S.TERMINAL,’None’) terminal,
 L.SID||’,’||S.SERIAL# Kill,
 U1.NAME||’.’||substr(T1.NAME,1,20) tab,
 decode(L.LMODE,1,’No Lock’,
  2,’Row Share’,
  3,’Row Exclusive’,
  4,’Share’,
  5,’Share Row Exclusive’,
  6,’Exclusive’,null) lmode,
 decode(L.REQUEST,1,’No Lock’,
  2,’Row Share’,
  3,’Row Exclusive’,
  4,’Share’,
  5,’Share Row Exclusive’,
  6,’Exclusive’,null) request
from V$LOCK L, 
 V$SESSION S,
 SYS.USER$ U1,
 SYS.OBJ$ T1
where L.SID = S.SID 
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) 
and U1.USER# = T1.OWNER#
and S.TYPE != ‘BACKGROUND’
order by 1,2,5
/

SET ECHO off
REM NAME:   TFSDBJBS.SQL
REM USAGE:”@path/tfsdbjbs”
REM ————————————————————————
REM REQUIREMENTS:
REM    SELECT on DBA_JOBS
REM ————————————————————————
REM PURPOSE:
REM    Lists all jobs that have been submitted to run in the
REM    local database job queue.
REM ————————————————————————
REM Main text of script follows:
 
set pagesize 80
ttitle –
  center  ‘List Submitted Jobs’ skip 2
 
col jid  format 999  heading ‘Id’
col subu format a10  heading ‘Submitter’     trunc
col secd format a10  heading ‘Security’      trunc
col proc format a20  heading ‘Job’           word_wrapped
col lsd  format a5   heading ‘Last|Ok|Date’ 
col lst  format a5   heading ‘Last|Ok|Time’
col nrd  format a5   heading ‘Next|Run|Date’
col nrt  format a5   heading ‘Next|Run|Time’
col fail format 999  heading ‘Errs’
col ok   format a2   heading ‘Ok’
 
select
  job                        jid,
  log_user                   subu,
  priv_user                  secd,
  what                       proc,
  to_char(last_date,’MM/DD’) lsd,
  substr(last_sec,1,5)       lst,
  to_char(next_date,’MM/DD’) nrd,
  substr(next_sec,1,5)       nrt,
  failures                   fail,
  decode(broken,’Y’,’N’,’Y’) ok
from
  sys.dba_jobs
/

SET ECHO off
REM NAME:   TFSLCKWT.SQL
REM USAGE:”@path/tfslckwt”
REM ————————————————————————
REM REQUIREMENTS:
REM    SELECT on V$SESSION, V$LOCK
REM ————————————————————————
REM PURPOSE:
REM    Reports users waiting for locks.
REM ————————————————————————
REM EXAMPLE:
REM    USERNAME          SID TYPE HELD        REQ           ID1        ID2
REM    ————— —– —- ———– ———– ——– ——–
REM    SYSTEM             12 TX   Exclusive   None          131087     2328
REM    SCOTT               7 TX   None        Exclusive     131087     2328 
REM    SCOTT               8 TX   Exclusive   None          131099     2332
REM    SYSTEM             10 TX   None        Exclusive     131099     2332
REM    SYSTEM             12 TX   None        Exclusive     131099     2332
REM 
REM ————————————————————————
REM
column username format  A15
column sid      format  9990    heading SID
column type     format  A4
column lmode    format  990     heading ‘HELD’
column request  format  990     heading ‘REQ’
column id1      format  9999990
column id2 format  9999990
break on id1 skip 1 dup
spool tfslckwt.lst

SELECT sn.username, m.sid, m.type,
        DECODE(m.lmode, 0, ‘None’,
                        1, ‘Null’,
                        2, ‘Row Share’,
                        3, ‘Row Excl.’,
                        4, ‘Share’,
                        5, ‘S/Row Excl.’,
                        6, ‘Exclusive’,
                lmode, ltrim(to_char(lmode,’990′))) lmode,
        DECODE(m.request,0, ‘None’,
                         1, ‘Null’,
                         2, ‘Row Share’,
                         3, ‘Row Excl.’,
                         4, ‘Share’,
                         5, ‘S/Row Excl.’,
                         6, ‘Exclusive’,
                         request, ltrim(to_char(m.request,
                ‘990’))) request, m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR    (sn.sid = m.sid AND m.request = 0 AND lmode != 4
       AND (id1, id2)
          IN (SELECT s.id1, s.id2 FROM v$lock s
              WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2
             )
      )
ORDER BY id1, id2, m.request;
spool off
clear breaks;

SET ECHO off
REM NAME:    TFSLKSQL.SQL
REM USAGE:”@path/tfslksql”
REM ————————————————————————
REM REQUIREMENTS:
REM    SELECT on V$SQLTEXT, V$SESSION, and V$ACCESS
REM ————————————————————————
REM PURPOSE:
REM    This script will report the SQL text of some of the locks 
REM    currently being held in the database.
REM ————————————————————————
REM EXAMPLE:
REM    USERNAME                              SID
REM    —————————— ———-
REM    OBJECT
REM    ——————————————————————-
REM    LOCKWAIT SQL
REM    ——– ———————————————————-
REM    SYSTEM                                 11
REM    SCOTT.TABLE_CONFIG
REM    E0034A5C update scott.table_config set tabno=99 where tabno=9
REM    
REM    SYS                                     6
REM    SCOTT.TABLE_CONFIG
REM    E0034C98 update scott.table_config set capacity=28 where capacity=4
REM 
REM ————————————————————————
REM Main text of script follows:
 
set pagesize 60
set linesize 132
select s.username username, 
       a.sid sid, 
       a.owner||’.’||a.object object, 
       s.lockwait, 
       t.sql_text SQL
from   v$sqltext t, 
       v$session s, 
       v$access a
where  t.address=s.sql_address 
and    t.hash_value=s.sql_hash_value 
and    s.sid = a.sid 
and    a.owner != ‘SYS’
and    upper(substr(a.object,1,2)) != ‘V$’
/

/*

set pagesize 60
set linesize 132
select s.username username, s.sid, s.serial#, 
       t.sql_text SQL
from   v$sqltext t, 
       v$session s
where  t.address=s.sql_address 
and    t.hash_value=s.sql_hash_value
and     s.type = ‘USER’
/
*/

SET ECHO off
REM NAME:   TFSDBJRN.SQL
REM USAGE:”@path/tfsdbjrn”
REM ————————————————————————
REM REQUIREMENTS:
REM    SELECT on DBA_JOBS, DBA_JOBS_RUNNING
REM ————————————————————————
REM PURPOSE:
REM    Lists all jobs that are currently running in the local databbase.
REM ————————————————————————
REM Main text of script follows:
 
set pagesize 80
ttitle –
  center  ‘List Running Jobs’  skip 2
 
col sess format 99   heading ‘Ses’
col jid  format 999  heading ‘Id’
col subu format a10  heading ‘Submitter’     trunc
col secd format a10  heading ‘Security’      trunc
col proc format a20  heading ‘Job’           word_wrapped
col lsd  format a5   heading ‘Last|Ok|Date’ 
col lst  format a5   heading ‘Last|Ok|Time’
col nrd  format a5   heading ‘This|Run|Date’
col nrt  format a5   heading ‘This|Run|Time’
col fail format 99 heading ‘Err’
 
select
  djr.sid                        sess,
  djr.job                        jid,
  dj.log_user                    subu,
  dj.priv_user                   secd,
  dj.what                        proc,
  to_char(djr.last_date,’MM/DD’) lsd,
  substr(djr.last_sec,1,5)       lst,
  to_char(djr.this_date,’MM/DD’) nrd,
  substr(djr.this_sec,1,5)       nrt,
  djr.failures                   fail
from
  sys.dba_jobs dj,
  sys.dba_jobs_running djr
where
  djr.job = dj.job
/

set pagesize 66
col c1 for a9
col c1 heading “OS User”
col c2 for a9
col c2 heading “Oracle User”
col b1 for a9
col b1 heading “Unix PID”
col b2 for 9999 justify left
col b2 heading “SID”
col b3 for 99999 justify left
col b3 heading “SERIAL#”
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3

select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
  from v$sqltext a, v$session b, v$process c
   where a.address    = b.sql_address
–   and b.status     = ‘ACTIVE’ /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ONLY ACTIVE TRANSACTIONS ON THAT MOMENT */
   and b.paddr      = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid,a.hash_value,a.piece;
         

REM    SELECT ON V$LOCK, V$SESSION
EM PURPOSE:
REM Shows User Lock Information
REM EXAMPLE:
REM    Sess                    Op Sys                   OBJ NAME or
REM     ID    USERNAME User ID  TERMINAL TRANS_ID          TY Lock Mode   Req Mode
REM    —-    ——————- ———–  ————— ———————-
REM       7   SCOTT        usupport  ttyr5        TA           TM Row Excl
REM       7   SCOTT        usupport  ttyr5        Trans-196623 TX Exclusive
REM       8   SCOTT        usupport  ttyr1        TABLE_CONFIG TM Row Excl
REM       8   SCOTT        usupport  ttyr1        Trans-131099 TX Exclusive
REM      10   SYSTEM       usupport  ttyqe        TABLE_CONFIG TM Row Excl
REM      10   SYSTEM       usupport  ttyqe        Trans-131099 TX –Waiting–     Exclusive
REM      11   SYS          usupport  ttyr8        GTEMP        TM Row Excl
REM      11   SYS          usupport  ttyr8        Trans-196622 TX Exclusive
REM      12   SYSTEM       usupport  ttyr6        INDEX_BLOCKS TM Row Excl
REM      12   SYSTEM       usupport  ttyr6        Trans-131080 TX Exclusive
REM
REM ————————————————————————
REM DISCLAIMER:
REM    This script is provided for educational purposes only. It is NOT
REM    supported by Oracle World Wide Technical Support.
REM    The script has been tested and appears to work as intended.
REM    You should always run new scripts on a test instance initially.
REM ————————————————————————
REM Main text of script follows:
set echo off
set pagesize 60
Column SID         FORMAT 999 heading “Sess|ID “
COLUMN OBJECT_NAME FORMAT A17 heading “OBJ NAME or|TRANS_ID” Trunc
COLUMN OSUSER      FORMAT A10 heading “Op Sys|User ID”
COLUMN USERNAME    FORMAT A8
COLUMN TERMINAL    FORMAT A8  trunc
select B.SID,
       C.USERNAME,
       C.OSUSER,
       C.TERMINAL,
       DECODE(B.ID2, 0, A.OBJECT_NAME, ‘Trans-‘||to_char(B.ID1)) OBJECT_NAME,
       B.TYPE,
       DECODE(B.LMODE,0,’–Waiting–‘,
                      1,’Null’,
                      2,’Row Share’,
                      3,’Row Excl’,
                      4,’Share’,
                      5,’Sha Row Exc’,
                      6,’Exclusive’,
                        ‘Other’) “Lock Mode”,
       DECODE(B.REQUEST,0,’ ‘,
                      1,’Null’,
                      2,’Row Share’,
                      3,’Row Excl’,
                      4,’Share’,
                      5,’Sha Row Exc’,
                      6,’Exclusive’,
                     ‘Other’) “Req Mode”
from  DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
and   B.SID = C.SID
and   C.USERNAME is not null
order by B.SID, B.ID2;