Home » SQL & PL/SQL » SQL & PL/SQL » Bitmap index rebuild - getting ORA-00054
Bitmap index rebuild - getting ORA-00054 [message #570764] Thu, 15 November 2012 10:03 Go to next message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Hello,

I have a procedure that is run weekly to load the data, for which it calls another procedure. This weekly procedure, disables the index, load the data, and rebuilds the index. During rebuilding of index, its giving ORA-00054: resource busy and acquire with NOWAIT error. I dont have privileges to look for session id, and kill the session. How to avoid this error.
create or replace
PROCEDURE "WCL_WEEKLY_UPLOAD" (
                                p_event_id IN NUMBER
                                   )
AUTHID CURRENT_USER IS 
   l_utlfile_weekly_log          utl_file.file_type;
   l_utlfile_weekly_log_index    utl_file.file_type;
   l_dir                         VARCHAR2(50):= 'WC_1';
   l_user                        VARCHAR2(10):= 'Tech_1';
   l_type                   VARCHAR2(60);
   l_type1                  VARCHAR2(60);


  CURSOR cur_loads(c_type NUMBER)
      IS SELECT * FROM WC_DATA_LOADS_temp
	  WHERE  product_group_id = c_type
	    AND event_id = 1;
   
BEGIN
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Event Claims');
   wclaim.Wcl_Css; -- Cascading Style Sheet (CSS) Info 
   htp.headClose;

   htp.bodyOpen(NULL, 'BGCOLOR="#DFDED0" MARGINHEIGHT="0" MARGINWIDTH="0" TOPMARGIN="0" LEFTMARGIN="0"');
   wclaim.Wcl_Menu(p_event_id);

   l_utlfile_weekly_log_index := utl_file.fopen('WC_1','weekly-upload_index.log','w');
   l_utlfile_weekly_log := utl_file.fopen('WC_1','weekly-upload.log','w');
   utl_file.put_line(l_utlfile_weekly_log, 'Start time: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log);
   htp.bold('Start time: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   htp.br;


  utl_file.put_line(l_utlfile_weekly_log, 'START :- Indexes disabled: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
-- Disable Indexes 
   EXECUTE IMMEDIATE 'ALTER INDEX EVENT.WC_COMMODITY_MY_BM UNUSABLE';
   EXECUTE IMMEDIATE 'ALTER INDEX EVENT.WC_EVENUMBER UNUSABLE';

   EXECUTE IMMEDIATE 'alter session set skip_unusable_indexes=true';

   utl_file.put_line(l_utlfile_weekly_log, 'END :- Indexes disabled: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log);
 
   utl_file.put_line(l_utlfile_weekly_log, 'Interior Data Load Started: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log);
   htp.bold('Data Load Started: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   htp.br;

   FOR loads_rec IN cur_loads(3) LOOP
      IF loads_rec.is_extended IS NULL THEN	
       utl_file.put_line(l_utlfile_weekly_log, 'Data Load - '||loads_rec.file_name||' - Wc_Upload Started: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
         Wc_Upload(loads_rec.event_id,loads_rec.product_group_id,l_dir,loads_rec.file_name,SUBSTR(loads_rec.file_name,1,INSTR(loads_rec.file_name,'.')-1),l_user);
      END IF;
   END LOOP;
  COMMIT ;

-- Enable Indexes
   utl_file.put_line(l_utlfile_weekly_log_index, 'Index log Start time: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log_index);

   utl_file.put_line(l_utlfile_weekly_log_index, 'Enabling COMMODITY_MY Index: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   htp.br;
   htp.bold('Enabling Index WC_COMMODITY_MY_BM');
   htp.br;
--  EXECUTE IMMEDIATE 'alter session set ddl_lock_timeout=100 ';
--  EXECUTE IMMEDIATE 'commit';
--  EXECUTE IMMEDIATE 'alter session set ddl_lock_timeout=300' ;
--  DBMS_LOCK.sleep(300); 

    begin 
   EXECUTE IMMEDIATE 'ALTER INDEX WC_COMMODITY_MY_BM REBUILD NOLOGGING';
    exception
      WHEN OTHERS THEN
         utl_file.put_line(l_utlfile_weekly_log_index, 'Error in WC_COMMODITY_MY_BM Index : '||SQLERRM);
   end;
   htp.bold('WC_COMMODITY_MY_BM Index enabled');
   htp.br;
   htp.br;
   utl_file.put_line(l_utlfile_weekly_log_index, 'WC_COMMODITY_MY_BM Index Enabled: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log_index);
   htp.br;

   utl_file.put_line(l_utlfile_weekly_log_index, 'Enabling WC_EVENUMBER Index: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   htp.br;
   htp.bold('Enabling Index WC_EVENUMBER');
   htp.br;
    begin 
   EXECUTE IMMEDIATE 'ALTER INDEX WC_EVENUMBER REBUILD NOLOGGING';
    exception
      WHEN OTHERS THEN
         utl_file.put_line(l_utlfile_weekly_log_index, 'Error in WC_EVENUMBER Index : '||SQLERRM);
   end;
   htp.bold('WC_EVENUMBER Index enabled');
   htp.br;
   htp.br;
   utl_file.put_line(l_utlfile_weekly_log_index, 'WC_EVENUMBER Index Enabled: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log_index);
   htp.br;

   utl_file.put_line(l_utlfile_weekly_log_index, ' end :- Indexes enabled: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   utl_file.new_line(l_utlfile_weekly_log_index);
   htp.bold(' Indexes enabled: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
   htp.br;
   htp.br;

   htp.br;
   htp.bold('*****     WEEKLY UPLOAD PROCESS COMPLETED     *****');
   htp.br;
 utl_file.put_line(l_utlfile_weekly_log_index, 'Weekly Upload Process Completed: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
  utl_file.new_line(l_utlfile_weekly_log_index);
  utl_file.fclose(l_utlfile_weekly_log_index);

   htp.br;
   COMMIT;
   htp.bodyClose;
   htp.htmlClose;
EXCEPTION
   WHEN OTHERS THEN
        htp.bold('Error: '||SQLERRM, cattributes=>'CLASS=error');
        htp.bodyClose;
        htp.htmlClose;
END;



As you can see, after the procedure wc_upload, COMMIT is issued, and then the rebuilding of the index is starting. So dont know what is causing the table to lock.

Thanks.
Re: Bitmap index rebuild - getting ORA-00054 [message #570765 is a reply to message #570764] Thu, 15 November 2012 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to avoid this error.


Remove access to the table to anyone but you.

Regards
Michel
Re: Bitmap index rebuild - getting ORA-00054 [message #570766 is a reply to message #570765] Thu, 15 November 2012 10:13 Go to previous messageGo to next message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
Thanks Michel, but I dont have any privilege to grant/revoke access
Re: Bitmap index rebuild - getting ORA-00054 [message #570773 is a reply to message #570766] Thu, 15 November 2012 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask your DBA to put the database in restricted mode (or shutdown the listener, depending on your environment, dedicated or shared...) and create the index.

Regards
Michel
Re: Bitmap index rebuild - getting ORA-00054 [message #570774 is a reply to message #570773] Thu, 15 November 2012 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
not that it matters, but I do not see where any data actually gets "loaded".
a SELECT occurs, but I do not see any DML.
what populates data in WC_DATA_LOADS_temp table?
Re: Bitmap index rebuild - getting ORA-00054 [message #570776 is a reply to message #570774] Thu, 15 November 2012 11:31 Go to previous messageGo to next message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
Procedure Wc_Upload inserts/update data in the event table, and then in the WC_DATA_LOADS_temp table. Event table is the one that has bitmap index, and while rebuilding the index it gives ORA-00054 error.
Re: Bitmap index rebuild - getting ORA-00054 [message #570778 is a reply to message #570776] Thu, 15 November 2012 11:48 Go to previous message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
one possible brute force "solution" would be to
LOCK TABLE
at the top of the procedure.
Previous Topic: Generate movements
Next Topic: Help in this pivot query
Goto Forum:
  


Current Time: Tue Oct 21 15:37:02 CDT 2014

Total time taken to generate the page: 0.07052 seconds