Additional SQL for Space usage for Tablespaces and Segments

Main Tables List
Get block size
Get segments per Tablespace
View top 100 Tablespaces
View top 100 Segments
History of tablespace usage.
Get space usage for several Schemas, per Tablespace


=========================
Main Tables List
=========================
DBA_SEGMENTS
DBA_DATA_FILES
DBA_TABLESPACES
DBA_TABLESPACE_USAGE_METRICS
DBA_FREE_SPACE
DBA_FREE_SPACE_COALESCED

=========================
Get block size
=========================
In this example - it is 8192 bytes
SQL> col name for A30
SQL> col value for A30
SQL> SELECT name, value from V$PARAMETER WHERE name IN ('db_block_size');
NAME                           VALUE
------------------------------ ------------------------------
db_block_size                  8192
SQL> 

Entries in DBA_TABLESPACE_USAGE_METRICS are listed in blocks

=========================
Get segments per Tablespace
=========================
SELECT SEGMENT_NAME,
       SUM(bytes)/1024/1024 AS Mb
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = 'MY_TBS'
GROUP BY SEGMENT_NAME

=========================
View top 100 Tablespaces from DBA_SEGMENTS
=========================
SELECT owner, 
       tablespace_name, 
       TO_CHAR(ROUND(SUM(bytes)/1024/1024,0),'999,999') "Size in Mb"
FROM DBA_SEGMENTS
WHERE  owner NOT IN( 'SYS%','UNDOTBS', 'SYSTEM', 'SYSAUX','OUTLN','WMSYS')
  AND ROWNUM < 101
GROUP BY owner, tablespace_name
ORDER BY SUM(bytes)/1024/1024 DESC

=========================
View top 100 Segments from DBA_SEGMENTS
=========================
SELECT owner, 
       tablespace_name, 
       segment_name, 
       partition_name, 
       segment_type, 
       TO_CHAR(ROUND(SUM(bytes)/1024/1024,0),'999,999') "Size in Mb"
FROM   DBA_SEGMENTS
WHERE  owner NOT IN( 'SYS','UNDOTBS', 'SYSTEM', 'SYSAUX', 'OUTLN','WMSYS')
  AND  ROWNUM < 101
GROUP BY owner, tablespace_name, segment_name, partition_name, segment_type
ORDER BY SUM(bytes)/1024/1024 DESC

=========================
View top 100 used Tablespaces from DBA_TABLESPACE_USAGE_METRICS
=========================
SELECT *
FROM (
SELECT TBS_METRICS.tablespace_name,
       ROUND(TBS_METRICS.used_space*8192/1024/1024) "Used Space Mb",
       ROUND(TBS_METRICS.tablespace_size*8192/1024/1024) "Total Size Mb",
       ROUND(TBS_METRICS.used_percent) "Used Percent",
       ROUND(100-TBS_METRICS.used_percent) "Free Percent"
FROM  DBA_TABLESPACE_USAGE_METRICS TBS_METRICS
ORDER BY ROUND(TBS_METRICS.used_percent) DESC
) ALL_TABLESPACES
WHERE ROWNUM < 101

=========================
This is identical to more expensive query from DBA_DATA_FILES
=========================
SELECT tablespace_name,
       ROUND(SUM(MAXBYTES)/1024/1024) AS "Max Bytes Mb",  
       ROUND(SUM (USER_BYTES)/1024/1024) AS "User Bytes Mb",
       ROUND((SUM(MAXBYTES)-SUM(USER_BYTES))/1024/1024) "Free Mb"
FROM DBA_DATA_FILES
WHERE tablespace_name NOT IN( 'SYS%','UNDOTBS', 'SYSTEM', 'SYSAUX')
GROUP BY TABLESPACE_NAME
ORDER BY 3

=========================
History of tablespace usage. V$DATAFILE
=========================
SELECT * FROM V$DATAFILE
WHERE file# IN
  (SELECT file_id FROM DBA_DATA_FILES
    WHERE tablespace_name = 'MY_TABLESPACE'
  )
ORDER BY creation_time DESC;

=========================
Get space usage for several Schemas, per Tablespace
=========================
- Step 1 - Per Database, get the list of Tablespaces to monitor: - MONITOR_PKG_TBS_LIST_VW.
- Step 2 - Per Tablespace list, get the space usage: MNTR_PKG_FREE_SPACE_DB1_VW
- Step 3 - Select from MNTR_PKG_FREE_SPACE_DB1_VW,optionally apply filter.
- Step 4 - Per each reported tablespace, get the top Segments.

MONITOR_PKG_TBS_LIST_VW
CREATE OR REPLACE VIEW MONITOR_PKG_TBS_LIST_VW AS
-- DB1
  SELECT 'DB1' as DB_NAME, tablespace_name
   FROM DBA_TABLESPACES@DB1_LINK DBA_TABLESPACES
  WHERE tablespace_name NOT IN ('SYSAUX', 'TEMP01', 'TEMPORARY', 'UNDOTBS', 'WORKAREA')
UNION ALL -- DB2
  SELECT 'DB2' as DB_NAME, tablespace_name
    FROM DBA_TABLESPACES@DB2_LINK DBA_TABLESPACES
   WHERE tablespace_name NOT IN ('SYSAUX', 'TEMP01', 'TEMPORARY', 'UNDOTBS', 'WORKAREA')
UNION ALL -- DB3
  SELECT 'DB3' as DB_NAME, tablespace_name
    FROM DBA_TABLESPACES@DB3_LINK DBA_TABLESPACES
   WHERE tablespace_name LIKE 'PARTITIONED_TB_MYTB1_%'
     AND (SUBSTR(tablespace_name,LENGTH(tablespace_name)-5) >= TO_CHAR(SYSDATE,'YYYYMM')OR (SUBSTR(tablespace_name,LENGTH(tablespace_name)-5) = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') AND TO_NUMBER(TO_CHAR(SYSDATE,'DD')) < 8))
   UNION ALL
   SELECT 'DB3' as DB_NAME, tablespace_name
    FROM DBA_TABLESPACES@DB3_LINK DBA_TABLESPACES
    WHERE tablespace_name LIKE 'PARTITIONED_TB_MYTB2_%'
      AND (SUBSTR(tablespace_name,LENGTH(tablespace_name)-5) >= TO_CHAR(SYSDATE,'YYYYMM')OR (SUBSTR(tablespace_name,LENGTH(tablespace_name)-5) = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') AND TO_NUMBER(TO_CHAR(SYSDATE,'DD')) < 8))

MNTR_PKG_FREE_SPACE_DB1_VW
CREATE OR REPLACE VIEW MNTR_PKG_FREE_SPACE_DB1_VW AS
SELECT MAX_BLOCKS.tablespace_name          AS tablespace_name,
       MAX_BLOCKS.user_blocks              AS max_blocks,
       USED_SEGMENTS.used_blocks           AS used_blocks,
       DBA_FREE_SPACE_BLOCKS.dba_free_blocks               AS dba_free_blocks,
       MAX_BLOCKS.user_blocks - USED_SEGMENTS.used_blocks  AS free_blocks,
       ROUND(((MAX_BLOCKS.user_blocks - USED_SEGMENTS.used_blocks  )/MAX_BLOCKS.user_blocks)*100)            AS free_pct,
       LAST_EXTENT.max_extents_blocks      AS top_extent_blocks,
       LAST_EXTENT.max_extents_blocks * 2  AS two_blocks_size,
       MAX_BLOCKS.user_blocks-(LAST_EXTENT.max_extents_blocks * 2)   
                                           AS space_after_allocate_2_extents
 FROM
    (
    SELECT DBA_FREE_SPACE.tablespace_name AS tablespace_name,
           SUM(DBA_FREE_SPACE.blocks)     AS dba_free_blocks
      FROM DBA_FREE_SPACE@DB1_LINK DBA_FREE_SPACE
  GROUP BY DBA_FREE_SPACE.tablespace_name
     )DBA_FREE_SPACE_BLOCKS,
     (
    SELECT database_name,
           tablespace_name,
           max_extents_bytes,
           max_extents_blocks
      FROM TABLESPACE_MAX_EXTENTS
             WHERE database_name = 'DB1'
     )LAST_EXTENT,
     (
    SELECT DBA_SEGMENTS.tablespace_name, SUM (blocks) AS used_blocks
      FROM DBA_SEGMENTS@DB1_LINK DBA_SEGMENTS
  GROUP BY DBA_SEGMENTS.tablespace_name
     )USED_SEGMENTS,
     (
    SELECT MY_DATA_FIELS.tablespace_name,
           SUM(MY_DATA_FIELS.user_blocks) user_blocks
      FROM (
           SELECT tablespace_name, file_name, MAX(blocks) AS user_blocks
             FROM(
                 SELECT INNER_DBA_DATA_FILES.tablespace_name AS tablespace_name,                           INNER_DBA_DATA_FILES.file_name AS file_name,
                        user_blocks AS blocks, 
                        'user_blocks' AS type
                   FROM DBA_DATA_FILES@DB1_LINK INNER_DBA_DATA_FILES
              UNION ALL
                 SELECT INNER_DBA_DATA_FILES.tablespace_name AS tablespace_name,
                        INNER_DBA_DATA_FILES.file_name AS file_name,
                        maxblocks AS blocks,
                        'maxblocks' AS type
                   FROM DBA_DATA_FILES@DB1_LINK  INNER_DBA_DATA_FILES
               ORDER BY  tablespace_name, file_name
                 )
             GROUP BY tablespace_name, file_name
            ) MY_DATA_FIELS
     WHERE 1=1
     GROUP BY MY_DATA_FIELS.tablespace_name
     ORDER BY MY_DATA_FIELS.tablespace_name
     )MAX_BLOCKS,
      MONITOR_PKG_TBS_LIST_VW
WHERE 1 = 1
 AND MONITOR_PKG_TBS_LIST_VW.tablespace_name = MAX_BLOCKS.tablespace_name
 AND MONITOR_PKG_TBS_LIST_VW.tablespace_name =
     DBA_FREE_SPACE_BLOCKS.tablespace_name
 AND MONITOR_PKG_TBS_LIST_VW.tablespace_name = LAST_EXTENT.tablespace_name
 AND MONITOR_PKG_TBS_LIST_VW.tablespace_name = USED_SEGMENTS.tablespace_name
 AND MONITOR_PKG_TBS_LIST_VW.DB_NAME = 'DB1';

Select from MNTR_PKG_FREE_SPACE_DB1_VW,optionally apply filter.   
SELECT *
 FROM MNTR_PKG_FREE_SPACE_DB1_VW
WHERE 1=1
  AND free_pct < 10;