Re: Tablespace storage summary

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 10 Jun 1999 11:45:17 GMT
Message-ID: <3760a4c2.2605216@newshost.us.oracle.com>

A copy of this was sent to "Ted Starr" <starrt_at_cyberback.com> (if that email address didn't require changing) On Wed, 9 Jun 1999 15:57:41 -0500, you wrote:

>I need to write a query that will summarize table space storage
>in something similar to the following format:
>
>TABLESPACE USED FREE
> ts_name #### ####
> owner_1 ####
> owner_2 ####
>
> ts_name_2 #### ####
> owner_3 ####
> owner_4 ####
>
>I can get the tablespace_name, used, and free by joining
>dba_extents (or segments) with dba_free_space, but how
>do I get the used for each owner by tablespace at the same
>time?
>
>TIA,
>Ted
>

Here is one (designed for sqlplus)

---

• free.sql --
• This SQL Plus script lists freespace by tablespace

  ---

column dummy noprint

column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"

column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

column order_by_col1 noprint
column order_by_col2 noprint

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) order_by_col1,
       chr(0) order_by_col2,
       nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
       select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name ) b

where a.tablespace_name (+) = b.tablespace_name union all
select tablespace_name order_by_col1,

       owner order_by_col2,
       ' ' || owner,
       sum(bytes)/1024  kbytes,
       to_number(null) used, to_number(null) free, to_number(null) pct_used,

to_number(null) largest
from dba_segments
group by tablespace_name, owner , ' ' || owner order by 1, 2
/

-------------------------- eof ---------------------------------

creates a report that looks like:

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
ARIA                    6,144          104        6,040    1.7        6,040
 WEB$ARIA                  96
CERTIFICATION         222,208      221,680          528   99.8          216
 CERT                 215,744
 CERT_ADMIN             5,928
CIVTECH                 1,024          360          664   35.2          664
 CIVTECH                  352
CTXSYS                 12,288        8,896        3,392   72.4        2,680
 CTXSYS                 5,784
 PRESIDENTS             3,104
DES2                   22,528       21,704          824   96.3          824
 REPOS                 21,696
INTRANET_UPLOAD         1,024            8        1,016     .8        1,016

.....

So, you get a tablespace (eg: aria) and how much space is allocated to it (kbytes), how much is used in it (used), how much is left unallocated (free), the %used, and the largest free contigous set of blocks (biggest extent you could allocate in the tablespace).

under the tables is an indented list of users who have allocated objects in the tables and (in kbytes) how much storage they've allocated...

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 10 1999 - 06:45:17 CDT