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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Free space for all tablespaces

RE: Free space for all tablespaces

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Tue, 11 Jul 2000 14:02:57 -0400
Message-Id: <10555.111696@fatcity.com> 





Tom,

Listed below are the 4 scripts that I run to give me an indication of the =
database and space usage. It is run by the "DAILY.SQL" script and stirred =
in my "C:" drive. Adjust the parameters accordingly. The tsstats.sql will =


list the instance that I am reporting on by the name the database is =
given.


=AF---------------------

=AFDAILY.SQL


=AF----------------------



Rem daily sql scripts run to report on table space conditions. File =
c:\glcsql\daily.sql


Spool  c:\glcsql\rorreps


@c:\glcsql\tsstats;


ttitle off


@c:\glcsql\tsfree;


ttitle off


@c:\glcsql\tsdetail;


ttitle off


@c:\glcsql\tswarn;


ttitle off


spool off



=AF-------------------------



 TSSTATS.SQL



=AF---------------------------



Rem listing of the table space statistics. File c:\glcsql\tsstats.sql

	set feedback off;
	set pagesize 60;
	set head OFF;




column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  " HH24:MI:SS') today from dual;
SELECT DECODE (VALUE,'oracle','REPORT FOR GLC_DEVELOPMENT_411 server',

'oracle1','REPORT FOR OLD_DATABASE',
'oracle2','REPORT FOR GLC_DATABASE server',
'finance1','REPORT FOR GLC_APPLICATION server','REPORT FOR UNLISTED')


FROM V$PARAMETER WHERE NAME=3D'db_name';

TTITLE LEFT '--------------------------------------------------------------=
--------------------------------' -




SKIP CENTER xtoday -


SKIP CENTER   '*********** DATA FILE STATISTICS ***********' -


SKIP LEFT   '--------------------------------------------------------------=
--------------------------------'=20
set head on;=09
	column tablespace_name format a25 heading 'TS NAME'
	column bytes format 9,999,999,999
	column file_name format a35
	select tablespace_name,bytes,status,file_name
	from dba_data_files
	order by tablespace_name;


=AF---------------------------





TSFREE.SQL



=AF--------------------------



Rem listing of the free table space. File c:\glcsql\tsfree.sql


	set feedback off;
	set pagesize 60;
	set head on;




column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  "HH24:MI:SS') today from dual;

TTITLE LEFT '--------------------------------------------------------------=
--------------------------------' -




SKIP center xtoday -


SKIP CENTER   '*********** TABLESPACE FREE SPACE ***********' -


SKIP LEFT   '--------------------------------------------------------------=
--------------------------------'
	column tablespace_name format a25 heading 'TS NAME'
	column sum(bytes) format 9,999,999,999 heading 'bytes free'
	column max(bytes) format 9,999,999,999 heading 'max bytes'

	select tablespace_name,sum(bytes),max(bytes)
	from dba_free_space
	group by tablespace_name;


=AF-------------------------





TSDETAIL.SQL



=AF----------------------



Rem listing of the table space information. File c:\glcsql\tsdetail.sql

	set feedback off;
	set pagesize 256;
	set head on;




column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  " HH24:MI:SS') today from dual;

TTITLE LEFT '--------------------------------------------------------------=
--------------------------------' -




SKIP CENTER    xtoday -


SKIP CENTER   'TABLESPACE SEGMENTS DETAIL LISTING BY SEGMENT TYPE, =


TABLESPACE, SEGMENT NAME' -
SKIP LEFT   '--------------------------------------------------------------=
--------------------------------'=20
		column segment_name format a30
		column segment_type format a6 heading TYPE
		column tablespace_name format a25 heading 'TS NAME'
		column extents format 999 heading EXTS
		column bytes format 9,999,999,999
		column blocks format 9,999,999
		select segment_name,segment_type,tablespace_name,
		extents,bytes,blocks from dba_segments
		where tablespace_name !=3D 'SYSTEM'
		and owner not in ('SYS','SYSTEM')
		order by segment_type,tablespace_name,segment_name;


=AF------------------------	=09





TSWARN.SQL



=AF-------------------------



Rem listing of the table space segments greater than 3 extents. File =
c:\glcsql\tswarn.sql


	set feedback off;
	set pagesize 60;
	set head on;




column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  " HH24:MI:SS') today from dual;

TTITLE LEFT '--------------------------------------------------------------=
--------------------------------' -




SKIP CENTER xtoday -


SKIP CENTER   'TABLESPACE SEGMENTS WITH MORE THAN 3 EXTENTS' -


SKIP LEFT   '--------------------------------------------------------------=
--------------------------------'=20
	column segment_name format a30
	column segment_type format a6 heading TYPE
	column tablespace_name format a25 heading 'TS NAME'
	column extents format 999 heading EXTS
	column bytes format 9,999,999,999
	column blocks format 9,999,999
	select segment_name,segment_type,tablespace_name,
	extents,bytes,blocks from dba_segments
	where tablespace_name !=3D 'SYSTEM'
	and owner not in ('SYS','SYSTEM') and
	extents > 3
	order by segment_type,tablespace_name,segment_name;


=AF----------------------------------------------------






Hope this will help in your quest.


Ron Rogers


DBA



ATL.GA


>>> blair_at_pjm.com 07/11/00 12:05PM >>>


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]=20
> Sent:	Tuesday, July 11, 2000 10:58 AM
> To:	Multiple recipients of list ORACLE-L
> Subject:	Re: Free space for all tablespaces
>=20
> You can try this it works
>=20
> 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 =3D b.tablespace_name
> group by a.tablespace_name
> /
>=20
> Regards
> $uhen=20
> Oracle DBA
> Telkom SA
>=20
>=20
> >>> 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:
>=20
> 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 =3D b.tablespace_name
> group by 1;
>=20
> Does anyone have a query that does work??
>=20
> thanks,
>=20
> ..tom
>=20
>=20
>=20
> --=20
> Author:=20
>   INET: blair_at_pjm.com=20
>=20
> 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).
>=20
> --=20
> Author: Suhen Pather
>   INET: pathers5_at_telkom.co.za=20
>=20
> 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).


--=20


Author:=20


  INET: blair_at_pjm.com=20


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
Received on Tue Jul 11 2000 - 13:02:57 CDT












Original text of this message













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