-
initial extent size
Hi,
I have the following tablespace:
SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, MIN_EXTENTS,
EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT
FROM USER_TABLESPACES;
TABLESPACE BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS EXTENT_MAN
---------- ---------- -------------- ----------- ----------
EXAMPLE 8192 65536 1 LOCAL
When I query my tables, I have the following:
SELECT TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, MIN_EXTENTS
FROM USER_TABLES;
TABLE_NAME TABLESPACE INITIAL_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- -----------
TABLE_E EXAMPLE 16384 1
If you look at the initial extent of TABLE_E, it shows 16384. I am wondering how oracle derived this. I've read it somewhere that in a Locally-Managed Tablespace, the minimum extent is 3 data blocks. In my example above, the data block is 8K, so I am assuming that any table created in the tablespace will have at least 24K. But this does not seem to be the case as showed by TABLE_E which has 16K.
Any Ideas?
Thanks
toshi
-
Well is this Happening with all tables or just this one table...
This is possible if you have explicitly mention the storage parameter in the create table command like.
Code:
SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, MIN_EXTENTS,
2 EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT
3 FROM USER_TABLESPACES WHERE TABLESPACE_NAME LIKE 'EXAMPLE';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS EXTENT_MANAGEMENT SEGMENT_SPACE_MANA
--------------- ---------- -------------- ----------- ------------------------------ ------------------
EXAMPLE 8192 65536 1 LOCAL AUTO
SQL> CREATE TABLE TEST (x number)
2 STORAGE (INITIAL 16k NEXT 16k MINEXTENTS 1);
Table created.
SQL> SELECT TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, MIN_EXTENTS
2 FROM USER_TABLES where table_name like 'TEST';
TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT MIN_EXTENTS
---------- --------------- -------------- -----------
TEST EXAMPLE 16384 1
Here you see i have 8192 BLOCK_SIZE and i have create a table the way you have is TEST table having 16384 as initial extent...
Just check how you created the table...
Default will be like
Code:
SQL> create table TEST1 (x number)
Table created.
SQL> SELECT TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, MIN_EXTENTS
2 FROM USER_TABLES where table_name like 'TEST1';
TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT MIN_EXTENTS
---------- --------------- -------------- -----------
TEST1 EXAMPLE 65536 1
HTH
Amar
"There is a difference between knowing the path and walking the path."
-
When you created table, the first extent is (INITIAL * MINEXTENT) that you specific at SEGMENT or TABLESPACE level.
Regards,
P.Peach
-
Re: initial extent size
Originally posted by toshi
If you look at the initial extent of TABLE_E, it shows 16384.
Yes, it shows 16K, because this initial size was requested in your CRATE TABLE command. However the actual size of the initial (and all subsequent) extents created for this table in your LMT tablespace is 64K, not 16K. You can check this by querying USER_EXTENTS, eg
SELECT segment_name, tablespace_name, extent_id, bytes
FROM user_extents
WHERE segment_name = 'TABLE_E' AND segment_type = 'TABLE;
Read the following thread for more details:
http://www.dbasupport.com/forums/sho...threadid=33703
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thank you all for your ideas!
-
Re: initial extent size
Originally posted by toshi
Hi,
I've read it somewhere that in a Locally-Managed Tablespace, the minimum extent is 3 data blocks.
Thanks
toshi
I know on a LMT UNIFORM ts, the smallest UNIFORM extent you can have is 5 * Blocksize.
OCP 8i, 9i DBA
Brisbane Australia
-
Hi..
In LMT,If you dont specify the initial extent size oracle automatically allocates it which is equal to db_block_size * db_file_multiblock_read_count.In your case its 8K * 8K ie 64K.
Since your tablespace is locally managed,the tables created on it will of extent size 64K.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|