DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: fragmentation

  1. #1
    Join Date
    Sep 2000
    Posts
    31

    Question

    Hi guys,
    I just find out that some of my tablespaces are dangerously fragmented @98%. Please tell me how i can defrag or fix the problem. They are IDX and TEMP tablespace. oracle7.1.4 on unix.
    Thanks in advance,

    :(

  2. #2
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Hi You can rebuild the index's by dropping them. while rebuilding you may want to create drop the tablespace too and create back with new storage parameters.

    I think for Temp Tablespace you need not worry
    Thanks
    Kishore Kumar

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Try 'alter tablespace xxxxx coalesce ; as temporary solution.
    And rebuild your indexes using the following dynamic sql script:


    spool rbuildindx.sql
    select 'alter index '||owner||'.'||segment_name||' rebuild;'
    from dba_segments
    where owner not in ('SYS','SYSTEM','DBSNMP')
    -- where owner in ('TCOOLS','C_AFFAIRS')
    and segment_type='INDEX' and extents > 2;
    spool off

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I don't think you can use the "REBUILD" option in 7.1.x. You may be relegated to drop/create statements...
    Jeff Hunter

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You can use 'REBUILD' option on v7.3, I just looked into Manual. Not sure about lower versions...

    Yes, Jeff is Right! You can't use 'REBUILD' on lower versions of 7.3. Its valid from 7.3 onwards. I checked it up with 7.2 manual... there is no rebuild option.

    [Edited by sreddy on 12-28-2000 at 05:24 PM]

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