LMT Autoallocate initial, tot # of extents

From: Barbara Baker <barb.baker_at_gmail.com>
Date: Mon, 6 Jun 2005 10:19:09 -0600
Message-ID: <47a6f72b050606091954e0f98c@mail.gmail.com>

Oracle 9.2.0.4 Solaris 9 Blocksize 8192

I'm attempting to move (alter table .. move tablespace) several tables from DMT to autoallocate LMT for a database migrated from 8.1.7.4 to 9.2.0.4. (Agonized over autoallocate vs. uniform size.=20 Perhaps I made the wrong choice.) Anyhow . . .

I have 1 table in particular that's 2752 megs 185 extents in the DMT.=20  After I move it to the LMT, it's 2757M, 211 extents. Why so many extents?? I'm not as concerned with the # of extents as I am in understanding what it's doing.

I've tried several initial and next storage specifications.=20 Regardless of what initial I specify, it makes the initial 1024k. I'm NOT using parallel. Since I know this table will be nearly 3 gigs, I'd like to start out with a large extent size. Is there an "initial_yes_I_really_mean_it" parameter I missed in the doc's??

Here's my create tablespace command, and my alter table move command (showing all of the initial sizes I've tried and commented out when they didn't work):

CREATE TABLESPACE ADVLMT
    DATAFILE '/class14db/orarecovery/cls17trn/advlmt01.dbf' SIZE 2048000K AUTOEXTEND OFF,

                       =20

'/class14db/orarecovery/cls17trn/advlmt02.dbf' SIZE 2048000K AUTOEXTEND OFF
    EXTENT MANAGEMENT LOCAL=20
    AUTOALLOCATE LOGGING ONLINE
    SEGMENT SPACE MANAGEMENT AUTO alter table advdb.FORXML_BCK
 move tablespace ADVLMT=20
storage (initial 65536k next 65536k)

----storage (initial 16384k next 16384k)
----storage (initial 8192k )
--- storage (initial 8192k next 8192k)
----storage (initial 1024k )
---- parallel 2=20

 nologging;

SQL> select segment_name, extent_id, bytes from dba_extents 2 where tablespace_name=3D'ADVLMT' and segment_name =3D 'FORXML_BCK'

                          EXTENT_ID      BYTES                             =


    =20

------------------------ ---------- ----------                             =


    =20

FORXML_BCK                        0    1048576                             =


    =20

FORXML_BCK                        1    1048576                             =


    =20

FORXML_BCK                        2    1048576                             =

=20

 Switches to 8192k after 64 extents

FORXML_BCK                       63    1048576                             =


    =20

FORXML_BCK                       64    8388608                             =


    =20

FORXML_BCK                       65    8388608                             =

=20

Switches to 65,536k after 184 extents

FORXML_BCK                      183    8388608                             =


    =20

FORXML_BCK                      184   67108864                             =


    =20

FORXML_BCK                      185   67108864                             =

=20

---

                Bytes  Ext     Init     Next  Min
                  Meg  nts  Ext Meg  Ext Meg Exts

------------ -------- ---- -------- -------- ---- FORXML_BCK
ADVLMT 2,752 211 64 1 Thanks for any help!
Barb

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 06 2005 - 12:24:40 CDT