linux ora 01157,ORA-01157错误的解决

今天,我在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,正常,这说明查询数字字典视图的执行计划跟临时文件有关.