Oracle insert dual,Oracle 誤刪 DUAL 的解決方法

Solution

1 ) Error 980 during db open is causing the shut down of the database

2 ) Need to create the Dual Table to resolve the issue (which was not possible in the mount stage)

Workaround

=========

3 ) Set the following parameter in the init.ora

replication_dependency_tracking = FALSE

4 ) Startup the database

5 ) Create the Dual Table and Dual Public Synonym

CREATE TABLE "SYS"."DUAL"

( "DUMMY" VARCHAR2(1)

) PCTFREE 10 PCTUSED 4;

Insert Into Dual Values ('X');

Commit;

Grant Select On Dual To Public;

6 ) Remove the above parameter and restart the database

Note :- Dual table should not be dropped in any case , as it can lead to serious problems

Errors[@more@]SYS@HUIYI>drop table dual;

Table dropped.

SYS@HUIYI>startup force;

ORACLE instance started.

Total System Global Area 143727516 bytes

Fixed Size 453532 bytes

Variable Size 109051904 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

@>startup mount;

ORACLE instance started.

Total System Global Area 143727516 bytes

Fixed Size 453532 bytes

Variable Size 109051904 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

@>create pfile from spfile;

File created.

-- 修改參數文件加上replication_dependency_tracking = FALSE

……

-- 啟動數据庫

SYS@HUIYI>shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@HUIYI>startup pfile=G:A9Idatabaseinithuiyi.ora;

ORACLE instance started.

Total System Global Area 143727516 bytes

Fixed Size 453532 bytes

Variable Size 109051904 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

-- 創建dual 表

SYS@HUIYI>create table sys.dual

2 (

3 dummy varchar2(1)

4 )

5 pctfree 10 pctused 4;

Table created.

SYS@HUIYI>insert into dual values('X');

1 row created.

SYS@HUIYI>commit;

Commit complete.

SYS@HUIYI>grant select on dual to public;

Grant succeeded.

SYS@HUIYI>startup force;

ORACLE instance started.

Total System Global Area 143727516 bytes

Fixed Size 453532 bytes

Variable Size 109051904 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SYS@HUIYI>select sysdate from dual;

SYSDATE

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

07-Mar-07 10:26:15

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8799875/viewspace-903154/，如需转载，请注明出处，否则将追究法律责任。