Re: Change init.ora parameter value for another session

Hmm..looks i have to settle down for the AFTER LOGON trigger.

cheers
anand

On 30/08/06, Anand Rao <panandrao_at_gmail.com> wrote:
>
> 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
>

-- 
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