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.
