large fragment on LMT tablespace

[feroz]

Hi,

I have large fragment on our LMT tablespace (after DMT -> LMT). Because of this, it slows down the performance. What is the best way to tackle this issue? Can I just:

1) create new temporary tablespace
2) move out data to temp tablespace
3) drop old tablespace
4) recreate old tablespace
5) move back in from temp tablespace

What do you think? Thanks

[tamilselvan]

There are 4 types of fragmentation.

Which one are you talking?

[feroz]

Not sure myself. What are they anyway?

[slimdave]

1) create new temporary tablespace
2) move out data to temp tablespace
3) drop old tablespace
4) recreate old tablespace
5) move back in from temp tablespace

Yup.

[Bore]

I think we have discussed that already
1) Create new LMT
2) move all the segments to it
3) Drop the old one
4) If u use 10g rename the newly created tablespace to match the name of the old one
no need to move data forht and back

[feroz]

Hi,

Thanks. But what I dont understand is, why LMT cause fragmentation? I thought one move from DMT into LMT to avoid fragmentation.

Can I paste my sql code that I use to move here? I just want to make sure I dont miss anything. Thanks again.

[feroz]

Im using oracle 9.2.0.6 on solaris OS.

[Bore]

Yes, but when U move dmt to lmt using the Oracle package, actually extents are not touched. Just the free list is converted to bitmap and mooved to the file header. So if u had fragmentation in the DMT it stays in LMT as well
So to say, DMT moved to LMT is half lmt :-) If u create a new LMT and move the objects to it, then fragmentation shold be solved once and forever :-)

[feroz]

sql script that i use to move out:

accept TABLESPACE prompt "Enter tablespace name "

spool move_out.sql
select
'alter table '
||owner
||'.'
||table_name
|| chr(10)
||'move nologging tablespace LMT_MIG_TMP;'
from dba_tables
where tablespace_name=upper('&TABLESPACE')
/
select 'alter table '
||l.owner||'.'
||l.table_name
|| chr(10)
||' move lob ('
||l.column_name
||') '
|| chr(10)
||'store as '
||l.segment_name
||' (tablespace LMT_MIG_TMP);'
from dba_lobs l, dba_segments s
where s.owner = l.owner
and s.segment_name = l.segment_name
and s.tablespace_name=upper('&TABLESPACE')
/

select
'alter index '
||owner
||'.'
||index_name
|| chr(10)
||'rebuild nologging tablespace LMT_MIG_TMP;'
from dba_indexes
where tablespace_name=upper('&TABLESPACE')
/

spool off

sql script that i use to move back:

accept TABLESPACE prompt "Enter tablespace name "

spool move_back.sql
select
'alter table '
||owner
||'.'
||table_name
|| chr(10)
||'move nologging tablespace &TABLESPACE'
from dba_tables
where tablespace_name='LMT_MIG_TMP'
/
select 'alter table '
||l.owner||'.'
||l.table_name
|| chr(10)
||' move lob ('
||l.column_name
||') '
|| chr(10)
||'store as '
||l.segment_name
||' (tablespace &TABLESPACE);'
from dba_lobs l, dba_segments s
where s.owner = l.owner
and s.segment_name = l.segment_name
and tablespace_name='LMT_MIG_TMP'
/

select
'alter index '
||owner
||'.'
||index_name
|| chr(10)
||'rebuild nologging tablespace &TABLESPACE'
from dba_indexes
where tablespace_name='LMT_MIG_TMP'
/

select
'alter table '
||owner
||'.'
||table_name
|| chr(10)
||'logging ;'
from dba_tables
where tablespace_name='LMT_MIG_TMP'
/

select
'alter index '
||owner
||'.'
||index_name
|| chr(10)
||'logging ;'
from dba_indexes
where tablespace_name='LMT_MIG_TMP'
/
spool off

[feroz]

Bore,

Actually, I did create LMT tablespace first when moving from DMT. I used scripts that I paste here. And yet I still have fragmentation. I wonder why. Now I want to do the same thing in order to eliminate this fragmentation. DO you think by doing this will solve my issue?

Thanks.