Database Administration (MOSC)

MOSC Banner

Calculating used and free space at tablespace level

edited May 24, 2011 2:44PM in Database Administration (MOSC) 8 commentsAnswered
 
Hi,

We are trying to determine the best possible way to calculate the actual and free space at table space level
using 2 following methods. All the data files are auto extensible

METHOD-A
========


SQL> select tablespace_name,((used_space*8192)/1024/1024/1024) used_gb,
  2  ((tablespace_size*8192)/1024/1024/1024) size_gb,used_percent
  3  from dba_tablespace_usage_metrics where tablespace_name like '%DATA%'
  4  order by 4 desc;

TABLESPACE_NAME                USED_GB SIZE_GB USED_PERCENT
------------------------------ ------- ------- ------------
ALL_DATA                         43.16   53.96        79.99
IMG_DATA                          9.17   13.73        66.77
EXP_DATA                          3.13    6.00        52.13
ALL_INDEX                         7.61   20.00        38.03


METHOD-B
========

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 AXCS,
  2  sum(maxbytes)/1024/1024/1024 AXMS,
  3  (sum(maxbytes)/1024/1024/1024)-(sum(bytes)/1024/1024/1024) AX
  4  ((sum(bytes)/1024/1024/1024)/(sum(maxbytes)/1024/1024/1024))*100 AXPU

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center