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

Thread: Rebuild Index

  1. #1
    Join Date
    Dec 2002
    Posts
    89

    Angry Rebuild Index

    Hello All,
    I would like to know ,what will be best approach to solve fragmented table/index.Our database size is 20g.DBA before me did unlimited on tablespace and now one of the tablespace has reached to
    12 g and i belive that there are lots of delete.Also we are planning
    to have new servers in mar.'03.So should i wait until than to reorganize database or is there anyway which take minimal time .
    1)Recreate table.
    2)Rebuild Index.
    3)Rebuild database.
    If its rebuilding index,what will be the right steps
    doing that online.Is it only one command below....

    1)alter index test1 rebuild compute statistics.
    i tried that on our development server.After doing for some of the index,it crash database.

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    If you say there are lot of deletes took place then I would suggest export/import complete schema, thought it will take bit more time but It will be useful. And if you know specific tables, then just export/import those tables..

    Sameer

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well you say you "believe" there are fragmentation but have you really looked into these tables/indexes and see if they are really fragmentated? If they are not use your time for more productive jobs

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    Re: Rebuild Index

    Originally posted by kpate
    DBA before me did unlimited on tablespace and now one of the tablespace has reached to
    12 g
    Unlimited... on whay. Tablespace default parameter, datafile size...

    12 GB.. is this for a particular file or the database on a whole?

    I'd repost the results of the following queries to give us a larger understanding in what you are delaing with. A simple Import/Export, probably won't effectiviely deal with future fragmentation.

    SELECT
    t.tablespace_name "Tablespace",
    t.status "Status",
    ROUND((MAX(d.bytes)/1024/1024) - (NVL(SUM(f.bytes),0)/1024/1024),2) "Used MB",
    ROUND(NVL(SUM(f.bytes),0)/1024/1024,2) "Free MB" ,
    t.initial_extent "Initial Extent",
    t.next_extent "Next Extent",
    t.min_extents "Min Extents",
    t.max_extents "Max Extents",
    t.pct_increase "Pct Increase" ,
    SUBSTR(d.file_name,1,80) "Datafile name"
    FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
    WHERE t.tablespace_name = d.tablespace_name
    AND f.tablespace_name(+) = d.tablespace_name
    AND f.file_id(+) = d.file_id
    GROUP BY t.tablespace_name , d.file_name , t.initial_extent , t.next_extent ,
    t.min_extents , t.max_extents , t.pct_increase , t.status
    ORDER BY 1,3 DESC;

    Select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) "MB", initial_extent, next_extent, extents from dba_segments
    where owner not in ('SYS', 'SYSTEM')
    and extents > 100;

    Cheeers,
    OCP 8i, 9i DBA
    Brisbane Australia

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