[20121203]ora-4043错误.txt

SQL> select * from v$version where rownum<=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


--关闭数据库，在打开看看:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1191184496 bytes
Database Buffers          402653184 bytes
Redo Buffers                7360512 bytes
Database mounted.
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database open ;
Database altered.

SQL> select count(*) from dba_tables;
  COUNT(*)
----------
      2939


--在mount状态下无法看dba_tables视图。这个是正常的。打开到open状态可以看视图。关闭数据库再测试。

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1191184496 bytes
Database Buffers          402653184 bytes
Redo Buffers                7360512 bytes
Database mounted.
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist


SQL> alter database open ;
Database altered.


--打开新的会话2，以非sys用户登录（有dba权限)
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

--修改一个字符大小写看看。
SQL> select Count(*) from dba_tables;
select Count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from dba_tables where rownum<=1;
select * from dba_tables where rownum<=1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist

--回到sys用户执行如下：

SQL> desc dba_tables;

Name              Null?    Type

----------------- -------- --------------

OWNER             NOT NULL VARCHAR2(30)

TABLE_NAME        NOT NULL VARCHAR2(30)

...

DROPPED                    VARCHAR2(3)

READ_ONLY                  VARCHAR2(3)

SEGMENT_CREATED            VARCHAR2(3)

RESULT_CACHE               VARCHAR2(7)

--desc dba_tables执行正常。切换到回话2，执行:
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> Select count(*) from dba_tables;
Select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist

SQL> desc sys.dba_tables;
-- 执行正常。

SQL> select count(*) from dba_tables;
select count(*) from dba_tables
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcdlSynonymTranslation], [], [], [], [], [], [], [], [], [], [], []

--跑出来一个ora--00600错误。刷新共享池看看。

SQL> alter system flush shared_pool;
System altered.

SQL> select count(*) from dba_tables;

  COUNT(*)

----------

      2939

--OK一切正常。



--我仅仅想提醒的是在mount下执行desc xxx出现ora-4043时，要注意。假设在mount看desc dual表。看看会出现什么情况?

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2213776 bytes
Variable Size            1191184496 bytes
Database Buffers          402653184 bytes
Redo Buffers                7360512 bytes
Database mounted.
SQL> desc dual
ERROR:
ORA-04043: object dual does not exist


SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00942: table or view does not exist
Process ID: 26853
Session ID: 191 Serial number: 3

--oracle实例直接关闭。