Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Logon Trigger on schema

RE: Logon Trigger on schema

From: GUILLAUMIN Bert Ext ROSI/SI CLIENT <bguillaumin.ext_at_francetelecom.com>
Date: Tue, 11 Apr 2006 10:52:33 +0200
Message-Id: <005e01c65d45$46668bd0$ab068c0a@lille.francetelecom.fr>

-----Message d'origine-----
De : Bertrand Guillaumin [mailto:bguillaumin.ext_at_francetelecom.com] EnvoyÚ : jeudi 6 avril 2006 15:29
└ : 'Oracle-L Freelists'
Objet : Logon Trigger on schema

Hi! I'm trying to use a logon trigger on a schema so that it will systematically have the skip_unusable_indexes set to true and parallel DML enabled(I have to use an app which sometimes disconnects and reconnects without any visible reason).

So I wrote this trigger :

SQL> CREATE OR REPLACE TRIGGER FC_ENABLE_PARALLEL_DML after LOGON ON SCHEMA   2 BEGIN
  3 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';   4 EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE';   5 END;
  6 /

D┌clencheur cr┌┌.

and created a table for testing purpose : SQL> CREATe TABLE T AS SELECT 1 as num FROM DUAL   2 /

Table cr┌┌e.

SQL> CREATE INDEX I ON T(NUM)
  2 /

Index cr┌┌.

SQL> ALTER INDEX I UNUSABLE
  2 /

Index modifi┌.

But the problem is, when I disconnect and reconnect via sqlplus and try to update an indexed column with the same user :

SQL> UPDATE T set NUM = 2;
UPDATE T set NUM = 2
*

ERREUR Ë la ligne 1 :
ORA-01502: index 'CP20DEV2.I' or partition is unusable

I'm working on 9.2.0.4.
Any hint or advice appreciated.

Regards,
Bertrand Guillaumin



Ce message et toutes les pieces jointes (ci-apres le "message") sont confidentiels et etablis a l'intention exclusive de ses destinataires.
Toute utilisation ou diffusion non autorisee est interdite. Tout message electronique est susceptible d'alteration. Le Groupe France Telecom decline toute responsabilite au titre de ce message s'il a ete altere, deforme ou falsifie. Si vous n'etes pas destinataire de ce message, merci de le detruire immediatement et d'avertir l'expediteur.

This message and any attachments (the "message") are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited.
Messages are susceptible to alteration. France Telecom Group shall not be liable for the message if altered, changed or falsified.
If you are not the intended addressee of this message, please cancel it immediately and inform the sender.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 11 2006 - 03:52:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US