Printable View
If I have a HWM on the table, I move the tabes to different tablespaces using :
ALTER TABLE TABLE_NAME MOVE NEW_TABLESPACE;
what if I move the table on the same tablespace like:
ALTER TABLE TABLE_NAME MOVE;
the question is "is it bad thing to move the table on the same tablespace??? which may cause fragmentation on the tablespace b/c of the old segment???"
I am on 9i and using local extent
have a great weekend to all.
========
what if I move the table on the same tablespace like:
ALTER TABLE TABLE_NAME MOVE;
the question is "is it bad thing to move the table on the same tablespace??? which may cause fragmentation on the tablespace b/c of the old segment???"
I am on 9i and using local extent
========
It is not bad to move a table within the same tablespace. In fact after the move the rows would be packed within the blocks. Of course, you have to rebuild indexes after the move.
Tamil
my colleagues think it's cause the tablespace fragmented and I told him I don't think so but I can't prove it.
in case you are using LMT I would not worry about the tablespace fragmentation
This is an extract from a reply on allmost the same question
"If you are suffering from fragmentation -- your problem is not the index
rebuilds. It would be because you are using DICTIONARY managed tablespaces.
Switch over to locally managed tablespaces and you'll never have fragmentation
again. So, my suggestion -- one last index rebuild:
o create a locally managed tablespace
o alter indexrebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );
and then forget about them. You will rebuild an index in response to IDENTIFIED
and DEGRADED performance -- period. You will not rebulid indexes based on a
schedule. Period."
Hi hannah00,
There are 5 types of fragmentation:
1. ROW LEVEL Fragmentation
2. Block Level Fragmentation
3. Segment Level Fragmentation
4. Tablespace Fragmentation
5. Disk level fragmentation
Which one are you interested? You are already using LMT, so the option 4 is ruled out.
Tamil
tamil,
once you do the alter move on the same tablespace, your HWM will be removed, so what happen to the empty segment/extent??? will it be reused by some other objects???
I am not 100% clear on this. and thanks so much for your input.
I am on LMT and I understand that LMT will eliminate fragmentation, but I would like to understand how.
The freed extents will be reused as and when new extents are needed for any object.
See the example.
TamilPHP Code:SQL> L
1 select segment_name, tablespace_name,
file_id, extent_id, block_id, blocks , bytes
2 from dba_extents where segment_name = 'MY_TABLE'
3* order by file_id, block_id
SQL> /
SEGMENT_NAME TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
------------ ---------------- ---------- ---------- ---------- ---------- ----------
MY_TABLE SBL_MEDIUM_DATA 9 1 64009 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 3 65289 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 5 66569 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 7 67849 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 9 69129 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 11 70409 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 0 60169 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 2 62729 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 4 64009 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 6 65289 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 8 66569 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 10 67849 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 12 69129 1280 10485760
13 rows selected.
SQL> save x1.sql repl
Wrote file x1.sql
SQL> delete my_table where mod(object_id,3)= 0 ;
15588 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table my_table move;
Table altered.
SQL> get x1
1 select segment_name, tablespace_name,
file_id, extent_id, block_id, blocks , bytes
2 from dba_extents where segment_name = 'MY_TABLE'
3* order by file_id, block_id
SQL> /
SEGMENT_NAME TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
------------ ---------------- ---------- ---------- ---------- ---------- ----------
MY_TABLE SBL_MEDIUM_DATA 9 1 71689 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 3 72969 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 5 74249 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 7 75529 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 0 70409 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 2 71689 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 4 72969 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 6 74249 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 8 75529 1280 10485760
9 rows selected.
When you move a table with in the same tablespace, oracle would allocate new extents.
Now, I move the table to a different tablespace, users.
SQL> alter table my_table move tablespace users ;
Table altered.
Again, I move it back to original tablespace, sbl_medium_data.
SQL> alter table my_table move tablespace sbl_medium_data ;
Table altered.
SQL> get x1
1 select segment_name, tablespace_name,
file_id, extent_id, block_id, blocks , bytes
2 from dba_extents where segment_name = 'MY_TABLE'
3* order by file_id, block_id
SQL> /
SEGMENT_NAME TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
------------ ---------------- ---------- ---------- ---------- ---------- ----------
MY_TABLE SBL_MEDIUM_DATA 9 1 64009 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 3 65289 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 5 66569 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 9 7 67849 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 0 60169 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 2 62729 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 4 64009 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 6 65289 1280 10485760
MY_TABLE SBL_MEDIUM_DATA 10 8 66569 1280 10485760
9 rows selected.
The freed extents are reused.
SQL> spool off
Only if u have uniform extent.. well when u have uniform extents how will the Q of fragmentation arrise as the extents freed due DDL will be reused.. and ther wont be any small gaps left in while formating any extent in the datafile..Quote:
Originally posted by hannah00
I am on LMT and I understand that LMT will eliminate fragmentation, but I would like to understand how.
Abhay.