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

Thread: ora 54 resource busy

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

    ora 54 resource busy

    Hi

    I Have a program in C that drops a bitmap index and then rebuilds it.
    Its a DW system wherein we drop the indexes load the data and recreate the bitmap indexes.

    While dropping the bitmap index in the Pro C program sometimes i get that error ora 54 resource busy.I am thinking of trying the operation again all over till i suceed what do you guys think about this ?

    code is something like

    for dropping the index

    Code:
    do   {
          execute immeduate 'drop index ...';
          sleep(50);
    }
    while (sqlca.sqlcode!=54);
    For creating the index

    My C program calls a Pl/sql block and after creating the index when i try to set the degree of parallelism to 1 i run into ora 54 resource busy problem.So should i code similarly in the pl/sql block something like

    Code:
    loop {
           execute immeditae 'alter index xyz parallel 1';
           sleep(50);
           exit when sqlca.code!=54;
    }
    end loop;
    is this the right approach ?

    This code would be run through a scheduler .and would called through out the day by a lot of programs.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about a different approach, of setting the index unusable, "alter session set skip_unusable_indexes=true", load, then rebuild the indexes? I find it more robust myself, as changes to index properties (tablespace etc) do not have to be stored outside of the existing index definition.

    Are you using direct-path loads, by the way? If so, have you evaluated loading with enabled indexes in place? If not, I would strongly advise that you use direct and not conventional-path inserts
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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

    I am using sql based inserts and not a direct part insert.But its a bulk insert uisng Pro *C array features.

    I did not understand about the skip unusable indexes=true.How do i do that ?

    And also the ora 54 occurs when i am trying to set the parallel degree for the index not when i am rebuilding the index.

    Do you mean something like

    alter session set skip_unusable_indexes=true;
    alter index myindex unusable;
    alter index myindex rebuild;

    regards
    Hrishy

    regards
    Hrishy
    Last edited by hrishy; 09-24-2005 at 08:24 PM.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This sort of thing.
    Code:
    alter index myindex unusable;
    alter session set skip_unusable_indexes=true;
    >> load data here
    alter index myindex rebuild;
    How many rows do you insert in one load cycle?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Slim

    You rock..thanks will modify my code i insert about 5000 rows..this is a parameter the user supplies..depends on that day.

    regards
    Hrishy

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