Qs about Storage clause parameters

[ly]

Can anyone help me on the following questions:

Q1. Does storage clause only used in dictionary managed tablespace?

Q2. CREATE TABLE test_storage
( . . . )
STORAGE (INITIAL 100K NEXT 120K
MINEXTENTS 2 MAXEXTENTS 5
PCTINCREASE 50);

In this case, what is the 1st extent size? 100k or 200k? What's the 2nd? 120k or 240k (because the MINEXTENTS is 2)?
If the answer is "100k and 120k", what's the use of MINEXTENTS?

Thanks very much!

[jmodic]

A1.
No, it is also used in LMT

A2.
First extent will be 100K, second will be 120K (if oracle won't round the values up to the 5 blocks multiplication). The usage of MINEXTENTS - you are telling oracle that you want to prealocate 2 extents at table creation time. Those two extents will be sized based on INITAL and NEXT parameters.

[ly]

Jmodic, thanks very much for your quick reply.

However for Q1, pls see the following result running in 9i and 8i:
===========================================================
In 9i, the default is LMT
SQL> create tablespace local_storage datafile 'd:\testlocal.dbf' size 2m
2 default storage (initial 100k next 120k minextents 2);

Tablespace created.

SQL> select INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, EXTENT_MANAGEMENT from dba_tablespaces
2 where TABLESPACE_NAME='LOCAL_STORAGE';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS EXTENT_MAN
-------------- ----------- ----------- ----------
65536                   &nbsp 1 LOCAL

Though the storage can be used, but it doesn't really affect the space allocating.


In 8i, the default is DMT
SQL> create tablespace local_storage datafile '$ORACLE_BASE/oradata/local_storage.dbf'
2 size 2m extent management local
3 default storage (initial 120k);
create tablespace local_storage datafile '$ORACLE_BASE/oradata/local_storage.dbf'
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

error return
===========================================================

For Q2:
You said "Oracle will prealocate 2 extents at table creation time", do you mean oracle will preallocate "100k+120k" space, because the first 2 extents is "100k and 120k". Am I right?

Thanks!

[Edited by ly on 10-10-2002 at 05:01 AM]

[snmns]

Hello

This is to bring to the notice of all who are using Sybex for 031 test preparation.

On page 195, Sybex mentions


Oracle ILT recommends locally managed extents for Temporary tablespace, while Sybex advises against it.

I do not know if Sybex has published any errata on this points.

Request you all to clarify these discrepancies, as I am planning to take this test soon.

Thanks

[snmns]

On page 195, Sybex mentions should read as follows:

" You cannot specify DEFAULT STORAGE, TEMPORARY, and MINIMUM EXTENT clauses of the CREATE TABLESPACE in a LMT "

[stecal]

That would be consistent with Oracle's documentation on create tablespace.

http://tahiti.oracle.com/pls/db92/db...?section=99122

If you do not specify the extent_management_clause, then Oracle interprets the COMPATIBLE setting, the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management. If the COMPATIBLE initialization parameter is less than 9.0.0, then Oracle creates a dictionary managed tablespace. If COMPATIBLE = 9.0.0 or higher:

If you do not specify the DEFAULT storage_clause at all, then Oracle creates a locally managed autoallocated tablespace.
If you did specify the DEFAULT storage_clause:
If you specified the MINIMUM EXTENT clause, then Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
If you did not specify MINIMUM EXTENT clause, then Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

Restrictions on extent management:

A permanent locally managed tablespace can contain only permanent objects. If you need a locally managed tablespace to store temporary objects (for example, if you will assign it as a user's temporary tablespace, use the CREATE TEMPORARY TABLESPACE statement.

If you specify LOCAL, then you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.

[snmns]

Hello Stecal


" If you specify LOCAL, then you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY. "

I apologize for harping on above statement but can I safely assume for examination purpoe that above statement applies to both Temporary and Permanenet tablespaces.