Move Tablespace Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Move Tablespace Query

  1. #1
    Join Date
    May 2002
    Posts
    12

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

  3. #3
    Join Date
    May 2002
    Posts
    12
    I don't think coalesce will help.If you see carefully, I just have one chunk of free space over there.(in file 13)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width