SQL Query used to check the Table spaces usage and allocation:
Below Lines are to set the report format:
Query used to check the Table space Usage, Free and Allocation
SQL>
SELECT * FROM( SELECT
c.tablespace_name,
ROUND(a.bytes/(1024*1024),2) MB_Allocated,
ROUND((a.bytes-b.bytes)/(1024*1024),2) MB_Used,
ROUND(b.bytes/(1024*1024),2) MB_Free,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM
(SELECT tablespace_name, SUM(ddf.bytes) bytes FROM sys.DBA_DATA_FILES ddf GROUP BY tablespace_name) a,
(SELECT ddf.tablespace_name, NVL(SUM(dfs.bytes),0) bytes FROM sys.DBA_DATA_FILES ddf, sys.DBA_FREE_SPACE dfs WHERE ddf.tablespace_name = dfs.tablespace_name AND ddf.file_id = dfs.file_id GROUP BY ddf.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE
a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name )
WHERE
tot_Pct_Used >=0
ORDER BY
tablespace_name
/
SQL> SET LINe 200
SQL> SET PAGES 100
SQL> col STATUS for a10
SQL> col ACTUAL_START_DATE for a35
SQL> col RUN_DURATION for a25
SQL> col file_name for a60
Below are the optional things just to get SUM at end:
SQL> break on report
SQL> compute SUM of MB_FREE ON REPORT
SQL> compute sum of MB_ALLOCATED ON report
SQL> compute sum of MB_USED ON REPORT
Query used to check the Table space Usage, Free and Allocation
SQL>
SELECT * FROM( SELECT
c.tablespace_name,
ROUND(a.bytes/(1024*1024),2) MB_Allocated,
ROUND((a.bytes-b.bytes)/(1024*1024),2) MB_Used,
ROUND(b.bytes/(1024*1024),2) MB_Free,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM
(SELECT tablespace_name, SUM(ddf.bytes) bytes FROM sys.DBA_DATA_FILES ddf GROUP BY tablespace_name) a,
(SELECT ddf.tablespace_name, NVL(SUM(dfs.bytes),0) bytes FROM sys.DBA_DATA_FILES ddf, sys.DBA_FREE_SPACE dfs WHERE ddf.tablespace_name = dfs.tablespace_name AND ddf.file_id = dfs.file_id GROUP BY ddf.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE
a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name )
WHERE
tot_Pct_Used >=0
ORDER BY
tablespace_name
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Comments
Post a Comment