Correct script for TS size calculation
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
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
0