Top 60 Oracle Blogs

Recent comments


Recycle bin

Recent Charles Hooper’s post on the topic of Recycle bin (which is, BTW, documented behavior) reminded me of an issue with that functionality I’ve seen recently. The problem was a single-row INSERT INTO table VALUES () statement was hanging for more than an hour burning CPU.

How to log on to an instance when even SYSDBA can't do so?

When you have a situation where let say the archiver process is stuck, regular users and even users with DBA role may not be able to log on. This usually because new sessions logging on generate audit records or (sometimes) update SEQ$ table to get new values from a sequence for populating AUDSID. If the archiver is stuck, then any DML will get blocked as well as they can't generate any new redo until the archiver stuck issue is fixed.

In these cases the SYSDBA (and SYSOPER) privlege holders will still be able to log on and create their session. This is because SYSDBA connections do not generated any audit records to the database tables (that's why there's the audit_file_dest directory where SYSDBA audit files will be written) and SYSDBA connections also don't get their AUDSID from a sequence but use a hardcoded value (0xFFFFFFFF) instead.

But there are cases when even SYSDBA privilege holders can't log on! One situation what I've seen was due a bug, where a process never released a shared pool latch it had taken (in fact it was stuck spinning in free memory searching code). That instance happened to have only one shared pool subpool in use, protected by a single latch. 

That meant that no-one else could allocate/free memory from shared pool, causing most sessions to eventually hang.

Read on how to resolve such situations here: