Below is a very nice tablespace script, followed by some sample output.
Thought you might like it. I found in somewhere.
REM name: freespace.sql
REM This script is used to list database freespace, total database
REM space, largest extent, fragments and percent freespace.
REM
REM Usage sqlplus system/passwd @freespace
REM
REM Date Create Description
REM 30-Oct-96 Fan Zhang Initial creation
REM
REM dba tool key: freespace.sql -- list database freespace, total space
and percent free
REM
set pau off
set pages 35
set lines 120
col tablespace heading 'Tablespace'
col free heading 'Free|(Mb)' format 99999.9
col total heading 'Total|(Mb)' format 999999.9
col used heading 'Used|(Mb)' format 99999.9
col pct_free heading 'Pct|Free' format 99999.9
col pct_next heading 'Pct|Next' format 99999.9
col largest heading 'Largest|(Mb)' format 99999.9
col next heading 'Next|Ext(Mb)' format 99999.9
col fragment heading 'Fragment' format 999
col extents heading 'Max.|Ext.' format 999
spool freespace.txt
compute sum of total on report
compute sum of free on report
compute sum of used on report
break on report
select substr(a.tablespace_name,1,13) tablespace,
round(sum(a.total1)/(1024*1024), 1) Total,
round(sum(a.total1)/(1024*1024),
1)-round(sum(a.sum1)/(1024*1024), 1) used,
round(sum(a.sum1)/(1024*1024), 1) free,
round(sum(a.sum1)/(1024*1024),
1)*100/round(sum(a.total1)/(1024*1024), 1) pct_free,
round(sum(a.maxb)/(1024*1024), 1) largest,
round(sum(a.next1)/(1024*1024), 1) Next,
round(sum(a.next1)/(1024*1024),
1)*100/round(sum(a.maxb)/(1024*1024), 1) pct_next,
max(a.max_ext) extents,
max(a.cnt) fragment
from
(select tablespace_name,
0 total1,
sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt,
0 next1,
0 max_ext
from dba_free_space
group by tablespace_name
union
select tablespace_name,
sum(bytes) total1,
0,
0,
0,
0,
0
from dba_data_files
group by tablespace_name
union
select tablespace_name,
0,
0,
0,
0,
max(next_extent) next1,
max(extents) max_ext
from dba_segments
group by tablespace_name) a
group by a.tablespace_name
order by pct_free
/
spool off;
SQL*Plus: Release 3.3.4.0.0 - Production on Fri Nov 14 14:11:53 2003
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to:
Oracle7 Server Release 7.3.4.4.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.4.0 - Production
Total Used Free Pct Largest Next
Pct Max.
Tablespace (Mb) (Mb) (Mb) Free (Mb) Ext(Mb)
Next Ext. Fragment
------------- --------- -------- -------- -------- -------- --------
-------- ---- --------
PRODIDX 21500.0 20130.4 1369.6 6.4 925.8 87.9
9.5 62 13
PROD 27400.0 24014.1 3385.9 12.4 243.3 212.4
87.3 54 197
PRODALT 200.0 149.3 50.7 25.4 50.7 19.1
37.7 4 1
SYSTEM 200.0 92.0 108.0 54.0 86.5 4.2
4.9 15 12
TOOLS 100.0 12.5 87.5 87.5 75.1 12.0
16.0 4 6
PATROL_DATA 20.0 2.0 18.0 90.0 18.0 .3
1.7 1 1
RBS 2500.0 156.1 2343.9 93.8 443.9 8.0
1.8 2 24
USERS 100.0 3.2 96.8 96.8 93.4 1.0
1.1 2 5
BMC_SMGT_TS 1000.0 4.6 995.4 99.5 500.0 2.0
.4 1 280
PRODAUD 1000.0 3.4 996.6 99.7 996.6 .4
.0 20 1
PATROL_TEMP 10.0 .0 10.0 100.0 10.0 .0
.0 0 1
TEMP 2900.0 .0 2900.0 100.0 24.0 .0
.0 0 165
--------- -------- --------
sum 56930.0 44567.6 12362.4
12 rows selected.
-----Original Message-----
Sent: Friday, November 14, 2003 1:54 PM
To: Multiple recipients of list ORACLE-L
But Stephane, I am aggregating by tablespace for both extents and for
data_files. There is nothing here that is separating out anything by
datafile. And, if I take away the GROUP BY, I lose the ability to
aggregate at all, which is the point of this...
-----Original Message-----
Sent: Friday, November 14, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Bambi,
Your second query is wrong because all extents in a tablespace don't
necessarily belong to the same datafile. Try the query without the
aggregate functions and the GROUP BY, and you'll understand your
mistake.
HTH,
SF
"Bellow, Bambi" wrote:
>
> Friends --
>
> Why would these two queries return different results?
>
> This query works.
>
> SQL> l
> 1 select
>
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_alloca
ted,
> 2)*100 pct
> 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
> 3 from dba_extents group by tablespace_name) a,
> 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
> 5 from dba_data_files group by tablespace_name) b
> 6 where a.tablespace_name=b.tablespace_name
> 7* and a.tablespace_name='NAUAT'
> SQL> /
>
> TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
> ------------------------------ -------------- ---------- ----------
> NAUAT 22924.25 11509 50
>
> This query does not work
>
> 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
> 2 sum(a.bytes)/(1024*1024) megs_used,
> 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct
> 4 from dba_extents a, dba_data_files b
> 5 where a.tablespace_name=b.tablespace_name
> 6 and a.tablespace_name='NAUAT'
> 7* group by a.tablespace_name,b.tablespace_name
> SQL> /
>
> TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
> ------------------------------ -------------- ---------- ----------
> NAUAT 31773010.5 23018 .07
>
> Bambi.
> --
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bellow, Bambi
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Smith, Ron L.
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
