Scripts To Monitor Tablespaces and datafiles

Scripts To Monitor Tablespaces and datafiles

To check the tablespace in detail.

set linesize 160
set pagesize 300
select distinct
a.tablespace_name,
SUM(a.bytes)/1024/1024 "Used Size MB",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Max Size Mb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Total Used MB",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Total Free MB",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "Used Percentage"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

Note:
Tablespace usage, Free sizes will play a role when datafiles are enabled AUTOEXTENSIBLE.

Another script to check the tablespace in detail.

set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

To check the free and used space:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
 FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

To check the tablespace used in GB's

set linesize 100
set pagesize 100
col file_name for a60

select
    a.tablespace_name,
    round(SUM(a.bytes)/(1024*1024)) CURRENT_MB,
    round(SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024), GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024))))) MAX_MB,
    round((SUM(a.bytes)/(1024*1024) - c.Free/1024/1024)) USED_MB,
    round((SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024), GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024)))) - (SUM(a.bytes)/(1024*1024) - round(c.Free/1024/1024))),2) FREE_MB,
    round(100*(SUM(a.bytes)/(1024*1024) - round(c.Free/1024/1024))/(SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024),GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024)))))) USED_PCT
from
    dba_data_files a,
    (
        SELECT
            d.tablespace_name ,sum(nvl(c.bytes,0)) Free
        FROM
            dba_tablespaces d,
            DBA_FREE_SPACE c
        WHERE
            d.tablespace_name = c.tablespace_name(+)
            --AND d.contents='PERMANENT'
            --AND d.status='ONLINE'
            group by d.tablespace_name
    ) c
WHERE
    a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
order by 6;


To check Growth rate of Tablespaces

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
 ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
 max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
 FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
 DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname, days;


Which schema are taking how much space:

Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
 (select owner, ceil(sum(bytes)/1024/1024) seg_size  from dba_segments group by owner) seg
  where obj.owner  = seg.owner(+)
  order    by 3 desc ,2 desc, 1;


To Check Default Temporary Tablespace Name:

Select * from database_properties where PROPERTY_NAME like '%DEFAULT%';


To know default and Temporary Tablespace for particualr User:

Select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS';


To know Default Tablespace for All User:

Select default_tablespace,temporary_tablespace,username from dba_users;
To Check Datafiles used and Free Space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;


To check Used free space in Temporary Tablespace:

SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header GROUP  BY tablespace_name;
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         ( SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Who is using which UNDO or TEMP segment:

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x
WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = 'db_block_size';

Who is using the Temp Segment:

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a,
sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name  = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Total Size and Free Size of Database:

Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;


To find used space of datafiles:

SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
IO status of all of the datafiles in database:
WITH total_io AS
     (SELECT SUM (phyrds + phywrts) sum_io
        FROM v$filestat)
SELECT   NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,
         phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio
    FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c
   WHERE a.file# = b.file#
ORDER BY a.file#;
Displays Smallest size the datafiles can shrink to without a re-organize.
SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes
FROM   dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
        FROM   dba_extents
        GROUP by file_id) b
        WHERE  a.file_id = b.file_id
        ORDER BY a.tablespace_name, a.file_name;

Scripts to Find datafiles increment details:

Select  SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point,tn.name   tabsp_name,fn.name   file_name,
ddf.bytes/1024/1024 cur_size, decode(fex.maxextend,
NULL,ddf.bytes/1024/1024,fex.maxextend*tn.blocksize/1024/1024) max_size,
nvl(fex.maxextend,0)*tn.blocksize/1024/1024 - decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024)   unallocated,nvl(fex.inc,0)*tn.blocksize/1024/1024  inc_by
from  sys.v_$dbfile fn,    sys.ts$  tn,    sys.filext$ fex,    sys.file$  ft,    dba_data_files ddf
where    fn.file# = ft.file# and  fn.file# = ddf.file_id
and    tn.ts# = ft.ts# and    fn.file# = fex.file#(+)
order by 1;