Cannot delete parameter optimizer_index_cost_adj on Oracle 10.2

[Former Member]

Hi all,

I want to delete the parameter optimizer_index_cost_adj in Oracle 10.2 as per SAP recommendations, I did the following to remove it from the spfile:

SQL> alter system reset optimizer_index_cost_adj scope=spfile sid='*';

System altered.

but when I restarted Oracle, the parameter was once again set to 10 ! If I try and reset it in memory I get this error:

SQL> alter system reset optimizer_index_cost_adj scope=memory sid='*';

alter system reset optimizer_index_cost_adj scope=memory sid='*'

*

ERROR at line 1:

ORA-32009: cannot reset the memory value for instance * from instance BPA

any help much appreciated !

Stu

[former_member185239 · Accepted solution]

Hi Stuart,

Kindly check the post

Also do check the V$PARAMETER2 table . if you find the value ISDEFAULT = true against the parameter optimizer_index_cost_adj , it means the value has been reset.

so use the below command

SELECT INSTANCE, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

With Regards

Ashutosh Chaturvedi

[Former Member · Accepted solution]

Hi Ashutosh,

That's answered the question, thanks very much !

I looked everywhere in SCN and Google search, but couldn't find the answer

Stu