Scripts for checking Memory space in a Database for Space Maintenance

# Total Size and Free Size of Database.

(Database Size, Free space) -

SQL> 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;

# Script for checking Total tablespace, Used space and Free tablespace in MB.

(TABLESPACE, TOTAL_SPACE,  FREE_SPACE,  TOTAL_SPACE_MB, USED_SPACE_MB)- 

SQL> FREE_SPACE_MB,  PCT_FREE

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,

      ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB

      FROM dba_data_files

      GROUP BY tablespace_name) df,

     (SELECT tablespace_name, SUM (bytes) FREE_SPACE,

       ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB

       FROM dba_free_space

       GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

# To Check Datafiles used and Free Space:

(FILE_NAME, TABLESPACE_NAME ALLOCATED_MB, USED_MB FREE_SPACE_MB)-

SQL> 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;

# Script for checking Total tablespace, Used space and Free tablespace in KB.

(TABLESPACE, FILE_NAME, TABLESPACE TOTAL KB, TOTAL FREE KBYTES,  NEXT_FREE)-

SQL> set linesize 132

set pagesize 60

break on tablespace_name skip 1

col tablespace_name format a15

col file_name format a50

col tablespace_kb heading 'TABLESPACE|TOTAL KB'

col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM (fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE

FROM sys.dba_free_space fs, sys.dba_data_files dd

WHERE dd.tablespace_name = fs.tablespace_name

AND dd.file_id = fs.file_id

GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024

ORDER BY dd.tablespace_name, dd.file_name;

# Script for checking Current Size tablespace, Current Used tablespace and Current Free tablespace in MB and also in percentage(%).

(TABLESPACE_NAME, CUR_USE_MB,  CUR_SZ_MB, CUR_PCT_FULL, FREE_SPACE_MB,  MAX_SZ_MB,   PCT_FREE)-

SQL> set linesize 121

SELECT tablespace_name, ROUND( SUM (total_mb)- SUM (free_mb)) CUR_USE_MB, ROUND( SUM (total_mb)) CUR_SZ_MB,

ROUND(( SUM (total_mb)- SUM (free_mb))/ SUM (total_mb)*100) CUR_PCT_FULL, ROUND( SUM (max_mb) - ( SUM (total_mb)- SUM (free_mb))) FREE_SPACE_MB,

ROUND( SUM (max_mb)) MAX_SZ_MB, ROUND(( SUM (total_mb)- SUM (free_mb))/ SUM (max_mb)*100) PCT_FREE

FROM (

  SELECT tablespace_name, SUM (bytes)/1024/1024 FREE_MB,

  0 TOTAL_MB, 0 MAX_MB

  FROM dba_free_space

  GROUP BY tablespace_name

  UNION

  SELECT tablespace_name, 0 CURRENT_MB,

  SUM (bytes)/1024/1024 TOTAL_MB,

  SUM ( DECODE (maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB

  FROM dba_data_files

  GROUP BY tablespace_name)

GROUP BY tablespace_name;

Alternative way:

SQL> 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;

Another Alternative Way:

SQL> Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

# Statement For Tablespace Management in terms of File.

(TABLESPACE_NAME, DATAFILE_SZ, ALLOC_SIZE, PCT_USED, FREE_SPACE,  MAXNEXT, DEFINITSZ, DEFNEXTSZ)-

SQL> col tablespace_name format a15

col alloc_size format 999.999

col pct_used format 999.999

col free_space format 999.999

col maxnext format 999.999

col definitsz format 999.999

col defnextsz format 999.999

SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,

(b.alloc_size)/a.datafile_sz*100 PCT_USED,

(a.datafile_sz-b.alloc_size) FREE_SPACE,

b.next_extent/1024/1024 MAXNEXT,

a.initial_extent/1024/1024 DEFINITSZ,

a.next_extent/1024/1024 DEFNEXTSZ

FROM (

  SELECT a.tablespace_name, SUM (b.bytes)/1024/1024

  DATAFILE_SZ, a.initial_extent, a.next_extent

  FROM dba_tablespaces a, dba_data_files b

  WHERE a.tablespace_name = b.tablespace_name

  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,

    (

  SELECT a.tablespace_name, SUM (c.bytes)/1024/1024

  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT

  FROM dba_tablespaces a, dba_segments c

  WHERE a.tablespace_name = c.tablespace_name

  GROUP BY a.tablespace_name) B

WHERE a.tablespace_name = b.tablespace_name (+)

ORDER BY 1;

Alternative Way:

SQL> 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;

# Script to calculate Minimum Size of Data Files(FILENAME, MIN_SIZE)-

SQL> SELECT SUBSTR(f.file_name,1,70) FILENAME,

MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE

FROM dba_extents e, dba_data_files f

WHERE e.file_id = f.file_id

GROUP BY f.file_name;

# To check Tablespace free space-
SQL> SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

#To check Tablespace by datafile:
SQL> SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE

# To check Growth rate of  Tablespace in days and MB

(DAYS, TSNAME, CUR_SIZE_MB, USEDSIZE_MB)-

Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.
SQL> 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;

# List all Tablespaces with free space < 10% or full space> 90% 

(TABLESPACE_NAME,  TOT_SIZE,  TOT_FREE, PCT_FREE,  MIN_ADD)-

SQL> Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;

# Script to find all object Occupied space for a Tablespace.

SQL> Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments
where TABLESPACE_NAME = 'SDH_HRMS_DBF'
group by OWNER, SEGMENT_NAME;

# Which schema are taking how much space in terms of objects and memory(MB).

(Owner, Objects,  size MB)-

SQL> 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 the property name of the tablespaces in terms of DEFAULT name.

(PROPERTY_NAME, PROPERTY_VALUE, DESCRIPTION) -

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

# To check the Default Tablespace of the users.

(USERNAME, TEMPORARY_TABLESPACE, DEFAULT_TABLESPACE)-

SQL> Select username,temporary_tablespace,default_tablespace from dba_users where username='HR';

# To know Default Tablespace for All User.

(USERNAME, TEMPORARY_TABLESPACE, DEFAULT_TABLESPACE)-

SQL> Select default_tablespace,temporary_tablespace,username from dba_users;

# To check Used free space in Temporary Tablespace.

(TABLESPACE, MB_TOTAL,  MB_USED    MB_FREE) -

SQL> 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.

SQL> 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.

SQL> 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;

# To find used space of datafiles.

SQL> SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;

# IO status of all of the datafiles in database.

SQL> 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#;

#Sort (Temp) space used by Session.
SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;

# Sort (Temp) Space Usage by Statement.

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;