| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Next Extent Failure Notification for Locally Managed Tablespaces
I searched at the old Oracle-L archives ( http://www.fatcity.com/ ),
year 2003, word "autoallocate" in all fields and found a thread on the
subject from last year. I will take the liberty of copying one of my
posts from that thread, confirming that your code should be correct:
----- c&p from oracle-l ----
From: "Jacques Kilchoer"
Date: Tue, 30 Sep 2003 16:31:46 -0700
I think Jonathan Lewis has explained the algorithm before, but it's also something that we have investigated here. The algorithm (ignoring some details) is: There will be 4 extent sizes used, 64K, 1M, 8M, 64M As long as object allocation is 1M or less, 64K extent sizes are used, When object allocation is between 1M and 64M, 1M extent sizes are used. When object allocation is between 64M and 1G, 8M extent sizes are used. When object allocation is more than 1G, 64M extent sizes are used.
However, when you initially create the object, the extents are
determined by figuring out the space allocated to the newly created
object taking into account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS
storage parameters. So the object might start off with 1M extents
instead of starting off with 64K extents. The algorithm is similar to
the one outlined above but it is more complicated. The NEXT and
PCTINCREASE seem to be ignored after the object is created.
e.g.
create table ... tablespace locally_managed_autoallocate
storage (initial 1M next 512K minextents 15 pctincrease 0) ... ;
Initial allocation will be 1M + (15 - 1) * 512K = 8M
When you create the table, you will see eight extents, each of one
megabyte.
There are additional wrinkles ...
----- c&p from oracle-l ----
-----Original Message-----
Shawn Ferris
> 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
-- 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 - 16:11:01 CDT
 |
 |