Move Tablespace Query

Can someone explain this one:

I have a tablespace with 2 datafiles
SQL> select file_name,file_id,bytes/1048576 from dba_data_files where
tablespace_name='SEBEVT01';

FILE_NAME FILE_ID BYTES/1048576
--------------------------------- ---------- -------------
/u03/oradata/SAMT/sebevt01.dbf 13 1900
/u02/oradata/SAMT/sebevt02.dbf 14 1638.01172

The free space is only on datafile 13

SQL> select TABLESPACE_NAME,FILE_ID,BLOCK_ID,BYTES/1048576 "FREE MB",BLOCKS,RELATIVE_FNO
from dba_free_space where tablespace_name='SEBEVT01';

TABLESPACE_NAME FILE_ID BLOCK_ID FREE MB BLOCKS RELATIVE_FNO
---------------- ------- --------- ---------- ---------- ------------
SEBEVT01 13 197835 1127.21094 288566 13

----Following are the segments

SQL> select segment_name,initial_extent,next_extent,extents,bytes/1048576 from dba_segments where tablespace_name='SEBEVT01';

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT EXTENTS BYTES/1048576
------------ -------------- ----------- ------- -------------
TEBEVT01 536125440 5242880 1 511.289063
TEBEVT03 273100800 1048576 1 260.449219
TEBEVT04 12288 102400 1 .01171875
TEBEVT05 1085440 1048576 1 1.03515625
TEBEVT02 1717575680 209715200 1 1638.00781

Now I move the segment TEBEVT02 to a different tablespace and then check the free space in SEBEVT01

SQL> select TABLESPACE_NAME,FILE_ID,BLOCK_ID,BYTES/1048576 "FREE MB",BLOCKS,RELATIVE_FNO
2 from dba_free_space where tablespace_name='SEBEVT01';

TABLESPACE_NAME FILE_ID BLOCK_ID FREE MB BLOCKS RELATIVE_FNO
--------------- ------- -------- -------- -------- ------------
SEBEVT01 13 197835 1127.21094 288566 13
SEBEVT01 14 2 1638.00781 419330 14

which seems to be correct.

Now I move TEBEVT02 back to SEBEVT01 tablespace using an initial and next extent size of 10M.Ideally I would have liked to see that only 7MB from datafile 1 is free.(1120 used as each extent is 10M)

However when I actually do the move,this is what I get
SQL> select TABLESPACE_NAME,FILE_ID,BLOCK_ID,BYTES/1048576 "FREE MB",BLOCKS,RELATIVE_FNO
2 from dba_free_space where tablespace_name='SEBEVT01';

TABLESPACE_NAME FILE_ID BLOCK_ID FREE MB BLOCKS RELATIVE_FNO
-------------- -------- -------- ---------- ---------- ------------
SEBEVT01 13 384715 397.210938 101686 13
SEBEVT01 14 184322 918.007813 235010 14

Can someone explain why the 397MB on datafile 13 could not be used??

I do get to use all the space in datafile 13 is I move the segment TEBEVT02 with an initial extent of exactly 1127MB

Hi,
After you moved the segment you should use

Code:

---

 
  ALTER TABLESPACE ... COALESCE

---

to get larger peces of free blocks.

But there could still be a lot of free-spaces which are under 10M and so you cannnot use them.

Move ALL segments to another tablespace and then coalesce it.
So you will be successfull.

And in future pleas use LOCAL manged Tablespaces so this problem will never arise.


Orca
:cool:

I don't think coalesce will help.If you see carefully, I just have one chunk of free space over there.(in file 13)