Home » SQL & PL/SQL » SQL & PL/SQL » indexing materialized view - resource busy error (Oracle 9i)
indexing materialized view - resource busy error [message #326246] Tue, 10 June 2008 18:14
rtconsulting
Messages: 1
Registered: June 2008
Junior Member
I have a script that creates a materialized view and then following that, creates an index on the view. If the steps are ran separately with some time between them, everything works fine. However, if I run the steps together, I get a 'resource busy' error.

I presume that even though the materialized view has been created, it is still processing something and this is what is causing the resourced index to lock up.

What option/clause do I need to add to either my materialized view or my create index command to allow the scripts to move along? Can I add the index as part of my materialized view definition and bypass the secondary create index step?

create materialized view
     fm_dollarsbyMonthAndVendor
     USING INDEX STORAGE (INITIAL 25K NEXT 25K)
     REFRESH START WITH ROUND(SYSDATE) + 14/24 
     NEXT sysdate+4/24 
     WITH PRIMARY KEY
     as
     select cc.*,pscc.code as costCenterCode,
     fm_get_vendor_cost(yr,cc.code,cc.id,cc.costCenterID) as vendorCosts,
     fmGetMonthlyTotalCost(yr,cc.id,costCenterid) as TotalCCMonthlyCosts,
     fmGetStudentHours(cc.yr,cc.id,cc.costCenterId,'Completed') as totalCompletionHours,
     fmGetStudentHours(cc.yr,cc.id,cc.costCenterId,'In Progress') as totalEIPHours
     from fm_costCentersByMonthAndVendor cc
          left join psCostCenter psCC on cc.costCenterId=psCC.id;
--now setup some indexes for faster performance
create index ccmvYearMonth on fm_dollarsByMonthAndVendor(yr,id);


Thank you.
Previous Topic: Materialized View
Next Topic: partition type selection
Goto Forum:
  


Current Time: Wed Dec 07 16:37:20 CST 2016

Total time taken to generate the page: 0.28504 seconds