Category Archives: scripts – dba

R27 – Distinct Extent Sizes per Tablespace (fragmentation)

Posted on by 
prompt Robot Script #27: Distinct Extent Sizes per Tablespace
prompt ######################################################
break on tablespace
column Tablespace format a20
column "Size_KB" format 9,999,999
select Tablespace, count(*) from ( 
select tablespace_name Tablespace, bytes/1024 "Size_KB", count(*) 
from dba_extents
where tablespace_name not in ('SYSAUX','SYSTEM','XDB') 
group by tablespace_name, bytes)
order by 1,2 desc
/

R26 – Chained Rows ratio

Posted on by 
prompt 
prompt Robot Script #26: Tables with more Chaining 
prompt ###########################################
set line 120
set pagesize 50
column squema format a13
column Tabel format a22
column "Chain%" format 999,99
column chain_cnt format 999,999,999
column num_rows format 999,999,999
column DataStats format a10
column sample_size format 999,999,999
select distinct rpad(t.owner,20) squema, 
rpad(c.table_name,25) Tabel, 
t.chain_cnt, t.num_rows, 
round(t.chain_cnt/t.num_rows*100,2) "Chain%",
to_char(c.last_analyzed) DataStats, c.sample_size
from dba_tables t, dba_tab_columns c
where t.table_name = c.table_name
and t.chain_cnt > 0
and c.last_analyzed is not null
and c.sample_size is not null
order by 5 desc
/

R23 – Top 50 Schema Objects in Cache

Posted on by 
prompt 
prompt Robot Script #23: Top 50 Schema Objects in Cache
prompt ################################################
-- You need to create an auxiliary function for partitioned objects 
-- Note: This mechanism only works with partitioned objects that are all in the same buffer pool 
create or replace function get_bp(name varchar2, type varchar2) return varchar2 is
bpool varchar2(7);
begin
 if type in ('T','t') then
  select distinct buffer_pool
  into bpool
  from dba_tab_partitions
  where table_name = name;
 elsif type in ('I','i') then
     select distinct buffer_pool
   into bpool
   from dba_ind_partitions
  where index_name = name;
 else
  raise_application_error('Tipo de Objecto não permitido. (T)abela ou (I)ndice',-20500);
 end if;
 return(bpool);
end;
/
column Object_ format a32
column Size_MB format 999,999,999 
column CACHE format a10
select rpad(b.owner||'.'||b.object_name,42) Object_, rpad(c.buffer_pool,10) CACHE,
    count(*) NumBlocs, round((count(*)*8192)/1024/1024,3) Size_MB
    from dba_objects b,v$bh a,
        (select nvl(buffer_pool,get_bp(table_name,'T')) buffer_pool, 
                table_name Nome from dba_tables 
         union all 
         select nvl(buffer_pool,get_bp(index_name,'I')) buffer_pool, 
                index_name Nome from dba_indexes) c
    where a.objd= b.object_id
    and b.object_name = c.nome
    group by  b.owner||'.'||b.object_name,c.buffer_pool
    having count(*) > 50
order by 2 desc,3 desc
/
drop function get_bp
/

R16 – Log archive frequency (master/detail)

Posted on by

Attention: These scripts use two substitution variables for the number of days you want to look back at. Most of the times you will want to use a large value for master (say 16 days) and a smaller value for the detail (say half the master: 8 days):

prompt 
prompt Robot Script 16a: Log archive frequency (master)
prompt ##########################################################
column day format a10
column "Log Switches" format 999999
select to_char(first_time,'YYYY-MM-DD') Day,count(*) "Log Switches"
from v$log_history
where first_time > SYSDATE-&Number_Archive_Days_Master
group by to_char(first_time,'YYYY-MM-DD')
order by 1 
/
prompt 
prompt Robot Script 16b: Log archive frequency (detail)
prompt ##########################################################
column "First DML" format a20
column day format a10
set pagesize 300
select to_char(first_time,'DD-MM-YYYY') Day,
sequence# LSN,
to_char(first_time,'HH24:MI:SS') "First DML"
from v$log_history
where first_time > SYSDATE-&Number_Archive_Days_Detail
order by sequence#
/

R04 – Jobs created both in the database and the scheduler

Posted on by

This Robot Script #4 brings you information for both jobs created with DBMS_JOBS package (database jobs) and the ones created with DBMS_SCHEDULER_JOBS (Scheduler Jobs):

prompt Robot Script #4a: Database Jobs
prompt ##############################
set line 105
column id format a2
column DB_User format a10
column instruction format a50
column "Next Exec" format a19
column "Last Exec" format a19
column interval format a30
column failures format 9999
select rpad(to_char(job),2) ID,
rpad(log_user,10) DB_User,
rpad(what,50) Instruction,
to_char(next_date,'DD-MM-YYYY HH24:MI:ss') "Next Exec",
to_char(last_date,'DD-MM-YYYY HH24:MI:ss') "Last Exec",
FAILURES,INTERVAL
from dba_jobs
/
prompt
prompt Robot Script #4b: SCHEDULER Jobs
prompt ################################
column job_name format a30
column state format a10
column run_count format 999999999
column LAST_START_DATE format a40
break on state
set line 100
set pagesize 300
select     state,
        JOB_NAME,
        run_count,
        to_char(LAST_START_DATE) LAST_START_DATE
from dba_scheduler_jobs
where job_name not like 'SQLSCRIPT%'
and job_name not like '%SQLACCESS%'
and job_name not like 'RLM$%'
and job_name not like 'FGR$%'
order by 1,LAST_START_DATE
/

R01 – How many and what object types has every Schema

Posted on by 
prompt Robot Script #01: How many and what object types has every Schema
prompt ######################################################
set line 120
column name format a13 trunc heading SCHEMA
column cl format 9999 heading CLSTR
column ta format 99999 heading TABLE
column ix format 99999 heading INDEX
column se format 9999 heading SEQNC
column tr format 999 heading TRIG
column fn format 9999 heading FUNCT
column pr format 9999 heading PROC
column pa format 9999 heading PACKG
column vi format 9999 heading VIEWS
column sy format 99999 heading SYNYM
column ot format 999999 heading OTHER
break on report
compute sum of cl ta ix se tr fn pr pa vi sy ot on report
select
  u.name,
  sum(decode(o.type#, 3, objs))  cl,
  sum(decode(o.type#, 2, objs))  ta,
  sum(decode(o.type#, 1, objs))  ix,
  sum(decode(o.type#, 6, objs))  se,
  sum(decode(o.type#, 12, objs)) tr,
  sum(decode(o.type#, 8, objs))  fn,
  sum(decode(o.type#, 7, objs))  pr,
  sum(decode(o.type#, 9, objs))  pa,
  sum(decode(o.type#, 4, objs))  vi,
  sum(decode(o.type#, 5, objs))  sy,
  sum(decode(o.type#, 1,0, 2,0, 3,0, 4,0, 5,0, 6,0, 7,0, 8,0, 9,0, 12,0, objs))  ot
from
(select owner#, type#, count(*) objs from sys.obj$ group by owner#, type#)  o,
sys.user$  u
where
  u.user# = o.owner#
group by
  u.name
order by decode(u.name, 'SYS', 1, 'SYSTEM', 2,'PUBLIC',3, 4), u.name
/
clear breaks  
clear computes

R00 – 3 Basic DBA Scripts for Database Uptime, Logmode and Max Sessions

Posted on by 
prompt Database Name and Archive Mode
prompt ##################################
select name,log_mode from v$database
/
prompt
prompt Database Uptime
prompt ####################################
select
  trunc(sysdate - startup_time)  Days,
  trunc(mod(24 * (sysdate - startup_time), 24))  Hours,
  round(mod(60 * 24 * (sysdate - startup_time), 60))  Minutes
from
  sys.v_$instance
/
prompt
prompt Max. Sessions 
prompt ###########################
select 'Máximum number of sessions since last STARTUP is: '||sessions_highwater "."
from v$license 
/