Tom

I have a question regarding the MOVE clause of alter table.
We wanted to move from a dictionary managed tablespace to a locally managed one. To do this I:

1) created the local tablespace using uniform extents of 40K
2) I then used alter table move command to move the table into the new tablespace


So in the DMT it looks like:

11:25:57 OPT1@OP4>exec print_Table('select * from dba_segments where segment_name = ''ATTENDANCE''')
;
OWNER : OPT1
SEGMENT_NAME : ATTENDANCE
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : DATA
HEADER_FILE : 14
HEADER_BLOCK : 12482
BYTES : 10035200
BLOCKS : 1225
EXTENTS : 1
INITIAL_EXTENT : 10002432
NEXT_EXTENT : 10002432
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
RELATIVE_FNO : 14
BUFFER_POOL : KEEP
-----------------

PL/SQL procedure successfully completed.



Then I did:



11:26:59 OPT1@OP4>alter table attendance
11:27:16 2 move tablespace DATA_S;

Table altered.

Elapsed: 00:00:00.05
11:27:26 OPT1@OP4>exec print_Table('select * from dba_segments where segment_name = ''ATTENDANCE''')
;
OWNER : OPT1
SEGMENT_NAME : ATTENDANCE
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : DATA_S
HEADER_FILE : 25
HEADER_BLOCK : 1234
BYTES : 10035200
BLOCKS : 1225
EXTENTS : 245
INITIAL_EXTENT : 10002432
NEXT_EXTENT : 40960
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
RELATIVE_FNO : 25
BUFFER_POOL : KEEP
-----------------

PL/SQL procedure successfully completed.



I was hoping that this command would actually reset the HWM of the table so that the table itself would shrink to its exact size with no
extra storage.


However looking at dba_Segments after the move it seems that it has not shrunk, and the initial extent is ~10M, NEXT = 40K
How can the initial extent not be equal to the NEXT extent if we have uniform extents??

Why did the alter table move command not shrink the table?

Now if instead I do:

11:36:22 OPT1@OP4>create table T1
11:38:10 2 as select *
11:38:13 3 from attendance;

Table created.

Elapsed: 00:00:00.01
11:38:21 OPT1@OP4>drop table attendance;

Table dropped.

Elapsed: 00:00:00.03
11:38:30 OPT1@OP4>create table ATTENDANCE
11:38:39 2 as select * from
11:38:42 3 T1;

Table created.


I now move the table again since the default tablespace of the current user
is not the correct one:


Elapsed: 00:00:00.01
11:38:47 OPT1@OP4>alter table attendance move
11:38:54 2 tablespace DATA_S;

Table altered.

Elapsed: 00:00:00.00
11:39:05 OPT1@OP4>exec print_Table('select * from dba_segments where segment_name = ''ATTENDANCE''')
;
OWNER : OPT1
SEGMENT_NAME : ATTENDANCE
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : DATA_S
HEADER_FILE : 25
HEADER_BLOCK : 1234
BYTES : 40960
BLOCKS : 5
EXTENTS : 1
INITIAL_EXTENT : 40960
NEXT_EXTENT : 40960
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
RELATIVE_FNO : 25
BUFFER_POOL : DEFAULT
-----------------

PL/SQL procedure successfully completed.



So now I get what I want ie 1 extent and INITIAL=NEXT.
Why did I not see that with the first alter table move.... command?
Is it because the HWM of the table was taken into account?


If this is the case how can I deallocate the unused space with one move command?

Thanks for your help.