Home » SQL & PL/SQL » SQL & PL/SQL » Table Sizing
Table Sizing [message #346771] Tue, 09 September 2008 12:23 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, just a question on initial_extent, why is that even though I specified 8k as the initial, it still set it to 16k?


SQL> create table t(x char(1000))
  2  storage (
  3  initial 8k
  4  next   8k
  5  minextents 1
  6  );

Table created.

SQL>  select initial_extent, min_extents
  2  from user_tables
  3  where table_name = 'T';

INITIAL_EXTENT MIN_EXTENTS
-------------- -----------
         16384           1



Thanks!
Re: Table Sizing [message #346774 is a reply to message #346771] Tue, 09 September 2008 12:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
database is probably 16k blocksize or LMT with either 16k uniform extents or just plain oracle managed size.
Re: Table Sizing [message #346776 is a reply to message #346771] Tue, 09 September 2008 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The minimum size is 2 blocks.

Regards
Michel
Re: Table Sizing [message #346780 is a reply to message #346774] Tue, 09 September 2008 12:43 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi, i've check this:

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_block_size                        integer     8192



@Michel/Joy

1) LMT with either 16k uniform extents or just plain oracle managed size.

LMT - this is the extent_management right? you mean this might have force it to become 16k?
What do you mean by "plain oracle managed size"?


2) The minimum size is 2 blocks.
How will I check this?

Thank you very much again. Also, when I checked on the script from toad, the initial was set to 16k

STORAGE    (
            INITIAL          16K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
Re: Table Sizing [message #346781 is a reply to message #346780] Tue, 09 September 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2) The minimum size is 2 blocks.
How will I check this?

Reading the documentation, it is in SQL Reference.

Regards
Michel
Re: Table Sizing [message #346789 is a reply to message #346781] Tue, 09 September 2008 13:05 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks, I'm a bit confused on the documentation.

Quote:

In locally managed tablespaces, Oracle uses the value of INITIAL in conjunction with the size of extents specified for the tablespace to determine the object's first extent. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then Oracle creates five 1M extents.



On my tablespace it is LMT I think, and when I try to create a table using the tablespace's default:

create table t_auto(x char(10))

SQL> select initial_extent, min_extents
  2  from user_tables
  3  where table_name = 'T_AUTO'
  4  /

INITIAL_EXTENT MIN_EXTENTS
-------------- -----------
         65536           1



It inherited the tablespace default of around 64K (8 * 8k). How did my 8k initial extent calculated to be 16K? Thanks again.
Re: Table Sizing [message #346796 is a reply to message #346789] Tue, 09 September 2008 13:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, this is LMT, but it is AUTOALLOCATE (that's the word I was looking for before), not UNIFORM extents.
Re: Table Sizing [message #346807 is a reply to message #346796] Tue, 09 September 2008 13:54 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks, that may be it also. I've read this
but still can't find on how it came up with two extents.

Also, I tried querying this:

SQL> select segment_name, segment_type, extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'T';

SEGMENT_NAME SEGMENT_TYPE   EXTENT_ID  BYTES     BLOCKS
-----------  -----------    ---------- -------   ----------
T            TABLE               0      65536          8



Does this mean it used 8 blocks, even though it says that the initial extent is 16k (based on the previous query). Why it consumed 8blks even though the table is empty? Is this because of the LMT again? BTW, the tablespace is on AUTOALLOCATE. Thanks!
Previous Topic: Data Updation.
Next Topic: Update Statement Performance Issue
Goto Forum:
  


Current Time: Mon Dec 05 21:05:11 CST 2016

Total time taken to generate the page: 0.05186 seconds