RE: Free space for all tablespaces

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Tue, 11 Jul 2000 12:58:46 -0500
Message-Id: <10555.111694@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFEB61.A9125CF2
Content-Type: text/plain;

charset="iso-8859-1"

For a more detailed look, try the attached script. Ron

-----Original Message-----
From: Jack Silvey [mailto:JSilvey_at_XOL.com] Sent: Tuesday, July 11, 2000 1:17 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Free space for all tablespaces

Exactly.

It is because you are getting multiple records per tablespace for those tablespaces with multiple datafiles.

If a tablespace has >1 datafiles, you will have >1 records, which will cause you to have >1 records per tablespace from dba_free_space.

-----Original Message-----
Sent: Tuesday, July 11, 2000 12:41 PM
To: Multiple recipients of list ORACLE-L

Tom,
Try this one...
select a.tablespace_name tablespace, round(sum(a.bytes)/1024/1024,2) Used, round(nvl(b.free_space,0),2) Free
from dba_data_files a,

      (select tablespace_name, sum(bytes)/1024/1024 free_space
         from dba_free_space
         group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+) group by a.tablespace_name, b.free_space order by 1;

HTH,
- Sah Kohsuwan

> -----Original Message-----
> From: blair_at_pjm.com [SMTP:blair_at_pjm.com]
> Sent: Tuesday, July 11, 2000 12:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Free space for all tablespaces
>
> Thanks for the reply but... I have tried this. For some reason that I
> don't
> understand it seems to fail when the tablespace has more than 1 datafile.
> It
> does not generate an error - it just gives the wrong answer - really
> weird.
>
> thanks anyway,
>
> ..tom
>
>
>
>
>
>
> > -----Original Message-----
> > From: Suhen Pather [SMTP:pathers5_at_telkom.co.za]
> > Sent: Tuesday, July 11, 2000 10:58 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Free space for all tablespaces
> >
> > You can try this it works
> >
> > select a.tablespace_name, sum(a.bytes/1024/1024) "Allocated" ,
> > sum(b.bytes/1024/1024) "Free Space" from sys.dba_data_files a,
> > sys.dba_free_space b
> > where a.tablespace_name = b.tablespace_name
> > group by a.tablespace_name
> > /
> >
> > Regards
> > $uhen
> > Oracle DBA
> > Telkom SA
> >
> >
> > >>> blair_at_pjm.com 07/11/00 03:56PM >>>
> > I just want a SQL query to give me the freespace in all tablespaces.
> This
> > doesn't work:
> >
> > select a.tablespace_name, sum(a.bytes) TOTAL_SPACE, sum(b.bytes)a
> FREE_SPACE
> > from sys.dba_data_files a, sys.dba_free_space b
> > where a.tablespace_name = b.tablespace_name
> > group by 1;
> >
> > Does anyone have a query that does work??
> >
> > thanks,
> >
> > ..tom
> >
> >
> >
> > --
> > Author:
> > INET: blair_at_pjm.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> >
> > --
> > Author: Suhen Pather
> > INET: pathers5_at_telkom.co.za
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> --
> Author:
> INET: blair_at_pjm.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).

-- 
Author: Sah Kohsuwan
  INET: skohsuwan_at_comforce.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Author: Jack Silvey
  INET: JSilvey_at_XOL.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).


------_=_NextPart_000_01BFEB61.A9125CF2
Content-Type: application/octet-stream;
	name="freespace.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
	filename="freespace.sql"

DQoNClJFTSBuYW1lOiBmcmVlc3BhY2Uuc3FsDQpSRU0gICAgIFRoaXMgc2NyaXB0IGlzIHVzZWQg
dG8gbGlzdCBkYXRhYmFzZSBmcmVlc3BhY2UsIHRvdGFsIGRhdGFiYXNlDQpSRU0gc3BhY2UsIGxh
cmdlc3QgZXh0ZW50LCBmcmFnbWVudHMgYW5kIHBlcmNlbnQgZnJlZXNwYWNlLiANClJFTSAgDQpS
RU0gIFVzYWdlIHNxbHBsdXMgc3lzdGVtL3Bhc3N3ZCBAZnJlZXNwYWNlDQpSRU0NClJFTSBEYXRl
IAkgIENyZWF0ZSAJRGVzY3JpcHRpb24NClJFTSAzMC1PY3QtOTYgICAgRmFuIFpoYW5nIAlJbml0
aWFsIGNyZWF0aW9uDQpSRU0gIA0KUkVNICBkYmEgdG9vbCBrZXk6IGZyZWVzcGFjZS5zcWwgLS0g
bGlzdCBkYXRhYmFzZSBmcmVlc3BhY2UsIHRvdGFsIHNwYWNlIGFuZCBwZXJjZW50IGZyZWUgDQpS
RU0gDQoNCnNldCBwYXUgb2ZmDQpzZXQgcGFnZXMgMzUNCnNldCBsaW5lcyAxMjANCg0KY29sIHRh
Ymxlc3BhY2UgIAloZWFkaW5nICdUYWJsZXNwYWNlJw0KY29sIGZyZWUgICAgICAgIAloZWFkaW5n
ICdGcmVlfChNYiknICAgICAJZm9ybWF0IDk5OTk5LjkNCmNvbCB0b3RhbCAgICAgICAJaGVhZGlu
ZyAnVG90YWx8KE1iKScgICAgCWZvcm1hdCA5OTk5OTkuOQ0KY29sIHVzZWQgICAgICAgIAloZWFk
aW5nICdVc2VkfChNYiknICAgICAJZm9ybWF0IDk5OTk5LjkNCmNvbCBwY3RfZnJlZSAgICAJaGVh
ZGluZyAnUGN0fEZyZWUnICAgICAgCWZvcm1hdCA5OTk5OS45DQpjb2wgcGN0X25leHQgICAJaGVh
ZGluZyAnUGN0fE5leHQnICAgICAgCWZvcm1hdCA5OTk5OS45DQpjb2wgbGFyZ2VzdCAgICAgCWhl
YWRpbmcgJ0xhcmdlc3R8KE1iKScgIAlmb3JtYXQgOTk5OTkuOQ0KY29sIG5leHQgICAgICAgCWhl
YWRpbmcgJ05leHR8RXh0KE1iKScgIAlmb3JtYXQgOTk5OTkuOQ0KY29sIGZyYWdtZW50ICAgIAlo
ZWFkaW5nICdGcmFnbWVudCcgICAgICAJZm9ybWF0IDk5OQ0KY29sIGV4dGVudHMgICAgIAloZWFk
aW5nICdNYXgufEV4dC4nICAgICAJZm9ybWF0IDk5OQ0Kc3Bvb2wgZnJlZXNwYWNlLnR4dAoNCg0K
Y29tcHV0ZSBzdW0gb2YgdG90YWwgb24gcmVwb3J0DQpjb21wdXRlIHN1bSBvZiBmcmVlIG9uIHJl
cG9ydA0KY29tcHV0ZSBzdW0gb2YgdXNlZCBvbiByZXBvcnQNCg0KYnJlYWsgb24gcmVwb3J0DQoN
CnNlbGVjdCAgc3Vic3RyKGEudGFibGVzcGFjZV9uYW1lLDEsMTMpIHRhYmxlc3BhY2UsDQogICAg
ICAgIHJvdW5kKHN1bShhLnRvdGFsMSkvKDEwMjQqMTAyNCksIDEpIFRvdGFsLA0KICAgICAgICBy
b3VuZChzdW0oYS50b3RhbDEpLygxMDI0KjEwMjQpLCAxKS1yb3VuZChzdW0oYS5zdW0xKS8oMTAy
NCoxMDI0KSwgMSkgdXNlZCwNCiAgICAgICAgcm91bmQoc3VtKGEuc3VtMSkvKDEwMjQqMTAyNCks
IDEpIGZyZWUsDQogICAgICAgIHJvdW5kKHN1bShhLnN1bTEpLygxMDI0KjEwMjQpLCAxKSoxMDAv
cm91bmQoc3VtKGEudG90YWwxKS8oMTAyNCoxMDI0KSwgMSkgcGN0X2ZyZWUsDQogICAgICAgIHJv
dW5kKHN1bShhLm1heGIpLygxMDI0KjEwMjQpLCAxKSBsYXJnZXN0LA0KICAgICAgICByb3VuZChz
dW0oYS5uZXh0MSkvKDEwMjQqMTAyNCksIDEpIE5leHQsDQogICAgICAgIHJvdW5kKHN1bShhLm5l
eHQxKS8oMTAyNCoxMDI0KSwgMSkqMTAwL3JvdW5kKHN1bShhLm1heGIpLygxMDI0KjEwMjQpLCAx
KSBwY3RfbmV4dCwNCiAgICAgICAgbWF4KGEubWF4X2V4dCkgZXh0ZW50cywNCiAgICAgICAgbWF4
KGEuY250KSBmcmFnbWVudA0KZnJvbQ0KICAgICAgICAoc2VsZWN0IHRhYmxlc3BhY2VfbmFtZSwg
DQogICAgICAgICAgICAgICAgMCB0b3RhbDEsIA0KICAgICAgICAgICAgICAgIHN1bShieXRlcykg
c3VtMSwgDQogICAgICAgICAgICAgICAgbWF4KGJ5dGVzKSBNQVhCLA0KICAgICAgICAgICAgICAg
IGNvdW50KGJ5dGVzKSBjbnQsDQogICAgICAgICAgICAgICAgMCBuZXh0MSwNCiAgICAgICAgICAg
ICAgICAwIG1heF9leHQNCiAgICAgICAgZnJvbSAgICBkYmFfZnJlZV9zcGFjZQ0KICAgICAgICBn
cm91cCBieSB0YWJsZXNwYWNlX25hbWUNCiAgICAgICAgdW5pb24NCiAgICAgICAgc2VsZWN0ICB0
YWJsZXNwYWNlX25hbWUsIA0KICAgICAgICAgICAgICAgIHN1bShieXRlcykgdG90YWwxLCANCiAg
ICAgICAgICAgICAgICAwLCANCiAgICAgICAgICAgICAgICAwLCANCiAgICAgICAgICAgICAgICAw
LA0KICAgICAgICAgICAgICAgIDAsDQogICAgICAgICAgICAgICAgMCANCiAgICAgICAgZnJvbSAg
ICBkYmFfZGF0YV9maWxlcw0KICAgICAgICBncm91cCBieSB0YWJsZXNwYWNlX25hbWUNCiAgICAg
ICAgdW5pb24NCiAgICAgICAgc2VsZWN0IHRhYmxlc3BhY2VfbmFtZSwNCiAgICAgICAgICAgICAg
ICAwLA0KICAgICAgICAgICAgICAgIDAsDQogICAgICAgICAgICAgICAgMCwNCiAgICAgICAgICAg
ICAgICAwLA0KICAgICAgICAgICAgICAgIG1heChuZXh0X2V4dGVudCkgbmV4dDEsDQogICAgICAg
ICAgICAgICAgbWF4KGV4dGVudHMpIG1heF9leHQNCiAgICAgICAgZnJvbSBkYmFfc2VnbWVudHMN

Received on Tue Jul 11 2000 - 12:58:46 CDT