Tablespace Capacity Status 2

[jtanner]

Hello,

In 10g what SQL can I use to list all my tablespace:

1. Names
2. Sizes (in bytes or megabytes)
3. Used (in bytes or megabytes)
Percent used or free would be fine too.


Thanks,

JT

 

[jimirvine]

Have a look at the tables dba_free_space, dba_free_space_coalesced and dba_tablespaces. As for pct_used and pct_free, if you are on 10g you should be using Locally managed tablespaces with Automatic segment space management.

Jim

 

[SantaMufasa]

JT,

Many Tek-Tipsters like my "freespace.sql" script to achieve the results for which you are asking. My script lists the following information for each file in the database's tablespaces:

A. Tablespace Name
B. Total Potential File Size (maximum file size if file is autoextending)
C. Total Current File Size
D. Bytes Used (space used currently by tables, indexes, et cetera, in file)
E. Potential Bytes Free (difference between "B" and "D", above)
F. % Free of Potential Total Bytes ("E"/"B", above)
G. Auto Extending? (Yes/No)
H. Filename (name of file fully qualified by path)

Here is the code that will produce this report, along with an excerpt of the output (trimmed to avoid ugly wrapping):

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

                                Total            Total                                  
Tablespace                  Potential          Current                         Potential
Name               #        File Size        File Size       Bytes Used       Bytes Free
--------------- ---- ---------------- ---------------- ---------------- ----------------
BGGT_DATA          6                                                                    
BGGT_DATA          7                                                                    
DATA1              4    2,097,152,000      293,601,280       85,917,696    2,011,234,304
DATA2              5    2,097,152,000      241,172,480       10,027,008    2,087,124,992
DATA3              8    2,097,152,000       10,485,760           65,536    2,097,086,464
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464
SYSTEM             1    2,097,152,000      289,406,976      200,425,472    1,896,726,528
TEMP               3    2,097,152,000      125,829,120            8,192    2,097,143,808
                     ---------------- ---------------- ---------------- ----------------
sum                    12,582,912,000      981,467,136      296,509,440   12,286,402,560

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jtanner]

Guys,

Great feedback!

I did not know about dba_free_space - very useful. Santa, that is a fantastic script.

Thanks for taking the time to post.

JT