storage clauses

[smacattack]

As i understand it the oracle default storage is overwritten by the tablespace storage clauses which in turn gets overwritten by the tables storage clauses!

If the table storage params are less than the tablesapce params will it take the largest amount.

e:g

table initial extent = 131072

tablespace initial extent = 163840

Should it take the tablespace initial extent for all tables within the tablespace.?

Please advice as i am confused as to why a table has all extents 163840 while the table was created with next extents of 131072.

Many thanks.

 

[smacattack]

Sorry the table in mention is a index but whether this makes any difference to the arguement i don't know!

 

[ThomVF]

From ORACLE technet:
When you create a cluster, index, rollback segment, snapshot, snapshot log, table, or partition, you can specify values for the storage parameters for the segments allocated to these objects. If you omit any storage parameter, Oracle uses the value of that parameter specified for the tablespace.

One explanation: Is it possible that the index storage parameters were added sometime after it was created? If INITIAL and/or NEXT were ommitted at creation, the index would take on the INITIAL 163840 from the tablespace defaults. This storage argument could have been changed to 131072 after???

I've [also] seen some weirdness in 8.1.7 regarding storage clauses (for tables). In some cases, it appears to ignore the storage clause. Has anyone else seen cases like this for indexes ?


=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

The size of the tablespace defaults is irrelevant. Oracle will always use size parameters if they are explicitly mentioned in the "create table" or "create index" statement. If they are omitted, Oracle will use the tablespace defaults.

Most likely you are mistaken about the next extents parameter. Your index was probably created using the tablespace defaults.

Incidentally, if you don't like the current value of next extents, it can be easily changed with an "alter index" command.

 

[carp]

Don't forget - Oracle will also round up to the next multiple of DB_BLOCK_SIZE if the extent size you specify is not a multiple of this parameter. This may also cause the extent size to differ from what you specified.

 

[ThomVF]

Also! I just realized that if this is a "locally managed" tablespace, the storage clause will not be used....that was why I saw weirdness with my storage clauses - I had no idea the DBA was using locally versus dictionary-managed tablespaces.

Check w/ your DBA and let us know what you have. "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com