Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL for Tablespace use/allocation?

SQL for Tablespace use/allocation?

From: Peter Mroz <pmroz_at_domaincorp.com>
Date: 1997/05/16
Message-ID: <337CAF1E.2AE2@domaincorp.com>#1/1

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
Received on Fri May 16 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US