ORA-00701: object necessary for warmstarting database cannot be altered

ORA-00701: object necessary for warmstarting database cannot be altered

 

 

در زمان اجرای دستور :

execute Analyze_Table(‘SCHEMA’, ‘TABLENAME’);

خطای زیر رخ داد:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 447, file 1, block 29781 (2)
ORA-06512: at “SYS.DBMS_STATS”, line 24281
ORA-06512: at “SYS.DBMS_STATS”, line 24332
که مشخص شد آبجکت شماره 447 دچار مشکل شده است.
جهت یافت آبجکت مورد نظر دستورات زیر را اجرا کنید:

 

select object_name from dba_objects where data_object_id =447;              ==> I_H_OBJ#_COL#  (index)
select table_name from dba_indexes where index_name=’I_H_OBJ#_COL#’;   ==>   HISTGRM$ (Table)

 

جهت برطرف کردن ایراد مذکور می بایست ایندکس مورد نظر recreate گردد. به هنگام اجرای دستور drop با خطای زیر مواجه می شوید:

 

ORA-00701: object necessary for warmstarting database cannot be altered

 

زیرا ایندکس مورد نظر جزو آبکجت های یوزر SYS بوده و شما نمیتوانید آبجکت های یوزر SYS را Alter  نمایید.
جهت رفع این مشکل مراحل زیر را انجام دهید:
  1. دیتابیس را در مد UPGRADE بالا بیاورید:

 

SQL> shut immadiate;
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 2113930424 bytes
Database Buffers 2147483648 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.
2.ایندکس مورد نظر را DROP و CREATE نمایید:

 

DROP INDEX SYS.I_H_OBJ#_COL#;
CREATE INDEX SYS.I_H_OBJ#_COL# ON SYS.HISTGRM$
(OBJ#, COL#)
LOGGING
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 2113930424 bytes
Database Buffers 2147483648 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.
SQL>

IN ENGLISH

ORA-00701: object necessary for warmstarting database cannot be altered

 

when you run this command :

execute Analyze_Table(‘SCHEMA’, ‘TABLENAME’);

you get these error’s:

ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 447, file 1, block 29781 (2)
ORA-06512: at “SYS.DBMS_STATS”, line 24281
ORA-06512: at “SYS.DBMS_STATS”, line 24332

you have problem with object number (447) .

you can find the object with these commands:

select object_name from dba_objects where data_object_id =447;  ==> I_H_OBJ#_COL#  (index)
select table_name from dba_indexes where index_name=’I_H_OBJ#_COL#’; ==>  HISTGRM$ (Table)

 

You get this error when you run rebuild command  :

ORA-00701: object necessary for warmstarting database cannot be altered

 

Because owner of this index is SYS and yo can’t ALter any sys object

SO for recreat index you have to do :

 

  1. 1.Startup database in UPGRADE MODE:

SQL> shut immadiate;
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 2113930424 bytes
Database Buffers 2147483648 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.

 

2.DROP & CREATE the index

DROP INDEX SYS.I_H_OBJ#_COL#;
CREATE INDEX SYS.I_H_OBJ#_COL# ON SYS.HISTGRM$
(OBJ#, COL#)
LOGGING
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);

 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 2113930424 bytes
Database Buffers 2147483648 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened.
SQL>

 

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *