Technical Tip: Fail on Two
Tuesday, March 27th, 2012 at
12:22 pm
It’s always better to know about a potential failure and fix it rather than have to scramble to find and allocate space when there is a failure. This script will help identify (but not eliminate) impending space allocation failures.
The reason it’s not foolproof is that a segment taking an extent may cause another segment to not have enough space for it’s next extent. Running this script daily will catch most problems and if you “know your data” it will give you a heads up on what other segments will soon need attention.
--****************************************************************** -- -- Script Name: fail_on_two.sql -- -- Purpose: Determine if a Table, Index or Cluster segment will fail -- when taking a next extent or if it will fail on taking 2 -- extents
-- -- Description: SELECT 1 -- Report on segment that will fail when taking 1 extent -- UNION -- SELECT 2 -- Report that there are no segments that will cause a failure -- UNION -- SELECT 3 -- Report on segment that will fail when taking 2 extents -- UNION -- SELECT 4 -- Report that there are no segments that will cause a failure -- when taking 2 extents -- -- Select statements 2 and 4 are optional but are included to produce -- output if everything is OK -- -- Author: Scott Nelson -- Results Through Technology, Inc. -- --****************************************************************
set feedback off set echo off set feedback off set echo off set pagesize 60 set linesize 132 col bkup hea 'Backup Date' format a15 col numfiles hea 'Number of Files' format 999 col problem_segment hea 'SEGMENT IN TABLESPACE' format a40 col next_ext_2 hea 'NEXT_EXTENT Info' format 999,999,999 col next_ext hea 'NEXT_EXTENT Info' format 999,999,999
PROMPT Checking for fail on next extent AND fail on taking two extents
set verify off
SELECT Segment_Type||' '||Segment_Name||' in '||
ts.ts#||':'||Tablespace_Name problem_segment,
next_Extent*2 next_ext_2
FROM dba_segments ds, sys.ts$ ts
WHERE ds.segment_type in ('INDEX','TABLE','CLUSTER')
AND ds.next_extent*2 > (SELECT nvl(sum(dfs.bytes),0)
FROM dba_free_space dfs
WHERE dfs.bytes >= ds.next_extent
AND ds.tablespace_name = dfs.tablespace_name)
AND 1 < (SELECT count(segment_name)
FROM dba_segments ds2
WHERE ds2.tablespace_name = ds.tablespace_name)
AND ds.tablespace_name = ts.name
UNION
SELECT v.name||' has no fail on 2 extents' problem_segment,
0 next_ext
FROM v$database v
WHERE not exists
(SELECT 'x'
FROM dba_segments ds, sys.ts$ ts
WHERE ds.segment_type in ('INDEX','TABLE','CLUSTER')
AND ds.next_extent*2 > (SELECT nvl(sum(dfs.bytes),0)
FROM dba_free_space dfs
WHERE dfs.bytes >= ds.next_extent
AND ds.tablespace_name = dfs.tablespace_name)
AND 1 < (SELECT count(segment_name)
FROM dba_segments ds2
WHERE ds2.tablespace_name = ds.tablespace_name)
AND ds.tablespace_name = ts.name)
UNION
SELECT Segment_Type||' '||Segment_Name||' in '||
ts.ts#||':'||Tablespace_Name problem_segment,
next_extent next_ext
FROM dba_segments ds, sys.ts$ ts
WHERE ds.segment_type in ('INDEX','TABLE','CLUSTER')
AND ds.next_extent > (SELECT nvl(sum(dfs.bytes),0)
FROM dba_free_space dfs
WHERE dfs.bytes >= ds.next_extent
AND ds.tablespace_name = dfs.tablespace_name)
AND 1 < (SELECT count(segment_name)
FROM dba_segments ds2
WHERE ds2.tablespace_name = ds.tablespace_name)
AND ds.tablespace_name = ts.name
UNION
SELECT v.name||' has no fail on next extent' problem_segment,
0 next_ext
FROM v$database v
WHERE not exists
(SELECT 'x'
FROM dba_segments ds, sys.ts$ ts
WHERE ds.segment_type in ('INDEX','TABLE','CLUSTER')
AND ds.next_extent > (SELECT nvl(sum(dfs.bytes),0)
FROM dba_free_space dfs
WHERE dfs.bytes >= ds.next_extent
AND ds.tablespace_name = dfs.tablespace_name)
AND 1 < (SELECT count(segment_name)
FROM dba_segments ds2
WHERE ds2.tablespace_name = ds.tablespace_name)
AND ds.tablespace_name = ts.name)
/
Filed under: Technical Tips
Like this post? Subscribe to my RSS feed and get loads more!
