ORA-00054 - Resource busy and requires nowait

[joy_ada]

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.

[ChrisD]

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.

[skhanal]

Sounds like one of a table in the schema is locked. Have you tried running it at different time?

[joy_ada]

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

[joy_ada]

I got the same error again but this was successful when I ran it manually this morning.

[ChrisD]

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 ?

[joy_ada]

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