not able rebuild index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: not able rebuild index

  1. #1
    Join Date
    Jun 2003
    Posts
    24

    Unhappy not able rebuild index

    I was rebuilding an index and was killed and now try to re-execute the rebuilding of index but was face with error

    ora-08104-this index object 3969 is being online built or rebuilt

    I know I have to wiat for smon to do recovery but is there any recovery so that I can re-execute the rebuilding of index......this table is very huge where I think it will take days

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how huge is huge?

  3. #3
    Join Date
    Jun 2003
    Posts
    24
    I am rebuild an index where the base table is 71G

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how many rows?

  5. #5
    Join Date
    Jun 2003
    Posts
    24
    not able to get the rows because face with snapshot too old when trying to run a query to count the table but based on the application user they say it is more than millions rows

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    my 65 million rows table with 17 columns is 4gb... how can your 1 million rows table be 71gb...??????

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    6
    Metalink issues three possible ways to overcome this :

    1. wait for SMON to clean up the temporary segments in the tablespace

    2. try FORCE dropping the index, eg. 'drop index blah.blah FORCE;' ;
    however this only applies to DOMAIN indexes

    3. in Doc.ID 363662.999 (titled "How to handle ora-8104 errors")
    Oracle gives the following possibility :

    You can force this using the command
    "alter session set events
    'immediate trace name DROP_SEGMENTS level TS#+1';"

    This event finds all the temporary segments in a tablespace which are not currently locked and drops them.
    level = tablespace number+1. If the value is 2147483647 then temp segments in ALL tablespaces are dropped, otherwise, only segments in a tablespace whose number is equal to the LEVEL specification are dropped.
    So you have to find the number of the tablespace in which the index resides issue the above command where substitute ts-number+1 for the level.


    However, I would advise you to :
    -first : visit metalink and search for the appropriate error reports
    -second: try these possiblities out in TEST before applying them in your PRODUCTION environment
    Last edited by icdb023; 01-14-2004 at 06:17 AM.

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