Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,422
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Where are my DBA_BLOCKERS and DBA_WAITERS ??

Posted by FatDBA on July 12, 2014

While doing performance checks and tuning for one of the database found that two of the very well known dynamic views are not working and throwing “Does Not Exist”. We are using – Oracle Database 11g Release 11.2.0.3.0 – 64bit Production.
I was trying to call the views using SYS user.

Purpose of using discussed two dynamic views are:
DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.

SQL> conn / as sysdba
Connected.

SQL> desc dba_blockers;
ERROR:
ORA-04043: object dba_blockers does not exist

SQL> desc dba_waiters
ERROR:
ORA-04043: object dba_waiters does not exist

Solution:
——————-
Call one script catblock.sql from ORACLE_HOME/rdbms/admin folder which creates required views, synonyms and provides required grants to the DBA privileged user and it will be back.

SQL> @?/rdbms/admin/catblock.sql

View created.

Synonym created.

Grant succeeded.

—–

—–

SQL>
SQL>
SQL>
SQL> desc dba_blockers
Name                                     Null?    Type
—————————————- ——– —————————
HOLDING_SESSION                                   NUMBER

SQL> desc dba * waiters;
Usage: DESCRIBE [schema.]object[@db_link]
SQL> desc dba_waiters
Name                                     Null?    Type
—————————————- ——– —————————
WAITING_SESSION                                   NUMBER
HOLDING_SESSION                                   NUMBER
LOCK_TYPE                                         VARCHAR2(26)
MODE_HELD                                         VARCHAR2(40)
MODE_REQUESTED                                    VARCHAR2(40)
LOCK_ID1                                          NUMBER
LOCK_ID2                                          NUMBER

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: