initial extent size of tablespaces

hi
while creating tablespaces we can specify the default storage parameters.
first tablespace app_data-
when i give the initial size as 256k, next as 256k and pctincrease as 0. logically it means that the third extent would have 256k as its size.
second tablespace app_data1
if i change the pctincrease to 50% then why does it change the initial extent size as per the output that i received.
------------------------------------------------
the first query is:
create tablespace app_data
datafile 'e:\oracle\oradata\db1\app_data.dbf' size 5m
default storage( initial 20k next 20k minextents 2 pctincrease 0
maxextents 20)
blocksize 4k minimum extent 20k logging online permanent
the output is:
TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT| MIN_EXTENTS | MAX_EXTENTS| PCT_INCREASE | MIN_EXTLEN |STATUS CONTENTS | LOGGING | EXTENT_MAN | ALLOCATIO | PLU SEGMEN
------ ---------- --------- --- -------------------------------------
APP_DATA | 4096 | 20480 | 20480 |
1 | 2147483645| 0 | 20480 |ONLINE PERMANENT | LOGGING | LOCAL | UNIFORM | NO MANUAL
--------------------------------------------------------------------
the second query is:
create tablespace app_data1
datafile 'e:\oracle\oradata\db1\app_data1.dbf' size 5m
default storage( initial 20k next 20k minextents 2 pctincrease 50
maxextents 20)
blocksize 4k minimum extent 20k logging online permanent
the output is:
TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT| MIN_EXTENTS | MAX_EXTENTS| PCT_INCREASE | MIN_EXTLEN |STATUS CONTENTS | LOGGING | EXTENT_MAN | ALLOCATIO | PLU SEGMEN
------ ---------- --------- --- -----------------------------------
APP_DATA1 | 4096 | 65536 | |
1 | 2147483645| | 65536 |ONLINE PERMANENT | LOGGING | LOCAL | SYSTEM | NO MANUAL
-------------------------------------------------------------------

why does the allocation change to system in the second case.
thanks

Extent Management is LOCAL. And Allocation Type is SYSTEM

Initial, Next have no significance in LMT.

Read the manuals.

The reason you are seeing this is is because in the first example you extent sizes are uniform, yet in your second example this would not be true hence the allocation type is defined as system which has a default extent size of 64k

Take a look at this document for clarity

http://support.oracle.co.uk/metalink...T&p_id=93771.1

HTH

Thats because the tablespace APP_DATA1 is not a uniform extent tablespace, its an auto allocate or SYSTEM Managed LMT. So no matter what for a SYSTEM or Autoallocate LMT the first few extents will be 64k in size.

Look at the ALLOCATION_TYPE column value of dba_tablespaces. You will see that its not uniform allocation but SYSTEM allocated.

HTH

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)

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.

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.

Quote:

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)

LOCAL

Quote:

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.

Expected normal behavior.

Quote:

Originally posted by ser
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.

PCTINCREASE 0 ( in 1st case ).

RTM in Technet.ORACLE.com

Abhay.

Quote:

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.

Quote:

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).

Code:

[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.

Code:

[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.

Quote:

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

Amar,
Your print_table procedure is cool. Wondering what happens if more that one row selected ??

Quote:

Originally posted by SANJAY_G
Amar,
Your print_table procedure is cool. Wondering what happens if more that one row selected ??

It will come below one another ;)

try it

Code:

CREATE OR REPLACE procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER is l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(4000); l_status integer; l_descTbl dbms_sql.desc_tab; l_colCnt number; begin dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl); for i in 1 .. l_colCnt loop dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); end loop; l_status := dbms_sql.execute(l_theCursor); while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop for i in 1 .. l_colCnt loop dbms_sql.column_value( l_theCursor, i, l_columnValue ); dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 ) || ': ' || l_columnValue ); end loop; dbms_output.put_line( '-----------------' ); end loop; exception when others then dbms_sql.close_cursor( l_theCursor ); RAISE; end; /

HTH

Quote:

Originally posted by adewri
It will come below one another

Thanks for the code. :)

Cheers.

thanks a lot to all

thass from tom kyte if I remember right

Do you have to spoil the fun http://www.dbasupport.com/forums/

Me --> http://www.stupid-boy.com/smilies/co.../overpape2.gif <--- Pando

:D :D :D :D :D