selecting initial_extent and next_extent from dba_tables is incorrect. It
tells you what you asked for, NOT what Oracle allocated. You need to look
at dba_extents:
SQL> select tablespace_name, initial_extent, next_extent,
extent_management, allocation_type, MIN_EXTLEN
from dba_tablespaces where tablespace_name in ('SMALLTBL', 'LARGETBL');
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
ALLOCATIO MIN_EXTLEN
------------------------------ -------------- ----------- ----------
--------- ----------
LARGETBL 5242880 5242880
LOCAL UNIFORM 5242880
SMALLTBL 65536 65536
LOCAL UNIFORM 65536
SQL> create table test tablespace smalltbl storage(initial 2K next 2K) as
select * from dba_objects;
Table created.
SQL> select tablespace_name, initial_extent, next_extent from dba_tables
where table_name='TEST';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
SMALLTBL 16384 65536
1 row selected.
SQL> select tablespace_name, bytes initial_extent from dba_extents where
segment_name='TEST' and extent_id <= 2;
TABLESPACE_NAME INITIAL_EXTENT
------------------------------ --------------
SMALLTBL 65536
SMALLTBL 65536
SMALLTBL 65536
SQL> alter table test move tablespace largetbl;
Table altered.
SQL> select tablespace_name, initial_extent, next_extent from dba_tables
where table_name='TEST';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
LARGETBL 16384 5242880
1 row selected.
SQL> select tablespace_name, bytes initial_extent from dba_extents where
segment_name='TEST' and extent_id <= 2;
TABLESPACE_NAME INITIAL_EXTENT
------------------------------ --------------
LARGETBL 5242880
LARGETBL 5242880
At 07:34 AM 3/25/2003 -0800, you wrote:
><mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]> select tablespace_name, initial_extent,
>next_extent, extent_management from dba_tablespaces
> 2 where tablespace_name in ('TAB128K02', 'TAB4M02');
>
>TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
>------------------------------ -------------- ----------- ----------
>TAB4M02 4194304 4194304 LOCAL
>TAB128K02 131072 131072 LOCAL
>
><mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]> create table test tablespace tab128k02 as
>select * from dba_objects;
>
>Table created.
>
><mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]> select tablespace_name, initial_extent,
>next_extent from dba_tables where table_name='TEST';
>
>TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
>------------------------------ -------------- -----------
>TAB128K02 131072 131072
>
><mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]> alter table test move tablespace TAB4M02;
>
>Table altered.
>
><mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]> select tablespace_name, initial_extent,
>next_extent from dba_tables where table_name='TEST';
>
>TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
>------------------------------ -------------- -----------
>TAB4M02 131072 4194304
>We are on 8.1.7.4 on HP-UX 11.0
>
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
********************
This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.
Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement. Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.
