List,
There was a question as to how to identify objects that will fail to extend?
This is what we do.
SELECT owner, tablespace_name, segment_name, next_extent
FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
AND next_extent > ( SELECT max(bytes)
FROM dba_free_space
WHERE tablespace_name=ds.tablespace_name)
ORDER BY 1, 2;
-----Original Message-----
Sent: Thursday, November 14, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Until a whole mass of astrological confluences happen, I'm stuck with
dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And we're having
some space/growth issues right now that I want (need!) to be more
proactive
with. So, based on several factors -- most political -- I want to run a
daily report that tells me when a segment will not be able to extend
twice.
(We're already running the single extent failure hourly.)
After looking on the net, I found some queries to do this, but all I saw
were severely flawed. So, I rolled my own. The only problem I can see
with
it for dictionary TSs is when the RANK() has multiple matches for first
and
second (e.g. TS "MY_BIG_TS" has it's largest contiguous free spaces of
40M,
10M, and 10M). Unfortunately, I'm stumped as to how to prevent this.
Anyone care to comment on this load of SQueaL? Thx! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
USA
SELECT ds.owner, ds.segment_name, ds.segment_type, ds.tablespace_name,
ds.next_extent/1024 "Next ext", fs2.max_free/1024 "Max Free",
fs2.min_free/1024 "2nd Max Free", fs2.free_spaces
FROM dba_segments ds,
(
SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
min_free,
count(*) free_spaces
FROM
(
SELECT tablespace_name, bytes,
RANK() OVER (PARTITION BY tablespace_name
ORDER BY tablespace_name, bytes DESC)
byte_rank
FROM dba_free_space
)
WHERE byte_rank < 3
GROUP BY tablespace_name
) fs2
WHERE ds.segment_type IN ('INDEX','TABLE')
AND fs2.tablespace_name = ds.tablespace_name
AND (
((ds.next_extent > fs2.min_free OR fs2.free_spaces < 2)
AND ds.next_extent*2 > fs2.max_free)
OR ds.next_extent > fs2.max_free
)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
