During the test today, I want to query the tablespace information corresponding to the data file. In the desc dba_Data_files view, I found that this object does not exist. At that time, I thought it was normal that my DB could not be queried under mount. Later I queried dba_data_files under DB OPEN and reported that this object did not exist. I suddenly felt confused. All other objects can be queried normally, but the DBA _ * view cannot be used normally. Query MOS (Document ID 296235.1) and find the following description:
Applies:
Oracle Database-Enterprise Edition-Version 8.1.7.4 to 11.2.0.1 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
SYMPTOMS
If you describe any DBA _ * Views (Data dictionary views) in a mount stage, than you will not be able
Describe those views even after opening the database.
You can test as follows:
+ Shutdown the database.
SQL> Shutdown immediate
+ Mount the database.
SQL> Startup Mount
+ Describe any Data Dictionary view.
SQL> DESC DBA_DATA_FILES
ERROR:
ORA-04043: object DBA_DATA_FILES does not exist.
+ Open the Database.
SQL> ALTER DATABASE OPEN;
Database altered
+ Describe the same Data Dictionary view.
SQL> DESC DBA_DATA_FILES
ERROR:
ORA-04043: object DBA_DATA_FILES does not exist
* ** Error will appear till we bounce the Database/flush the shared pool.
CAUSE
This is a related to Bug 2365821
Abstract: ORA-4043 on dba _ * tables if they are described in a mount stage.
SOLUTION
Available workarounds are:
1) Don't describe the dba _ * views at mount stage.
OR
2) If you issue DESC of any DBA _ * views at mount stage, then shutdown and restart the DB instance.
OR
3) Flush the shared pool.
SQL> Alter system flush shared_pool;
And then reissue the failing command.
It is precisely because I am querying the dba_data_files view in the database mount status that causes oracle database product Bug 2365821. Even if the database is OPEN, dba _ * view cannot be used normally.
Oracle view basics and Examples
Oracle performance problems caused by improper definition of materialized views
Oracle dictionary and Dynamic View
Oracle materialized view test
Connection-based materialized views of Oracle [refresh regularly]