Scheduled procedure causing too many open files error
Posted by:
F Kemp
Date: January 23, 2013 01:11PM
Hi, thanks in advance for any advice.
I created a procedure for monitoring and it looks like it is locking up the database, causing errors about "too many open files". Our limit is set really high and the DBA checked the open files last time we got that error and we were not close to the limit. He thinks it might be a memory issue and is checking into our errors some more.
In the mean time, is there anything I need to do/add in my stored procedure to make sure I am not leaving files open after reading them?
The procedure is scheduled to run once an hour and it runs for just a few seconds. It queries between 800-1000 tables. Because we have varying numbers/names of tables to read I am building the statements dynamically and then using prepare/execute inside a cursor to query the tables and update a separate monitoring table. It seems to work correctly, but we can't have it locking up the DB. Here is a version of that part of the sql:
insert into querytable (Query_Text)
select concat('update monitortable m set m.LatestTime= (select max(Updated) from schema.', t.TABLE_NAME, ''');') words
from information_schema.TABLES t where t.TABLE_NAME like "%_xyz";
-- cursor to execute saved queries --------------------------------------------
OPEN sp_monitor_cur;
loop1:LOOP
FETCH sp_monitor_cur INTO query;
IF done THEN
LEAVE loop1;
END IF;
set @sql = query;
prepare stmt from @sql;
execute stmt;
END LOOP;
CLOSE sp_monitor_cur;
--------------------------------------------------------------------
Thanks again!
Subject
Views
Written By
Posted
Scheduled procedure causing too many open files error
2969
January 23, 2013 01:11PM
1034
January 24, 2013 12:19PM
891
January 24, 2013 12:46PM
1089
January 24, 2013 01:36PM
1132
January 24, 2013 02:11PM
1013
January 26, 2013 12:46AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.