-
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.
-
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
-
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.
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|