Printable View
When I run an analyze on one of our schemas , code below:
BEGIN DBMS_STATS.gather_schema_stats (ownname => 'ISSLIVE', cascade =>true,estimate_percent => dbms_stats.auto_sample_size); END;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
This job is scheduled to run at 1am and does not coincide with another job. Any reason why I keep getting this? The same code runs without errors in other databases.
Any suggestions please.
Hi,
is it also when you run it manually ?
what does the last_analyze column in user_tables says ? did it starts with the statistics-creation ?
is it possible to analyze single tables with gather_table_stats ?
when it's possible, create a self genrating script for all tables in the schema and then you will see where the failure appears.
Sounds like one of a table in the schema is locked. Have you tried running it at different time?
Hi
I don't get this error when I run it manually.
The last_analyzed column for all the tables are not uniform i.e there are different dates suggesting the analyze did not complete.
I have tried changing the scheduled time but it still reports the same error. I kick the job off at 1am but there is a hot backup scheduled at 4am. I will bring the analyze job forward to 10pm tonight and see if this resolves it.
I will feedback tomorrow morning.
Thank you both for taking a look.
Regards
I got the same error again but this was successful when I ran it manually this morning.
Okay, you're sure that there's no other job running at the same time ?
Do you have disabled the automatic stats-job in this instance ?
Hi Chris
I eat my words - I just found out that there is scheduled job for a materialised view refresh at 10pm. I was just checking the nt scheduled jobs but this job is scheduled using the dbms_scheduler. I have brought the analyzed job forward now.
Thank you for your help.
Regards