| Next Extent Uncertanties [message #63350] |
Tue, 28 September 2004 12:03  |
Michael R. Hughes
Messages: 1
Registered: September 2004
|
Junior Member |
|
|
General Information :
Database => 9.2.0.5
Database block size => 32 K
OS => Solaris 9
e-mail => Michael.hughes@us.ibm.com
Phone => 775.742.5458
Problem Description :
I have created a table with the following SQL :
create table IBM_SGA_STATS
(
STAT_DATE DATE NOT NULL,
FIXED NUMBER,
VARIABLE NUMBER,
DATABASE_BUFF NUMBER,
REDO_BUFF NUMBER
)
TABLESPACE IBM_TABLESPACE
STORAGE (
INITIAL 32 M
NEXT 32 M
) ;
In previous versions of Oracle, after table creation, I would expect to get one table, with one 32 Mbyte extent. The next extent allocated would also be 32 Mbytes whenever a next extent is allocated. Lastly, this information would be stored in sys.dba_segments. However, when I perform the query :
select segment_name,
initial_extent,
next_extent,
extents
from sys.dba_segments
where segment_name = 'IBM_SGA_STATS' ;
I get the following results :
SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT EXTENTS
------------------------ ----------------------- -------------------- -------------
IBM_SGA_STATS 33554432 32
1 row selected.
Questions :
1) Why is the value of next extent null when the table creation script clearly has a “NEXT 32 M” in the storage clause ?
2) Why is the value of extents = 32 ? Does this mean that the table was created with 32 extents instead of one ?
3) Since NEXT_EXTENT is null, what will the size of the next extent really be ? The tablespace default next extent (which is quite small) or some other value ?
This may seem like a trivial question but it is making my capacity planning software go bananas.
|
|
|
|
| Re: Next Extent Uncertanties [message #63360 is a reply to message #63350] |
Wed, 29 September 2004 04:26  |
billh
Messages: 35
Registered: August 1999
|
Member |
|
|
Hello Michael,
Being on 9.2.0.5 I would guess that you have a locally managed tablespace. Check dba_tablespaces for the attributes of tablespace IBM_TABLESPACE. It should show you that you have a 1mg extent size, locally managed and allocation type of auto.
With this configuration of a tablespace you will get 32 1mg extents on your initial table allocation. Since extent sizing is auto the RDBMS does not know at creation time the next extent size, hence, NULL.
Storage parameters on tables in locally managed tablespaces have little to no significance in how disk space is allocated. The storage parameters of the tablespace take precedence.
Good Luck,
billh
|
|
|
|
| Re: Next Extent Uncertanties [message #63364 is a reply to message #63350] |
Wed, 29 September 2004 12:53 |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
Because of oracle9.2 onwards, tablesapce creation defaults to LMT (localy managed tablespaces).
That means that Oracle will allocate extents with automatically generated size regarding what you specify in the STORAGE clause of CREATE TABLE statement.
To override this behaviour, you must explicity create tablespaces as EXTENT MANAGEMENT DICTIONARY
then you can move your table to that tablespace, or drop your current table and recreate it on newly created tablespace. You can then control table/indexes storage attributes manually.
But i would recomend you LMT, it is easier.
I started as DBA since Oracle5, and i believe that LMT is very helpful.
Best luck
Oracle Consultant and remote DBA for hire.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
