今天,我在SQL*PLUS想看下查询数字字典的执行计划:

SQL> SET TIMING ON;
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM DBA_ROLES WHERE NOT EXISTS
  2  (SELECT NULL FROM DBA_ROLE_PRIVS PR WHERE PR.granted_role=DBA_ROLES.role);

已用时间:  00: 00: 00.51

执行计划
----------------------------------------------------------
ERROR:
ORA-01157: 无法标识/锁定数据文件  - 请参阅 DBWR 跟踪文件

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
        473  consistent gets
          0  physical reads
          0  redo size
        509  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
          1  rows processed

难道是我的数据库不能跑出执行计划?

我用了select * from prd where code='04COCA04000'去测试,正常,再用了select * from dba_roles去跑执行计划,一样的错误,这证明查询数据字典的数据都不能跑执行计划,什么原因呢?

这才想起去看DBWR跟踪文件.

看到了这样一段话:

***********END PLSQL RUNTIME DUMP************
*** KEWUXS - encountered error: (ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB\TEMP01.DBF'
) 
*** KEWFADS: Error=13515 encountered by Auto DBFUS slave.

我初步确定是临时文件出了问题.

我查了一下临时文件:

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1                0                        3          1 ONLINE  READ WRITE          0          0     20971520       8192 C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB\TEMP01.DBF

我知道了,现在这个数据库是我前天才建的,文件是拿其他数据库备份下来的,放在d:\oracle\product\10.1.0\oradata\db目录下,数据文件,重做日记,控制文件都rename了,就是忘了rename临时文件.于是重建临时文件.

SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB\TEMP01.DBF'  offline drop;

SQL> alter tablespace temp add tempfile 'd:\oracle\product\10.1.0\oradata\TEMP01.DBF' size 100M;

Tablespace altered

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1                0                        3          1 ONLINE  READ WRITE  104857600      12800    104857600       8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\TEMP01.DBF

SQL> SELECT * FROM DBA_ROLES WHERE NOT EXISTS
  2  (SELECT NULL FROM DBA_ROLE_PRIVS PR WHERE PR.granted_role=DBA_ROLES.role);

已用时间:  00: 00: 00.45

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=142 Card=34 Bytes=
          816)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'USER$' (CLUSTER) (Cost=3 Card=34
           Bytes=816)

   3    1     SORT (GROUP BY) (Cost=8 Card=4 Bytes=256)
   4    3       NESTED LOOPS (OUTER) (Cost=7 Card=4 Bytes=256)
   5    4         HASH JOIN (Cost=7 Card=4 Bytes=152)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'USER$' (CLUSTER)
           (Cost=1 Card=1 Bytes=15)

   7    6             INDEX (UNIQUE SCAN) OF 'I_USER1' (INDEX (UNIQUE)
          ) (Cost=0 Card=1)

   8    5           HASH JOIN (Cost=6 Card=673 Bytes=15479)
   9    8             INDEX (FAST FULL SCAN) OF 'I_SYSAUTH1' (INDEX (U
          NIQUE)) (Cost=2 Card=673 Bytes=5384)

  10    8             TABLE ACCESS (FULL) OF 'USER$' (CLUSTER) (Cost=3
           Card=64 Bytes=960)

  11    4         INDEX (UNIQUE SCAN) OF 'I_DEFROLE1' (INDEX (UNIQUE))
           (Cost=0 Card=1 Bytes=26)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        469  consistent gets
          0  physical reads
          0  redo size
        509  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
          1  rows processed

ok,正常,这说明查询数字字典视图的执行计划跟临时文件有关.