Change init.ora parameter value for another session

From: Anand Rao <panandrao_at_gmail.com>
Date: Wed, 30 Aug 2006 12:36:55 +0530
Message-ID: <d70710370608300006n97897f9x3b636fa330828892@mail.gmail.com>

Hi,

I am trying to set skip_unusable_indexes = true for about 25 sessions before i perform a data load. all those 25 session are connected to Oracle. The version is 9206 and OS is AIX 5.3

here is the simple PL/SQL block,

 declare
 vs_sid v$session.sid%type;
 vs_serial v$session.serial#%type;
 cursor c1 is select s.sid,s.serial# from  v$session s, v$process p where p.addr = s.paddr and s.username = 'SCOTT';  begin
 for vsrec in c1 loop

 DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION (vsrec.sid, vsrec.serial#,

'skip_unusable_indexes', TRUE)

 dbms_output.put_line('Successfully set the parameter TRUE for :
'||vsrec.sid);

 end loop;
 end;

the block executes successfully but when i try to insert data, i get errors.

insert into emp_range values (5010,'ZZZ',1) *
ERROR at line 1:
ORA-01502: index 'SCOTT.EMP_RANGE_PART_IDX' or partition of such index is in unusable state

of course, if i do a 'alter session set skip_unusable_indexes = true' as user SCOTT, the insert works fine. i want to do it from SYS using the PL/SQL block.

It seems the parameter change hasn't taken effect. i checked V$PARAMETER and V$PARAMETER2 for SCOTT but this parameter is not listed there.

I tried changing timed_statistics for SCOTT and it works fine and reflected in V$PARAMETER.

Is it that we cannot change undocumented parameters? (like skip_unusable_indexes)

thanks
anand

-- 
All I need to make a comedy is a park, a policeman and a pretty girl -
Charlie Chaplin

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 30 2006 - 02:06:55 CDT