Re: SQL for Tablespace use/allocation?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/17
Message-ID: <337cf99a.604779@newshost>#1/1

Here is mine, it generates a report like:

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
SEMINAR                 1,024          502          522   49.0          432
CIVTECH                 1,024          372          652   36.3          652
PRISM                   1,024          222          802   21.7          802

doesnt show the number of files but shows
- space allocated to tablespace (kbytes)

• space used within tablespace (Used) might not be filled with data but is allocated to an object
• free (free) free space in thta tablespace
• Used (used) pct of space within the tablespace that is allocated to objects
• largest (largest) the largest block of space that can be allocated. Because of fragments may be < free.

You would go:

SQL> @free 1     -- to order by tablespace name
SQL> @free 2     -- to order by size 

and so on....

---

• 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

select 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 ) b

where a.tablespace_name (+) = b.tablespace_name order by &1
/

On Fri, 16 May 1997 15:01:51 -0400, Peter Mroz <pmroz_at_domaincorp.com> wrote:

>I'm trying to write a SQL script to produce a report of tablespace
>usage.  Ideally I'd like something like:
>
>Table Space   Blocks Alloc   Blocks Used   # Files   % Used
>-----------------------------------------------------------
>SYSTEM        100                65          2        65
>TEMP           10                 1          1        10
>FOO            10                 8          2        80
>BAR           200               140          5        70
>
>I know that all of this information is in sys.dba_data_files and
>sys.dba_extents; I'm having trouble with my script:
>
>select a.tablespace_name "Table Space",
>sum(a.blocks) "Blocks Alloc",
>sum(b.blocks) "Blocks Used",
>count(a.blocks) "# Files",
>100 * sum(b.blocks) / sum(a.blocks) "% Used"
>from sys.dba_data_files a, sys.dba_extents b
>where a.tablespace_name = b.tablespace_name
>group by a.tablespace_name;
>
>
>Has anyone written a script to do this?  Or, can you point me in the
>right direction with my script?  Thanks.
>
>-- 
>Peter Mroz				Domain Solutions Corporation
>pmroz_at_domaincorp.com

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 17 1997 - 00:00:00 CDT