Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple.
Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips by upgrading to "Premium Membership".
Oracle Training

Thread Rating:
  • 5 Vote(s) - 2.8 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query to Check Tablespace Size and frees pace in Oracle Database
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

            ,TO_CHAR(SUM(BYTES)/(1024*1024)) "TOTAL"
       ) DDF,
       ,TO_CHAR(SUM(BYTES)/(1024*1024)) "FREESPACE"
       FROM DBA_FREE_SPACE      
       ) DFS
       where DDF.tablespace = DFS.tablespace
     order by DFS.tablespace;

2.    To check the each datafile size and freespace.

       (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;

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
(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
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;

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"
(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 ;
Thanks given by:

Possibly Related Threads...
Thread Author Replies Views Last Post
  Oracle E-business Suite R-12 - Oracle Payable User Manual admin 1 11,603 07-08-2017, 12:12 AM
Last Post: Spartan117
  Oracle E-Business Suite on Oracle Cloud admin 0 992 06-03-2017, 05:38 AM
Last Post: admin
  Oracle E-Business Suite on Oracle Cloud Kashif Manzoor 0 2,885 04-07-2016, 10:20 PM
Last Post: Kashif Manzoor
  How to Increase the size of a Linux LVM by expanding the virtual machine disk Kashif Manzoor 0 1,601 08-23-2015, 07:19 PM
Last Post: Kashif Manzoor
  Query- How to check the Oracle database version Zishan Ali 0 1,623 08-16-2015, 11:35 PM
Last Post: Zishan Ali
  Recover database without archivelogs Zishan Ali 0 1,449 08-15-2015, 07:42 AM
Last Post: Zishan Ali
  SQL Query to get the username in Fusion Applications Zishan Ali 0 1,501 08-06-2015, 05:11 PM
Last Post: Zishan Ali
  Query Oracle iRecruitment candidates basic information Ahmad Mujeeb 0 1,587 07-02-2015, 08:21 PM
Last Post: Ahmad Mujeeb
  Query to extract Oracle Requisitions Details Ahmad Mujeeb 0 4,125 06-18-2015, 04:24 PM
Last Post: Ahmad Mujeeb
  Oracle 11gR2 database upgrade in Apps R12.1.1 Sara Khan 0 2,198 12-20-2013, 05:49 AM
Last Post: Sara Khan

Users browsing this thread: 1 Guest(s)
Get Premium Membership Get Oracle Tutorials, Tips! Post a Question!