| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Query
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 creationREM
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 999spool 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
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 >
> 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: sfaroult_at_oriole.com 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: ListGuru_at_fatcity.com (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: bbellow_at_chi.navtech.com 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: ListGuru_at_fatcity.com (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: rlsmith_at_kmg.com 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: ListGuru_at_fatcity.com (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).Received on Fri Nov 14 2003 - 14:19:25 CST
 |
 |