Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: Logon Trigger on schema

Yasin Baskan

2006-04-11

Replies:


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@(protected)
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@(protected)]
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





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
--
http://www.freelists.org/webpage/oracle-l