|
|
| Re: Change the initial extent size with Locally managed tablespace [message #150662 is a reply to message #150609] |
Thu, 08 December 2005 23:56   |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
| Quote: |
AUTOALLOCATE causes the tablespace to be system managed with the smallest extent size being 64K. There is an increase in initial space allocated for objects in autoallocated tablespaces. This is because the objects have a minimum size of two blocks in dictionary-managed tablespaces, whereas in autoallocated locally managed tablespaces, the minimum object size is 64K.
You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or TEMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL.
|
now see this
SQL> create tablespace dumm datafile '/data/dumm01.dbf' size 10M
2 autoallocate
3 default storage
4 (initial 50K
5 next 100K
6 pctincrease 0);
create tablespace dumm datafile '/data/dumm01.dbf' size 10M
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy
I think its clear now.
regards,
tarun
|
|
|
|
| Re: Change the initial extent size with Locally managed tablespace [message #150709 is a reply to message #150609] |
Fri, 09 December 2005 03:46 |
reena_ch30
Messages: 100
Registered: December 2005
|
Senior Member |
|
|
Hi,
I create an LMT with autoallocate option (i.e size 64kb default). I take 2 cases.
Case 1: I create a table in that tablespace without any storage clause.It assigns 1 extent with 64K.
Case 2. I create a table in that tablespace with initial extent as 72K in storage.It assigns 2 extents with 64K each.
Please justify.
Regards,
reena
|
|
|
|
| Re: Change the initial extent size with Locally managed tablespace [message #150711 is a reply to message #150709] |
Fri, 09 December 2005 04:02 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Exactly here is the catch -- ( you got it right).
It is the default behaviour of Oracle.
-->Case 1: I create a table in that tablespace without any storage clause.It assigns 1 extent with 64K.
The above happened because the default extent size for the tablespace was 64k and since the table is created in that tablespace so it followed the default storage.
--> Case 2. I create a table in that tablespace with initial extent as 72K in storage.It assigns 2 extents with 64K each.
Now , when you specified the storage clause with initial as 72k the Oracle will automatically assigns two extents of 64K each to the table since 72K > 64 k (minimum size of an extent) .
And for the remaining 12K , the table will get another extent of 64K.
and at last this all is happening just to save fragmentation in the tablespace.
regards,
tarun
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
