Oracle 重建dual表

博客文章除注明转载外，均为原创。转载请注明出处。

如果dual表被误删除，或者损坏，重启oracle就会看到如下错误：

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00942: table or view does not exist

这里我就不在去重现整个过程了，简单写下重建的方法mark。

step1：编辑参数文件加入如下参数

create pfile='/home/oracle/pfile.ora' from spfile

replication_dependency_tracking = FALSE

参数的作用时oracle启动过程中跳过dual表的错误，从而打开数据库。

step2:启动数据库

startup

step3:创建dual表

CREATE TABLE "SYS"."DUAL"

(

"DUMMY" VARCHAR2(1)

)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(

INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE "SYSTEM"

Insert Into Dual Values ('X');

Commit;

step4:授权

grant select on sys.dual to public with grant option;

select count(*) from dual;

step5:重启数据库

shutdown immediate

create spfile from pfile='/home/oracle/pfile.ora'

startup

step6:检查

column OWNER format a15;

column OBJECT_NAME format a20;

select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where object_name='DUAL';

step：测试验证

conn test/test

select sysdate from dual;

---The end