Re: Script Determine Which TableSpaces

From: MattyD <mattdee_at_gmail.com>
Date: 19 Jul 2004 10:52:19 -0700
Message-ID: <8c3a1661.0407190952.7550809d@posting.google.com>

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0407161356.7ca5a598_at_posting.google.com>...
> melliott42_at_yahoo.com (Michael) wrote in message news:<91721cf.0407151650.4eaab67e_at_posting.google.com>...
> > Hello,
> >
> > I have been tasked with creating a .sql script to determine which
> > TableSpaces in a database are > 90% full. This script must work on
> > both Oracle8 and Oralce9 systems.
> >
> > Can you please share a script or snippet that will do this.
> >
> > Thanks very much,
> >
> > Michael
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT&p_id=131704.1
>
> jg

SET ECHO off
REM NAME: TFSTSNFO.SQL
REM USAGE:"@path/tfstsnfo"

REM ------------------------------------------------------------------------

REM REQUIREMENTS:
REM SELECT on DBA_DATA_FILES, DBA_FREE_SPACE

REM ------------------------------------------------------------------------

REM PURPOSE:
REM Produces a brief report showing the filenames, free space, REM size and status of all tablespaces in the database.

REM ------------------------------------------------------------------------

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially.

REM ------------------------------------------------------------------------

REM Main text of script follows:

set echo off
break on table_space on free
column free format 999,999,999,990
column bytes format 999,999,999,990
set pagesize 66
set lines 100
spool tblsp.out

select	substr(df.tablespace_name,1,15) table_space,  
        sum(fs.bytes) free, 
	substr(df.file_name,1,35) Name_of_File,  
	df.bytes,  
        substr(df.status,1,5) stat

from dba_data_files df ,dba_free_space fs where df.tablespace_name = fs.tablespace_name group by substr(df.tablespace_name,1,15),

	substr(df.file_name,1,35),  
	df.bytes,substr(df.status,1,5)

/
spool off
set echo on

OR ------

rem -----------------------------------------------------------------------

rem Filename: tsspace.sql
rem Purpose: Show Used/free space in Meg by tablespace name rem Author: Balaji Rajagopalan [try2xl_at_yahoo.com]

rem -----------------------------------------------------------------------

tti "Space Usage for Database in Meg"

SELECT Total.name "Tablespace Name",

Free_space, (total_space-Free_space) Used_space, total_space FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space

     from sys.dba_free_space
    group by tablespace_name
  ) Free,
  (select b.name, sum(bytes/1024/1024) TOTAL_SPACE

     from sys.v_$datafile a, sys.v_$tablespace B     where a.ts# = b.ts#
    group by b.name
  ) Total
WHERE Free.Tablespace_name = Total.name
/

tti off

OR -----

Just go here....
http://www.orafaq.com/scripts/index.htm#SPACE

Cheers,
Matt Received on Mon Jul 19 2004 - 12:52:19 CDT

Re: Script Determine Which TableSpaces > 90% Full