+- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com)
+-- Forum: Oracle Applications (http://www.oraerp.com/forum-39.html)
+--- Forum: Oracle Tutorials, Articles, eBooks, Tools & Tips (http://www.oraerp.com/forum-12.html)
+--- Thread: Query to Check Tablespace Size and frees pace in Oracle Database (/thread-71437.html)
Query to Check Tablespace Size and frees pace in Oracle Database - Ahmad Mujeeb - 06-25-2015
1. To check the consolidated tablespace size and freespace:
set pagesize 1000;
set linesize 250;
column total format a20
column freespace format a20
column tablespace_name format a25
column file_name format a55
SELECT DFS.TABLESPACE,TOTAL,FREESPACE,TOTAL-FREESPACE "USED",((TOTAL-FREESPACE)*100)/TOTAL "PERCENTAGE USED"
from
(SELECT TABLESPACE_NAME "TABLESPACE"
,TO_CHAR(SUM(BYTES)/(1024*1024)) "TOTAL"
FROM DBA_DATA_FILES
--WHERE TABLESPACE_NAME='INVENTORY_UNIT'
GROUP BY TABLESPACE_NAME
) DDF,
(SELECT TABLESPACE_NAME "TABLESPACE"
,TO_CHAR(SUM(BYTES)/(1024*1024)) "FREESPACE"
FROM DBA_FREE_SPACE
--WHERE TABLESPACE_NAME='INVENTORY_UNIT'
GROUP BY TABLESPACE_NAME
) DFS
where DDF.tablespace = DFS.tablespace
order by DFS.tablespace;
2. To check the each datafile size and freespace.
SELECT TABLESPACE_NAME,
file_name,
(bytes/1024)/1024 "total(MB)",
(free/1024)/1024 "free(MB)" ,
((free/1024)/1024) / ((bytes/1024)/1024)*100 "%Free"
FROM dba_data_files a,
(select file_id, sum(bytes) free from dba_free_space group by file_id) b
WHERE a.file_id=b.file_id(+) ORDER BY TABLESPACE_NAME;
3.
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;
4.
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;