Be aware that this script does not include autoexted info.... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan
[EMAIL PROTECTED] Sent by: To: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: .com Subject: RE: SQL Query 11/14/2003 01:19 PM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas 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).