奇怪的ORA-04043错误及解决方法

           在查询数据字典表,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.

发表评论

邮箱地址不会被公开。 必填项已用*标注