Re: Locally Managed vs Dictionary managed tablespaces

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 2 Nov 2001 07:30:18 +1100
Message-ID: <3be1b0b4$0$13585$afc38c87@news.optusnet.com.au>

I guess you're after doing the 'where a segment's next extent is bigger than the largest piece of free space left in a tablespace' test?

True, that can't be done, even in 9i, because the next extent field is null there too. But I've always thought it was bit late to be wondering about such things when there's only one new extent to go before disaster!

Hence, I use this:

SELECT TABLESPACE_NAME,

ROUND(SUM(TOTAL_MB)-SUM(FREE_MB)) MB_USED,
ROUND(SUM(TOTAL_MB)) MB_SIZE,
ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(TOTAL_MB)*100) PCT_FULL,
ROUND(SUM(MAX_MB) - (SUM(TOTAL_MB)-SUM(FREE_MB))) MB_FREE,
ROUND(SUM(MAX_MB)) MB_MAXSIZE,
ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(MAX_MB)*100) PCT_UTIL

FROM
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,0 TOTAL_MB,0 MAX_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME UNION
SELECT TABLESPACE_NAME,0 CURRENT_MB,SUM(BYTES)/1024/1024 TOTAL_MB, SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/1024/1024 MAX_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) GROUP BY TABLESPACE_NAME; ...which has no dependencies on dba_segments, and works (as far as I can tell) whatever the type of tablespace.

Full details at the link below (Tips, Administration, under 'tablespaces').

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"Raymond" <r_h_at_hetnet.nl> wrote in message
news:9rsabv$pe5$1_at_news1.xs4all.nl...


>

> One disadvantage of LMTS's I noticed: it is hard to predict the

> space needed for the next extent of segments in such a tablespace.

> The next_extent column in dba_segments is NULL (at least in V8.1.7 it is,

> don't know about 9.0.1).

> Monitoring this vital space requirement is much easier on DMTS's.

>

> Anyone with a query that reports the free space deficit in LMTS's ?

>

> Raymond.

> Oracle DBA.

>

>

Received on Thu Nov 01 2001 - 14:30:18 CST