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;
