在查询数据字典表，DBA_DATA_FILES时，遇见一则有趣的ORA-04043错误，错误重现及解决办法见下述：
1数据库版本，正常情况下，可以查看DBA_DATA_FILES数据字典表：
SQL> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 398462968 bytes Database Buffers 218103808 bytes Redo Buffers 7544832 bytes Database mounted. Database opened. SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> desc dba_data_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL>
2重现错误：
SQL> startup mount; ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2215944 bytes Variable Size 398462968 bytes Database Buffers 218103808 bytes Redo Buffers 7544832 bytes Database mounted. SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL>
3解决办法：
SQL> show user; USER is "SYS" SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> desc dba_data_files; ERROR: ORA-04043: object dba_data_files does not exist SQL> alter system flush shared_pool; System altered. SQL> desc dba_data_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL>
或者，先shutdown，然后startup，也可以避免ORA-04043错误。
描述：当数据库处于MOUNT状态时，如果试图去访问DBA_之类的数据字典表时，数据库会报出ORA-04043错误！即使，接下来，ALTER DATABASE OPEN，将数据库置于OPEN状态，再尝试去查看DBA_之类的数据字典表时，数据库依然会报出ORA-04043错误！解决的办法有两个：
- ① 重启数据库；
- ② 刷出共享池：ALTER SYSTEM FLUSH SHARED_POOL
补充，经测试，在数据库MOUNT状态尝试去查看SYS用户的普通表的话，也会报出同样的错误，看来不单是数据字典表了，具体见下述：
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 398462968 bytes
Database Buffers 218103808 bytes
Redo Buffers 7544832 bytes
Database mounted.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist
SQL> desc hr.t;
ERROR:
ORA-04043: object hr.t does not exist
SQL> alter database open;
Database altered.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist
SQL> desc hr.t;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> alter system flush shared_pool;
System altered.
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> desc test_04043
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
SQL> desc hr.t;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> select * from test_04043;
ID NAME
---------- ------------------------------
1 The F**king ORA-04043 Error.
2 WWW.OracleOnLinux.CN
SQL>
从上，可以看出，在数据库MOUNT状态下，试图去查看数据字典表和SYS用户下的普通表，会报ORA-04043错误，将数据库OPEN之后，再去查看这些对象时，依然会报错，而普通用户的对象却不会出现这个错误！
究其原因，这是个Oracle的bug,Bug 2365821,可见Metalink上的ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]，受影响的数据库版本：Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.2.0.1 – Release: 8.1.7 to 11.2，This problem can occur on any platform.