Re: Locally Managed vs Dictionary managed tablespaces

Howard,

That is a usefull query of course. However, there can still be segments that will have a next_extent that won't fit. It would be nice to be able to detect them. I tried to figure out the algorithm that Oracle uses internally to determine the next_extent size, but it isn't always easy to understand what it is doing... The best i could think of is to assume that the next_extent is at least as big as the largest extent for a specific segment. If that one doesn't fit, trouble is ahead.

Maybe Oracle can simply fill next_extent in dba_segments in a next release.

Raymond.

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3be1b0b4$0$13585$afc38c87_at_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 Fri Nov 02 2001 - 13:25:57 CST