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: sufficient next extent space

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

Original text of this message

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