Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005

    Unanswered: tables extent management local

    In oracle 9 you use locally managed tables extent management local
    The question I have is what effect does the initial have for a tablespace that is defined with extent management local?

  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    If you are using LMT then do not specify INITIAL or NEXT for each table. They are treated as hints, not commands.

    If you do specify INITIAL for a table in a LMT, then Oracle creates N extents of the default size (specified at the LMT level, not the table level) that are equal to the size you specified for INITIAL. For example, if I have a LMT with AUTOALLOCATE so that the first group of extents is 64 KB, and I specify INITIAL = 512K, then Oracle creates 8 extents of 64 KB each.

    Most storage parameters act as hints with LMT, and not commands. Consider the following example of MINEXTENTS.

    create table test2 (col1 date) tablespace users storage (minextents 100);

    select extent_id, bytes from user_extents where segment_name = 'TEST2';

    ---------- ----------
    0 8388608
    1 8388608
    2 8388608
    3 8388608
    4 8388608
    5 8388608
    6 8388608
    7 8388608
    8 8388608
    9 8388608
    10 8388608
    11 8388608
    12 8388608

    13 rows selected.

    Oracle took the number of extents I wanted and converted that into a space request. It then reconverted the space request into a number of auto-sized extents. Since the calculated number of extents was greater than some number, Oracle switched from 64K extents to 8M extent sizes.

    I hope this helps. See the OCP study guides for lots of examples on this.
    Author, Oracle Database 10g: From Nuts to Soup

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts