Re: Script Determine Which TableSpaces > 90% Full

mattdee_at_gmail.com (MattyD) wrote in message news:<8c3a1661.0407190952.7550809d_at_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

Regardless where the scripts come from, a common mistake in Oracle tablespace freespace/usage scripts is that when the free space is so small even one extent can't be created in the tablespace, the tablespace name disappears from dba_free_space. Many scripts, not using outer joins between dba_data_files and dba_free_space, or using outer joins but with a simple freespace > somepercentage, miss those tablespaces that need space the most.

Yong Huang Received on Wed Jul 21 2004 - 10:41:44 CDT