large fragment on LMT tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: large fragment on LMT tablespace

  1. #1

    large fragment on LMT tablespace

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There are 4 types of fragmentation.

    Which one are you talking?

  3. #3
    Not sure myself. What are they anyway?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by feroz
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  6. #6
    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.

  7. #7
    Im using oracle 9.2.0.6 on solaris OS.

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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 :-)

  9. #9
    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

  10. #10
    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.

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