|
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|