Find Used Space on SAP Oracle Database - SQL Query / Script

This query or script will display the following output: Very handy to manage database storage

  • Total Database Size
  • Used Space on Database
  • Free Space on Database


Run this from your SQL > 

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Example Output: 

Database Size                Used space                Free space
-------------------- -------------------- --------------------

457 GB                           343 GB                    114 GB
Share this article :

Post a Comment

 
Copyright © 2011. SAP HANA TUTORIALS FREE - S/4 HANA - All Rights Reserved