Create tablespaces in ORACLE 10g

[Enrico Venturi]

Hi colleagues,
I run
SQL> create tablespace PM_HIST_INDEX datafile '/usr/Systems/1354RM_12_7.4.7_Master/databases/dbsnml/data/pm_hist_index01.dbf' size 170 M
default storage(
initial 3M
next 1M
maxextents 505
pctincrease 20
);


but after the creation I found:

SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE from dba_tablespaces where TABLESPACE_NAME='PM_HIST_INDEX';

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
65536 2147483645


why such difference?
it seems that the create tablespaces didn't get the input parameters ....

thanks
Enrico

[Volker Borowski · Accepted solution]

Hi,

I guess this is because 10g defaults to local managed tablespaces and not to dictionary managed tablespaces, so the extent management parameters are obsolete.

Check the extent_management column in dba_tablespaces.

If you like to stay dictionary managed for whatever unknown reason, you have to specify that in your create tablespace statement.

Hope this helps
volker

[Yogeeraj_1]

Hi Enrico,

As mentioned by Volker, 10g defaults to local managed tablespaces. This clause is valid only for a dictionary-managed tablespace. Also, Oracle strongly recommends that you create tablespaces that are locally managed rather than dictionary managed.

The storage_clause is interpreted differently for locally managed tablespaces. At creation, Oracle ignores MAXEXTENTS and uses the remaining parameter values to calculate the initial size of a segment. Segments that are created on the tablespace itself and does not impact on the tablespace iself!

If we review the syntax diagram for creating tablespaces:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2132654

You can now realise that the "default storage" clause in your commands has been ignored.

In fact, the storage_clause syntax is as follows:
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/clauses008.htm#i997450


For more information, please see:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

and also:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/physical.htm#CNCPT003


hope this helps too!
kind regards
ygoeeraj

[Yogeeraj_1]

hi again,

You can test that objects that you create on the tablespace PH_HIST_INDEX are taking the default values you have specified when creating the tablespace.

You will observe that Oracle allocates space for the table based on the STORAGE parameter values as follows:

The INITIAL value is 3M, so the size of the first extent is 3M.

If the table data grows to exceed the first extent, then Oracle allocates a second extent. The NEXT value is 1M, so the size of the second extent will be 1M.

If the table data subsequently grows to exceed the first two extents, then Oracle allocates a third extent. The PCTINCREASE value is 20, so the calculated size of the third extent is 20% larger than the second extent, or 1.2M.

If the table data continues to grow, then Oracle allocates more extents, each 20% larger than the previous one.

You can do the tests youself.

Below an example of one tablespace and the values obtained when queried:
===============================
YD@mydb.mu>select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE from
dba_tablespaces where TABLESPACE_NAME='ISMALL01';

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
______________ ___________ ___________ ____________
1048576 1048576 2147483645 0

Elapsed: 00:00:00.47
YD@mydb.mu>select dbms_metadata.get_ddl('TABLESPACE','ISMALL01') from dua
l;

DBMS_METADATA.GET_DDL('TABLESPACE','ISMALL01')
________________________________________________________________________

CREATE TABLESPACE "ISMALL01" DATAFILE
'/d01/oracle/oradata/mydb/ismall01.dbf' SIZE 1073741824
AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 SEGMENT SPACE MANAGEMENT AUTO



Elapsed: 00:00:08.55
YD@mydb.mu>




kind regards
yogeeraj