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.