Database Administration (MOSC)

MOSC Banner

Can't drop SCN indexes, resource busy. help !

edited Oct 15, 2010 1:24AM in Database Administration (MOSC) 5 commentsAnswered ✓

Hi
Re document ID 375401.1 regarding high SMON thrashing, I tried to drop the indexes and rebuild them as advised using the procedure:


connect / as sysdba
shutdown immediate;
startup restrict;
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade; 

I got this for the "drop index" statements:

ORA-00054: resource busy and acquire with NOWAIT specified

Understandable if smon is thrashing I suppose. Tried waiting a while, I know smon wakes on schedule and maybe it hasn't finished the previous scan before the next one starts. But no luck. No matter how long I wait, it's continuously hitting that. This is a dev database that has been lurking in a corner and I hadn't noticed this was happening. Appears to have been that way for a while now.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center