1  Create or replace trigger alt_session
 2  after logon on database
 3  begin
 4  if sys_context('userenv','session_user') ='SCOTT' Then
 5     execute immediate 'alter session set SKIP_UNUSABLE_INDEXES = TRUE' ;
 6  end if;
 7* end;
 8  /

Trigger created.

oracle@mutation#sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Aug 4 13:28:15 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

scott@9i > create index myindex on emp(empno);

Index created.

scott@9i > alter table emp move tablespace users;

Table altered.

scott@9i > select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
MYINDEX                        UNUSABLE

scott@9i > insert into emp (select * from emp);

14 rows created.

scott@9i > commit;

Commit complete.

scott@9i > alter session set skip_unusable_indexes=false;

Session altered.

scott@9i >  insert into emp (select * from emp);
 insert into emp (select * from emp)
*
ERROR at line 1:
ORA-01502: index 'SCOTT.MYINDEX' or partition of such index is in unusable state