ORA-04043: object dba_data_Files does not exist
问题现象
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> desc dba_data_Files;
ERROR:
ORA-04043: object dba_data_Files does not exist
查询系统视图dba_data_files，报错ORA-0094
desc 查询视图的结构，报错ORA-04043.
尝试解决
查看视图状态
dba_data_files为系统视图，首先应该确定这个视图是否真的存在以及他的状态是否可用，如果视图的底层对象有变化，视图状态就会为invalid。
SQL> select owner,object_name,OBJECT_TYPE,STATUS from dba_objects where object_name=‘DBA_DATA_FILES’;
SQL> col owner for a10
SQL> col object_name for a20
OWNER OBJECT_NAME OBJECT_TYPE STATUS
SYS DBA_DATA_FILES VIEW VALID
PUBLIC DBA_DATA_FILES SYNONYM VALID
视图状态为invalid，状态正常。而且按下面的过程，如果底层对象发生变化导致状态为失效，在调用的时候也会自动编译，不会报错。除非在自动编译的过程中出现错误。
扩展：
证明视图何时生效，以及编译失效对象
查看视图创建关系
SQL> select * from dba_dependencies where name='V1';
SQL> col owner for a10
SQL> col name for a10
SQL> col REFERENCED_NAME for a10
SQL> col REFERENCED_LINK_NAME for a10
OWNER NAME TYPE REFERENCED_OWNER REFERENCED REFERENCED_TYPE REFERENCED DEPE
---------- ---------- ------------------ ------------------------------ ---------- ------------------ ---------- ----
ZHUO V1 VIEW ZHUO T1 TABLE HARD
可以看出，视图V1基于table T1创建。
修改表结构
SQL> alter table zhuo.t1 modify (name varchar2(9));
Table altered.
查看表和视图ddl时间和状态:
SQL> select owner,object_name,OBJECT_TYPE,LAST_DDL_TIME,STATUS from dba_objects where object_name='T1';
OWNER OBJECT_NAM OBJECT_TYPE LAST_DDL_TIME STATUS
------------------------------ ---------- ------------------- ------------------- -------
ZHUO T1 TABLE 2020-07-31 09:59:04 VALID
SQL> select owner,object_name,OBJECT_TYPE,LAST_DDL_TIME,STATUS from dba_objects where object_name='V1';
OWNER OBJECT_NAM OBJECT_TYPE LAST_DDL_TIME STATUS
------------------------------ ---------- ------------------- ------------------- -------
ZHUO V1 VIEW 2020-07-31 09:58:19 INVALID
T1表发生DDL，视图V1的状态已经是invalid了。所以底层对象有变化，可能导致视图对象失效。
查看视图内容，发现仍然可以查看：
SQL> select * from zhuo.v1;
ID NAME
---------- ---------
1 zzz
1 aaa
SQL> select owner,object_name,OBJECT_TYPE,LAST_DDL_TIME,STATUS from dba_objects where object_name='V1';
OWNER OBJECT_NAM OBJECT_TYPE LAST_DDL_TIME STATUS
---------- ---------- ------------------- ------------------- -------
ZHUO V1 VIEW 2020-07-31 10:01:21 VALID
发现查询了一次视图对象，视图的状态自动变为有效了。自动重新编译了。
其实不管视图，像存储过程，函数、包等，如果代码本身没有什么错误，只是引用的对象发生了变化。也会失效。但并不影响调用，因为ORACLE在调用时会自动重新编译的，如果其它对象变化后导致编译有错误。这时调用时重新编译后也是错误并处于失效状态，所以调用会出错
参考：https://www.cnblogs.com/kerrycode/p/3723715.html
视图底层对象是否存在
—其实不用查看，如果不存在，编译的时候也会报错的，这里只是提供一种方法
SQL> alter view dba_data_files compile;
alter view dba_data_files compile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_OBJ2) violated
SQL> select TEXT from dba_views where view_name='DBA_DATA_FILES';
SQL> set long 9999999
TEXT
--------------------------------------------------------------------------------
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#
手动执行view创建脚本：
SQL> select v.name, f.file#, ts.name,
2 ts.blocksize * f.blocks, f.blocks,
3 decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
4 f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
5 ts.blocksize * f.maxextend, f.maxextend, f.inc,
6 ts.blocksize * (f.blocks - 1), f.blocks - 1,
7 decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
8 decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
9 from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
10 where v.file# = f.file#
11 and f.spare1 is NULL
12 and f.ts# = ts.ts#
13 and fe.fenum = f.file#
14 union all
15 select
16 v.name,f.file#, ts.name,
17 decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
18 decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
19 decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
20 f.relfile#,
21 decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
22 decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
23 decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
24 decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
25 decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
26 decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
27 decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
28 decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
29 from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
30 where v.file# = f.file#
31 and f.spare1 is NOT NULL
32 and v.file# = hc.ktfbhcafno
33 and hc.ktfbhctsn = ts.ts#
34 and fe.fenum = f.file#;
NAME FILE# NAME TS.BLOCKSIZE*F.BLOCKS BLOCKS DECODE(F. RELFILE# DEC TS.BLOCKSIZE*F.MAXEXTEND MAXEXTEND INC TS.BLOCKSIZE*(F.BLOCKS-1) F.BLOCKS-1 DECODE(
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ --------------------- ---------- --------- ---------- --- ------------------------ ---------- ---------- ------------------------- ---------- -------
/export/home/oracle/opt/oradata/ORCL/datafile/o1_mf_system_gs8c34nt_.dbf 1 SYSTEM 419430400 51200 AVAILABLE 1 YES 3.4360E+10 4194302 1280 419364864 51192 SYSTEM
/export/home/oracle/opt/oradata/ORCL/datafile/o1_mf_undotbs1_gs8c36vm_.dbf 2 UNDOTBS1 351272960 42880 AVAILABLE 2 YES 3.4360E+10 4194302 640 351207424 42872 ONLINE
/export/home/oracle/opt/oradata/ORCL/datafile/o1_mf_sysaux_gs8c377g_.dbf 3 SYSAUX 199229440 24320 AVAILABLE 3 YES 3.4360E+10 4194302 1280 199163904 24312 ONLINE
/export/home/oracle/opt/oradata/ORCL/datafile/o1_mf_users_gs8c38oq_.dbf 4 USERS 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 5177344 632 ONLINE
没有报错，说明底层对象都是正常的。包括这几个系统字典表和内部rdbms表：sys.v d b f i l e v , s y s . f i l e dbfile v, sys.file dbfilev,sys.file f, sys.x k t f b h c h c , s y s . t s ktfbhc hc, sys.ts ktfbhchc,sys.ts ts, x$kccfe
10046 event
生成10046trc文件
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/export/home/oracle/opt/admin/orcl/udump/orcl_ora_1622.trc
bash-3.2$ cat /export/home/oracle/opt/admin/orcl/udump/orcl_ora_1622.trc
/export/home/oracle/opt/admin/orcl/udump/orcl_ora_1622.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /export/home/oracle/opt/product/10.2.0/
System name: SunOS
Node name: oracle10g
Release: 5.10
Version: Generic_147148-26
Machine: i86pc
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 1622, image: oracle@oracle10g (TNS V1-V3)
*** ACTION NAME:() 2020-07-30 07:11:38.113
*** MODULE NAME:(sqlplus@oracle10g (TNS V1-V3)) 2020-07-30 07:11:38.113
*** SERVICE NAME:(SYS$USERS) 2020-07-30 07:11:38.113
*** SESSION ID:(544.5) 2020-07-30 07:11:38.113
*** 2020-07-30 07:11:38.113
Processing Oradebug command 'setmypid'
*** 2020-07-30 07:11:49.397
Processing Oradebug command 'unlimit'
*** 2020-07-30 07:11:50.011
Processing Oradebug command 'event 10046 trace name context forever,level 12'
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=9865 tim=79576818097
WAIT #0: nam='SQL*Net message from client' ela= 7077467 driver id=1650815232 #bytes=1 p3=0 obj#=9865 tim=79583895605
=====================
PARSE ERROR #3:len=29 dep=0 uid=0 oct=3 lid=0 tim=79583895854 err=942
select * from dba_data_files
WAIT #3: nam='SQL*Net break/reset to client' ela= 17 driver id=1650815232 break?=1 p3=0 obj#=9865 tim=79583895976
WAIT #3: nam='SQL*Net break/reset to client' ela= 72 driver id=1650815232 break?=0 p3=0 obj#=9865 tim=79583896067
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=9865 tim=79583896098
*** 2020-07-30 07:12:13.823
WAIT #3: nam='SQL*Net message from client' ela= 16175780 driver id=1650815232 #bytes=1 p3=0 obj#=9865 tim=79600071918
*** 2020-07-30 07:12:13.823
Processing Oradebug command 'event 10046 trace name context off'
*** 2020-07-30 07:12:17.759
Processing Oradebug command 'tracefile_name'
解析不到，10046文件没有内容。
errorstack
ErrorStack是Oracle提供的一种对于错误堆栈进行跟踪的方法，通过设置跟踪可以将一些错误的后台信息详尽地转储出来，写入跟踪文件，对于错误的研究与诊断非常有效。
设置ErrorStack主要有4个级别：
0 仅转储错误堆栈（0级已经被逐渐废弃）
1 转储错误堆栈和函数调用堆栈
2 Level1 + ProcessState
3 Level2 + Context area（显示所有cursors，着重显示当前cursor）
ErrorStack可以在实例级或会话级别设置，也可以在参数文件中设置，这个设置仅当某个特定的错误出现时才被触发，如设置ORA-00942事件的跟踪：
altersession set events ‘942 trace name errorstack level 1′;
先设置942的跟踪，看有没有有用信息
SQL> oradebug setmypid
Statement processed.
SQL> alter session set events '941 trace name errorstack level 3';
Session altered.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> oradebug tracefile_name
/export/home/oracle/opt/admin/orcl/udump/orcl_ora_1646.trc
设置完后，alert日志中会出现下面信息：
Thu Jul 30 07:23:03 CST 2020
Errors in file /export/home/oracle/opt/admin/orcl/udump/orcl_ora_1636.trc:
查看trc文件
bash-3.2$ cat /export/home/oracle/opt/admin/orcl/udump/orcl_ora_1646.trc
/export/home/oracle/opt/admin/orcl/udump/orcl_ora_1646.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /export/home/oracle/opt/product/10.2.0
System name: SunOS
Node name: oracle10g
Release: 5.10
Version: Generic_147148-26
Machine: i86pc
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 1646, image: oracle@oracle10g (TNS V1-V3)
*** ACTION NAME:() 2019-10-02 12:49:44.688
*** MODULE NAME:(sqlplus@oracle10g (TNS V1-V3)) 2019-10-02 12:49:44.688
*** SERVICE NAME:(SYS$USERS) 2019-10-02 12:49:44.688
*** SESSION ID:(543.3) 2019-10-02 12:49:44.688
kzam_upd_props: OCIStmtExecute1
Exception in kzam_upd_props, 942:ORA-00942: table or view does not exist
/export/home/oracle/opt/admin/orcl/udump/orcl_ora_1646.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /export/home/oracle/opt/product/10.2.0/
System name: SunOS
Node name: oracle10g
Release: 5.10
Version: Generic_147148-26
Machine: i86pc
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 1646, image: oracle@oracle10g (TNS V1-V3)
*** ACTION NAME:() 2020-07-30 07:27:18.377
*** MODULE NAME:(sqlplus@oracle10g (TNS V1-V3)) 2020-07-30 07:27:18.377
*** SERVICE NAME:(SYS$USERS) 2020-07-30 07:27:18.377
*** SESSION ID:(534.12) 2020-07-30 07:27:18.377
*** 2020-07-30 07:27:18.377
Processing Oradebug command 'setmypid'
*** 2020-07-30 07:27:34.385
Processing Oradebug command 'tracefile_name'
此trc没有任何有用信息，所以在此处不适用。
继续做系统级的errorstack和processstate：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> oradebug tracefile_name
/export/home/oracle/opt/admin/orcl/udump/orcl_ora_1636.trc
SQL> oradebug dump processstate 3
Statement processed.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00942: table or view does not exist
暂时看不懂。。。trc先放着，后面在学习。
mos
最后的大招，mos了下，发现是个bug。具体文章参考后面。
下面模拟过程：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 2098208 bytes
Variable Size 201329632 bytes
Database Buffers 402653184 bytes
Redo Buffers 6287360 bytes
Database mounted.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> alter database open;
Database altered.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from dba_data_files;
COUNT(*)
----------
4
原因
数据库在mount状态，desc dba_data_files,查看了表结构。触发了bug，Bug 2365821
解决方案
1）SQL> Alter system flush shared_pool;
2)重启数据库
参考
ORA-4043 On DBA_* Views If They Are Described In Mount Stage (Doc ID 296235.1) To BottomTo Bottom
In this Document
Symptoms
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.1 [Release 8.1.7 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
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 to
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 related to Bug 2365821
Abstract: ORA-4043 ON DBA_* TABLES IF THEY ARE DESCRIBED IN A MOUNT STAGE.
SOLUTION
Available workarounds are:
- Don’t describe the dba_* views at mount stage.
OR - If you issue DESC of any DBA_*views at mount stage, then shutdown and restart the DB instance.
OR - Flush the shared pool.
SQL> Alter system flush shared_pool;
and then reissue the failing command.
8625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
