Thursday, September 20, 2007

ORA-04043 in mount mode

There was a question at the OTN Database-General forum today about a problem when trying to describe the view dba_tablespaces.

The poster was getting an ORA-04043 error.


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

The first thing I thought about this was that the instance might have been in mount mode. I tried it on a database in mount stage and I got the error.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 404723928 bytes
Fixed Size 735448 bytes
Variable Size 234881024 bytes
Database Buffers 167772160 bytes
Redo Buffers 1335296 bytes
Database mounted.
SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

I was expecting to be able to see the view after opening the database, but...

SQL> alter database open;
Database altered.

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

I could not query it either.

SQL> select * from dba_tablespaces;
select * from dba_tablespaces
*
ERROR at line 1:
ORA-00942: table or view does not exist

A quick search in Metalink returned note 296235.1 which refers to the bug 2365821 and says that if you describe any dba_* view in mount mode you cannot describe the same view even after opening the database. The only solution is to restart the database.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 404723928 bytes
Fixed Size 735448 bytes
Variable Size 234881024 bytes
Database Buffers 167772160 bytes
Redo Buffers 1335296 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)

I was not aware of this behaviour till now. I tested this on 9.2 but the bug seems not fixed in any version.

7 comments:

  1. I could reproduce it in 11g, but rather than restarting the database I simply issued :
    alter system flush shared_pool;

    ReplyDelete
  2. Laurent, I have tested flushing the shared pool in 9.2 now and it corrects the problem. Nice solution. Thanks.

    ReplyDelete
  3. thank you sharing this interesting issue

    ReplyDelete
  4. Good piece of information from Yas and Laurent. Thanks for that.

    ReplyDelete
  5. I have sent a feedback in Metalink to put flushing the shared pool as a workaround in that note. Now the note also mentions flushing the shared pool as a workaround.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete