Fragmented tables and indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Fragmented tables and indexes

  1. #1
    Join Date
    Jun 2001
    Posts
    150

    Question

    Hi
    I have many fragmented tables and indexes in the database and also chained rows in some tables. What i have to do in order to tune those tables and indexes.
    There are few solution ihave in mind
    1. change the pct increase to 1 for the tablespaces which will do the coleasing but i don't know how often it will do it.
    2.recreate the tables and indexes with the bigger initial and next extents size.

    but there is one problem i can havein future if i have bigger size of initial and next extent then i might have problems when those indexes and tables will try to create the next extent. so i might receive the error that unable to allocate the next extents.

    please reply me what to do in order to do the tuning of those tables and indexes which are fragmented.

    Cheers
    UKDBA

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello

    I ahvent tried it but if you are on 8i why not think about locally managed tablespaces.....

    regards
    hrishy

  3. #3
    Join Date
    Jun 2001
    Posts
    150
    It is a good idea but i am talking about the databases which are on oracle734. and don't tell me to upgrade it to oracle 8i becasue it is in schedule but at the moment i have to tune the current database.
    UKDBA

  4. #4
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Wink well ...


    - move data to a temporary table.
    - drop all constraints for table
    - delete table
    - recreate table with desirable parameters (pct-free, pct-used)
    - move data from temporary table back into NEW table
    - enable constraints (lets hope they enable! otherwise use "exceptions" clause to find out why)
    - drop temp table

    voila!

    - Magnus

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    or our old friend export/import...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi UKDBA,

    If you are talking a lot of tables and indexes.

    for indexes :

    code :

    spool rebuild_inds.sql

    select 'alter index owner.'|| index_name || 'rebuild;'
    from dba_indexes
    where owner = 'owner'

    spool off
    @rebuild_ind

    Replace owner with index owner name.

    This will defragment your indexes, bear in mind that this will generate a lot of redo info, ensure that you have enough space to hold archived redo logs if in archivelog mode. You could also use the UNRECOVERABLE' keyword to stop the rebuild genrating any redo. It will also impact performance while rebuild is taking place. It is best to put a where condition in so that only the indexes you want to rebuild are rebuilt.

    If you are not aware of which indexes to rebuild issue the :
    alter index owner.index_name validate structure;

    then..

    select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
    where name = 'index_ name'

    If 20%+ of rows are deleted then the index should be rebuilt.

    To defragment tablespaces, you could try a full export/import of your database with compress=y. This will import into one contiguous extent.

    I you have OEM, you could use the re-organisation wizard.

    BTW, what part of UK are you from?

    Suresh




  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    I forget, was REBUILD new in 7.3 or 8.0?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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