Allocated vs free vs used space on a TABLESPACE

[kozlow]

I created the following query to give me the space allocated to a tablespace, the Free Space and then get the used and %. It is not giving me the correct results. Can I get another set of eyes and see what I am missing...

Here are the queries:

select dba_data_files.tablespace_name, round(sum(dba_data_files.bytes/1048576)) Allocated_MEG from dba_data_fi
les where tablespace_name = 'FEMX' group by tablespace_name;

select dba_data_files.tablespace_name, round(sum(dba_data_files.bytes/1048576)) Allocated_MEG, round(sum(dba_f
ree_space.bytes/1048576)) Free_Meg,
round(sum(dba_data_files.bytes/1048576 - dba_free_space.bytes/1048576)) Used_Meg,
round(sum(dba_free_space.bytes/1048576) / sum(dba_data_files.bytes/1048576)*100) Percent_Free,
round(sum(dba_data_files.bytes/1048576 - dba_free_space.bytes/1048576) / sum(dba_data_files.bytes/1048576)*100
) Percent_Used
from dba_data_files, dba_free_space where dba_data_files.tablespace_name = 'FEMX' and dba_data_files.tablespa
ce_name = dba_free_space.tablespace_name
group by dba_data_files.tablespace_name;

Here is the output.....
TABLESPACE_NAME ALLOCATED_MEG
------------------------------ -------------
FEMX 20


TABLESPACE_NAME ALLOCATED_MEG FREE_MEG USED_MEG PERCENT_FREE PERCENT_USED
------------------------------ ------------- ---------- ---------- ------------ ------------
FEMX 40 6 34 15 85

I did this against one tablespace so you can easily see the difference.... The 20Meg matches the number I get when I do it by hand...

Here is the Production Version....

select dba_data_files.tablespace_name, round(sum(dba_data_files.bytes/1048576)) Allocated_MEG, round(sum(dba_f
ree_space.bytes/1048576)) Free_Meg,
round(sum(dba_data_files.bytes/1048576 - dba_free_space.bytes/1048576)) Used_Meg,
round(sum(dba_free_space.bytes/1048576) / sum(dba_data_files.bytes/1048576)*100) Percent_Free,
round(sum(dba_data_files.bytes/1048576 - dba_free_space.bytes/1048576) / sum(dba_data_files.bytes/1048576)*100
) Percent_Used
from dba_data_files, dba_free_space where dba_data_files.tablespace_name = dba_free_space.tablespace_name
group by dba_data_files.tablespace_name;

Thanks....

 

[SantaMufasa]

Kozlow,

First, welcome to the Oracle Forums.

Second, rather than try to troubleshoot your code, how about if I post some code that may do as you wanted. I call the script "Freespace.sql". In Section 1, I'll show the results; Section 2 is the script.

Section 1 -- Sample output from "Freespace.sql":

@freespace
                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- --------------------------------------------------
DATA1              4    2,097,152,000      104,857,600       45,481,984    2,051,670,016     97 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA01.DBF'
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1    2,097,152,000      153,092,096      149,422,080    1,947,729,920     92 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       10,485,760        1,073,152    2,096,078,848     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
YADA               5    2,097,152,000        2,097,152           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTYADA01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      291,504,128      196,108,288   10,289,651,712

5 rows selected.


Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
************************************************************************************************************************************************************

Section 2 -- Source code for "Freespace.sql":

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

If a tablespace is "empty", then you will see "Bytes Used" for the tablespace's files "at minimum", which is 65,536 for "locally managed tablespaces" or 16,392 for dictionary-managed tablespaces.

Let me know if this is useful.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing low-cost remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kozlow]

That is great, thanks...

I am still trying to get a total of allocated, used and free space by TableSpace, not Instance.

Our DBA has left, went back to India, and we keep hitting space problems on individual TableSpaces. I was trying to give me a report that totals the Allocated vs. Free...

This will allow me to monitor TBS growth over a period and make better guess-ta-mits on how big to create the new DBF.

I do something similar for our non-Oracle databases and run once a week... I have a couple of years of data on them. It really helped when we sized our new server and the SAN devices....

Thanks again. I am looking at this to see if I can figure out how to modify it to sum by tablespace....

 

[SantaMufasa]

we keep hitting space problems on individual TableSpaces

Is it that your individual tablespaces keep filling up despite there being plenty of available space on disk? If so, you do not need to have "Kozlow's Crystal Ball" to anticipate usage...just allocate several files that AUTOEXTEND and add space on an as-needed, just-in-time basis. (That is our policy.) Doing so has caused us to eliminate entirely out-of-space errors on tablespaces. Here is the code we use to CREATE dictionary-managed, AUTOEXTENDing tablespaces, then to add more AUTOEXTENDing files:

create tablespace yada datafile ('<file name 1>') size 10m
autoextend on next 10m maxsize 2000m
extent management local autoallocate;

alter tablespace yada datafile ('<file name 2>') size 10m
autoextend on next 10m maxsize 2000m;

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing low-cost remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kozlow]

Thanks again...

How do you monitor TEMP space use?

 

[SantaMufasa]

You can incorporate TEMP tablespace information by assembling relevant data from these virtual data dictionary views:
V_$TEMPFILE
V_$TEMPORARY_LOBS
V_$TEMPSTAT
V_$TEMPSTATXS
V_$TEMP_CACHE_TRANSFER
V_$TEMP_EXTENT_MAP
V_$TEMP_EXTENT_POOL
V_$TEMP_PING
V_$TEMP_SPACE_HEADER

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing low-cost remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.