Oracle Queries for Database Space Management

Database_spacemgmt_1.sql

-- Query to get index details
SELECT SEGMENT_NAME,SEGMENT_TYPE, TABLESPACE_NAME, ROUND((BYTES/1024/1024/1024),3) "SIZE (GB)", BLOCKS, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
AND OWNER='<OWNER>'
AND segment_name = 'INDEX-NAME'
ORDER BY SEGMENT_NAME;

db_space_10.sql

-- Query to find Schema(s) using data file space
SET pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999

SELECT  obj.owner AS "Owner",
        obj_cnt AS "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) AS seg_size
       FROM dba_segments 
       GROUP BY owner) seg
WHERE     obj.owner  = seg.owner(+)
ORDER BY 3 DESC ,2 DESC, 1 ASC;

db_space_2.sql

-- Query to check Table size
SELECT SEGMENT_NAME,SEGMENT_TYPE, TABLESPACE_NAME, ROUND((BYTES/1024/1024/1024),3) "SIZE", BLOCKS, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
AND OWNER='<OWNER>'
AND segment_name = '<TABLE-NAME>'
ORDER BY SEGMENT_NAME;

db_space_3.sql

-- Query to find available space of Tablespace
SELECT ROUND(SUM(BYTES/1024/1024/1024),3) "FREE SIZE(GB)" 
FROM DBA_FREE_SPACE 
WHERE UPPER(TABLESPACE_NAME) LIKE '%<TABLESPACE-NAME>%';

db_space_4.sql

-- Query to find data file size
SELECT ROUND(SUM(BYTES/1024/1024/1024),3) "TOTAL SIZE(GB)"FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME='<TABLE-SPACE NAME>';

db_space_5.sql

-- Disk Space/IO Block distribution related query
SELECT io.OBJECT_NAME o_name, io.OBJECT_OWNER o_owner, io.OBJECT_TYPE o_type,
       mf.FILE_NAME, me.ELEM_NAME, io.DEPTH,
      (SUM(io.CU_SIZE * (io.NUM_CU - DECODE(io.PARITY_PERIOD, 0, 0,
                         TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size
FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf
WHERE io.OBJECT_NAME =  'TABLE-NAME'
AND   io.OBJECT_OWNER = '<OWNER>'
AND   io.OBJECT_TYPE =  'TABLE'
AND   me.ELEM_IDX = io.ELEM_IDX
AND   mf.FILE_MAP_IDX = io.FILE_MAP_IDX
GROUP BY io.ELEM_IDX, io.FILE_MAP_IDX, me.ELEM_NAME, mf.FILE_NAME, io.DEPTH, io.OBJECT_NAME, io.OBJECT_OWNER, io.OBJECT_TYPE
ORDER BY io.DEPTH

db_space_6.sql

-- QUERY TO FIND TEMP TABLESPACE USAGE
SELECT A.tablespace_name "TABLESPACE", 
       D.mb_total "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;

db_space_7.sql

-- TO PRODUCE STATISTICS ABOUT FREE EXTENTS AND COALESCING ACTIVITY FOR EACH TABLESPACE IN THE DATABASE
SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
   COUNT(*)    "PIECES",
   MAX(BLOCKS) "MAXIMUM",
   MIN(BLOCKS) "MINIMUM",
   CEIL(AVG(BLOCKS)) "AVERAGE",
   SUM(BLOCKS) "TOTAL",
   SUM(BYTES)FROM DBA_FREE_SPACE
WHERE LOWER(TABLESPACE_NAME) LIKE '%TABLESPACE-NAME%'
GROUP BY TABLESPACE_NAME, FILE_ID;

db_space_8.sql

-- Query to find Tablespace usage
SELECT SUBSTR (df.NAME, 1, 40) as FILE_NAME, 
  df.bytes / 1024 / 1024 AS ALLOCATED_MB,  
  ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) as USED_MB,  
  NVL (SUM (dfs.bytes) / 1024 / 1024, 0) as 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  
ORDER BY file_name;

db_space_9.sql

-- Query to find BackupSet Sizes
select ctime "Date"  
  , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type  
  , bsize "Size MB"  
from (select trunc(bp.completion_time) ctime  
          , backup_type  
          , round(sum(bp.bytes/1024/1024),2) bsize  
      from v$backup_set bs, v$backup_piece bp  
      where bs.set_stamp = bp.set_stamp  
      and bs.set_count  = bp.set_count  
      and bp.status = 'A'  
      group by trunc(bp.completion_time), backup_type
     )
order by 1, 2;