RE: sufficient next extent space

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Mon, 7 Feb 2005 12:57:45 -0500
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA65021B7E4C@25exch1.vicorpower.vicr.com>

Somewhat simpler:

SELECT e.TABLESPACE_NAME, NVL(MAX(B.BYTES),0)FREE_BYTES, MAX('DICTIONARY'),MAX('USER'), MAX(NVL(NEXT_EXTENT,0)),        COUNT(D.BLOCKS), max((select Sum(decode(autoextensible,'YES', D.MAXBYTES-D.BYTES, 0)) from dba_data_files z=20

                             where z.tablespace_name =3D

e.tablespace_name))TS_MAX_BYTES
FROM DBA_FREE_SPACE B, DBA_DATA_FILES D, DBA_TABLESPACES E=20 WHERE B.TABLESPACE_NAME =3D D.TABLESPACE_NAME AND D.TABLESPACE_NAME =3D E.TABLESPACE_NAME=20 GROUP BY E.TABLESPACE_NAME=20 Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Zeng, Lei=20
Sent: Monday, February 07, 2005 12:48 PM To: Tony.Adolph_at_o2.com; JBECKSTROM_at_gcrta.org Cc: oracle-db-l_at_Groups.ITtoolbox.com; ORACLE-L_at_IC.SUNYSB.EDU; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org; oracle-rdbms_at_yahoogroups.com; oracledba_at_LazyDBA.com Subject: RE: sufficient next extent space

Modified your script by replacing 'autoextensible' with a function 'decode(count(autoextensible),0,0,1)'. A tablespace may have multiple data files and some datafile is autoextensible but other are not. Using my function will eleminate double entries after 'group by' clause.

select

        df.tablespace_name||decode(autoext,1,'*',' ') ts,
        total/1024/1024 Total_MB,


        (total - free)/1024/1024 Used_MB,

        ((total - free)/ total) * 100 pct_used,

        free/1024/1024 free_MB,


        (free / total ) * 100 pct_free,

        max_extent/1024/1024 max_extent_size=3D20
from


        (select tablespace_name, sum(bytes) free, max(bytes) max_extent

        from  dba_free_space
        group by tablespace_name) fs
         ,


        (select  tablespace_name,decode(count(autoextensible),0,0,1)

autoext, sum(bytes) total
        from dba_data_files
        group by tablespace_name
        union all
        select tablespace_name, decode(count(autoextensible),0,0,1)
autoext, sum(bytes) total
        from dba_temp_files
        group by tablespace_name) df=3D20
where
        df.tablespace_name =3D3D fs.tablespace_name (+) ;

Lei
DBA, Vicor Corp.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 07 2005 - 13:00:45 CST