--- Begin Message ---
- From: "GUILLAUMIN Bert Ext ROSI/SI CLIENT" <bguillaumin.ext@xxxxxxxxxxxxxxxxx>
- To: "'Yasin Baskan'" <yasbs@xxxxxxxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Apr 2006 15:08:26 +0200
Your solution didn't solve my problem but I found the reason and the
workaround on metalink so I put it here if someone is interested(I hadn't
any error raised because my user had the "ADMINISTER DATABASE TRIGGER"
role) :
Subject: Execution of LOGON trigger results in ORA-12841
Doc ID: Note:271509.1 Type: PROBLEM
Last Revision Date: 05-OCT-2004 Status: PUBLISHED
The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
This problem can occur on any platform.
Symptoms
A LOGON trigger has been created to enable a PARALLEL DML during the
session
i.e.
CREATE OR REPLACE TRIGGER LOG_ON AFTER
LOGON ON DATABASE
BEGIN
execute immediate 'alter session enable parallel dml';
END;
LOGON trigger throws the following error:
ORA-604: error occurred at recursive SQL level 1
ORA-12841: Cannot alter the session parallel DML state within a
transaction
ORA-6512: at line 2
Cause
Parallel DML cannot be enabled during the transaction.
i.e.
SQL> insert into emp values ('fdfg');
1 row created.
SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a
transaction
-- The transaction should be completed before enabling the PARALLEL DML
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
Logon trigger executes as an autonomous transaction and that is why the
error ORA-12841 occurs.
Fix
1) invoke "alter session enable parallel dml" in other autonomous
transaction through a function.
2) call a function in a trigger
e.g.
create or replace function myfunc return number is
PRAGMA AUTONOMOUS_TRANSACTION;
x number:=0;
begin
execute immediate 'alter session enable parallel dml';
return x;
end;
create or replace trigger log_on after
logon on database
declare
x number;
begin
x := myfunc;
end;
-----Message d'origine-----
De : Yasin Baskan [mailto:yasbs@xxxxxxxxxxxxxx]
Envoyé : mardi 11 avril 2006 12:40
À : GUILLAUMIN Bert Ext ROSI/SI CLIENT; Oracle-L Freelists
Objet : RE: Logon Trigger on schema
Please specify the schema name as follows. This solves the issue.
SQL> UPDATE T set NUM = 2;
UPDATE T set NUM = 2
*
ERROR at line 1:
ORA-01502: index 'SCOTT.I' or partition of such index is in unusable state
SQL> begin
2 EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> UPDATE T set NUM = 2;
1 row updated.
SQL> CREATE OR REPLACE TRIGGER FC_ENABLE_PARALLEL_DML after LOGON ON
SCOTT.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE';
END;
/ 2 3 4 5 6 7
Trigger created.
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit
Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
SQL> conn scott
Enter password:
Connected.
SQL> UPDATE T set NUM = 2;
1 row updated.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of GUILLAUMIN Bert Ext ROSI/SI CLIENT
Sent: Tuesday, April 11, 2006 11:53 AM
To: 'Oracle-L Freelists'
Subject: RE: Logon Trigger on schema
-----Message d'origine-----
De : Bertrand Guillaumin [mailto:bguillaumin.ext@xxxxxxxxxxxxxxxxx]
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.
********************************
--
//www.freelists.org/webpage/oracle-l
Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel
ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu
mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz
ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina
iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini
kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji
gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde
ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi
Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi
kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi
zararlardan Bankamiz sorumlu tutulamaz.
This message and the files attached to it are under the privacy liability
in accordance with the Banking Law and confidential to the use of the
individual or entity to whom they are addressed. This message cannot be
copied, disclosed or sold monetary consideration for any purpose. If you
are not the intended recipient of this message, you should not copy,
distribute, disclose or forward the information that exists in the content
and in the attachments of this message; please notify the sender
immediately and delete all copies of this message. Our Bank does not
warrant the accuracy, integrity and currency of the information
transmitted with this message. This message has been detected for all
known computer viruses thence our Bank is not liable for the occurrence of
any system corruption caused by this message
--- End Message ---
