Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com 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 .
Thread Rating:
  • 8 Vote(s) - 3.25 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query to Check Tablespace Size and frees pace in Oracle Database
06-25-2015, 08:07 PM,
#1
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

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  Oracle HCM Cloud Interview Questions MM Ifan 1 50,204 06-06-2021, 12:16 AM
Last Post: zaidmd
  Oracle Implementation Project Plan admin 35 140,880 04-29-2021, 06:59 AM
Last Post: Cwahsayz
  Oracle ERP Implementation Project Schedule based on OUM Kashif Manzoor 7 25,548 03-31-2021, 11:45 PM
Last Post: haydara
  Oracle Financials - Data Flow From SLA to GL admin 8 79,305 12-17-2020, 11:26 PM
Last Post: cok3cl
  Oracle Inventory Module End User Training Material James Robert 1 29,385 08-27-2020, 08:59 PM
Last Post: shahid
  Oracle Inventory Miscellaneous Transactions Mark Donald 1 27,956 08-20-2020, 05:34 PM
Last Post: shahid
  Oracle Fusion Financials General Ledger Essentials M Irfan 5 92,144 08-20-2020, 05:32 PM
Last Post: shahid
  Oracle AIM - RD050 Business Requirement Documents admin 10 107,029 06-04-2020, 05:28 AM
Last Post: Shashi
  Oracle AIM - BR100 Documents admin 37 242,936 05-13-2020, 06:53 PM
Last Post: amjad.orafunctional
  AIM Vs OUM - Application Implementation Methodology vs Oracle Unified Methodology Kashif Manzoor 7 43,125 12-11-2019, 10:50 PM
Last Post: abuzeids



Users browsing this thread: 1 Guest(s)
Open Tech Talks Get Oracle Tutorials, Tips! Post a Question!