Database Administration (MOSC)

MOSC Banner

Correct script for TS size calculation

edited Aug 6, 2010 12:50AM in Database Administration (MOSC) 4 commentsAnswered
Hi,
Previously I was joining the dba_data_files and dba_free_space to know the space used by tablespaces in my database. but it was not giving me the correct picture because of
for some datafile autoextensible ='YES' and maxbytes >0 , so now I am using the following sript for the tablespace size calculation :

SELECT   TABLESPACE_NAME,
         round(SUM (DECODE (autoextensible, 'NO', bytes, maxbytes))/ 1048576/1024,2) GBYTES ,
         round(sum(BYTES) / 1048576/1024,2) GB_used,
         round(SUM (DECODE (autoextensible, 'NO', bytes, maxbytes)-BYTES )/ 1048576/1024,2) GB_free ,
         trunc((SUM (DECODE (autoextensible, 'NO', bytes, maxbytes))-sum(BYTES))
               /SUM (DECODE (autoextensible, 'NO', bytes, maxbytes))
              *100,2) pct_free
FROM   dba_data_files

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