initial extent size of tablespaces

[adewri]

Originally posted by ser
by default the extent management is local in oracle9i.
but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)

Remember that when the system tablespace is Locally managed tablespace then all tablespaces created will be locally managed. You are confusing yourself with the Allocation Type. The question should be whether it would be an uniform allocated or system(auto) allocated tablespace. If you do not mention uniform clause while creating the tablespace the tablespace will be System allocated ie the extent management is autoallocate.

In Auto allocate the first few extents size will be 64K then 1M, then 8M and then 64M and so on. But in case of uniform allocation the tablespace will have uniform extents only ie extents of same size.

Originally posted by ser

if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.

You don't need to give storage clause, that does not work in Locally Managed Tablespaces, its either uniform extent or system(autoallocate).

[email protected]D> create tablespace uniform_tbs datafile 'E:\ORACLE\ORADATA\ACME\uniform_tbs.dbf' size 10M
  2  extent management local uniform size 2M;

Tablespace created.

[email protected]D> exec print_table('select * from dba_tablespaces where tablespace_name=''UNIFORM_TBS''');
TABLESPACE_NAME               : UNIFORM_TBS
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 2097152
NEXT_EXTENT                   : 2097152
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  : 0
MIN_EXTLEN                    : 2097152
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : UNIFORM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : MANUAL
DEF_TAB_COMPRESSION           : DISABLED
-----------------

PL/SQL procedure successfully completed.

See here i have mentioned uniform extent of 2M so all extent allocation will always be 2M for uniform_tbs tablespace.

[email protected]D> create tablespace system_tbs datafile 'E:\ORACLE\ORADATA\ACME\system_tbs.dbf' size 10M
  2  extent management local;

Tablespace created.

[email protected]D> exec print_table('select * from dba_tablespaces where tablespace_name=''SYSTEM_TBS''');
TABLESPACE_NAME               : SYSTEM_TBS
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : MANUAL
DEF_TAB_COMPRESSION           : DISABLED
-----------------

PL/SQL procedure successfully completed.

See here i have not mentioned uniform extent clause so the extent allocation will always be autoallocate for system_tbs tablespace.

Originally posted by ser

jovery i do not have access to metalink , hence i couldn't access it. moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.

The server selected uniform for the first because the INITIAL=NEXT and PCTINCREASE=0 is what a Uniform extent will have ie all extents are uniform. But in the second case you have INITAIL=NEXT and PCTINCREASE=50, here because of the clause PCTINCREASE=50 the extents allocations cannot be uniform. So the only other option left is Autoallocate and server makes it an System(Autoallocate) managed LMT.

Do not use storage clauses as it doesnot work with LMT. It has to be either UNIFORM LMT or SYSTEM MANAGED LMT.

And do not confuse yourself between Dictionary managed and SYSTEM Managed they are two different things.

HTH