Re: Next Extent Failure Notification for Locally Managed Tablespaces

From: Shawn Ferris <shawn_at_virtualsmf.net>
Date: Thu, 24 Jun 2004 15:11:09 -0600 (MDT)
Message-ID: <24163.168.215.22.23.1088111469.squirrel@mail.virtualsmf.net>

> I've looked at this one: =
> <http://www.dbasupport.com/oracle/scripts/Detailed/217.shtml>

I just so happens that I was unhappy with those similar answers.. so I ventured out to try and determine the next extent on an LMT with autoallocate. Following is what I came up with.. I think it works.. I couldn't get it to fail in my environment/ test cases.. but any further testing/ comments would be welcome! (I don't think I provisioned for UNIFORM, but that would be easy to add.. it does handle AUTO and DICTIONARY.. I could see where you might have to take the block size into consideration, but didn't care to persue.. this was developed on 8k)

HTH Shawn
Sr. Database Administrator

select

      s.owner,
      s.segment_name,
      s.segment_type,
      s.bytes,
      s.tablespace_name,
      s.extents,
      s.max_extents,
      s.initial_extent,
      decode(allocation_type,
        'SYSTEM', decode(1,
          sign(trunc(s.bytes / (1048576*1024))), 67108864,
          sign(trunc(s.bytes / (67108864))),     8388608,
          sign(trunc(s.bytes / (1048576))),      1048576,
          sign(trunc(s.bytes / (65536))),        65536),
          (s.next_extent)
      ) next_extent,
      nvl(s.pct_increase,0) pct_increase
    from
      dba_segments s,
      dba_tablespaces t
    where
      s.tablespace_name=t.tablespace_name

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jun 24 2004 - 15:57:36 CDT