giovedì 26 marzo 2009

ORA-01940: Cannot drop a user that is currently connected

E' un pò di tempo che non scrivo sul mio blog, ma, ahimè, in questo periodo il lavoro con Oracle ha latitato e, di conseguenza, anche gli spunti da cui traggo i miei post :(
Questa mattina, tuttavia, una semplice operazione di DROP di un utente del mio db di test ha originato l'errore Oracle ORA-01940: cannot DROP a user that is currently connected
Premessa: lo schema di cui sopra costituisce il backend di un'applicazione web-based realizzata in jsp e gestita da Tomcat. Ovviamente, prima di eseguire, da utente system, la drop dello schema desiderato, mi sono preoccupato di avvertire gli utenti che stavano lavorando nell'ambiente di test di effetturare il logout, quindi, atteso un ragionevole lasso di tempo, ho stoppato il server Tomcat.
Svolgimento: ho pensato che eventuali sessioni appese a livello di db dovessero dipendere dalla presenza di qualche utente ancora connesso e con delle operazioni in corso nell'ambiente di test nel momento in cui ho eseguito lo stop del server Tomcat.

La vista v$session fornisce tutte le informazioni sulle sessioni attive/inattive/killate ed è proprio da questa che bisogna partire per fare un pò di diagnostica.
L'istruzione SELECT seguente interroga proprio tale vista in join con un'altra vista di sistema, ossia v$process in modo tale da ottenere l'id della, il codice seriale e lo status sessione, nonchè il pid del processo ad essa collegato.
SQL> select s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = 'MYUSER'
and p.addr (+) = s.paddr;

Il risultato che si ottiene è qualcosa di simile:
SID SERIAL# STATUS SPID
---------- ---------- -----------------
136 48808 INACTIVE 12065
135 31595 INACTIVE 12030
132 12779 INACTIVE 11092

Ora, le sessioni inattive possono essere terminate con il seguente comando (da ripetere per ogni sessione da terminare, ovviamente e da lanciare in qualità di utente con privilegi da DBA):
SQL> alter system kill session '<#sid,#serial>';

Rieseguendo la query dalla vista v$session si otterrà che le sessioni sono passate allo stato "KILLED", tuttavia, come è successo a me, può capitare che dopo pochi istanti nuove sessioni (o persino le stesse appena killate) tornino nuovamente allo stato "INACTIVE" rendendo nuovamente impossibile eseguire il drop dell'utente.
Personalmente credo che Oracle cerchi di portare a termine i job sottomessi da Tomcat sul db server e incapsulati nelle sessioni di cui sopra, ma avendo ormai perso la connessione con il Tomcat, tali sessioni restano sempre nello stato "INACTIVE".
La soluzione in realtà è semplice, infatti basta mettere in lock l'utente prima di dropparne le sessioni inattive:
SQL> alter user MYUSER account lock;

Quindi si può procedere con il drop dell'utente in tutta tranquillità!

Conclusione: partendo dalle viste di sistema si risolve tutto! ;)

L'immagine di inizio post con i nanetti da giardino l'ho recuperata da qui ed è rilasciata con licenza Creative Commons Attribution-No Derivative Works 2.0 Generic (quindi è possibile copiarla e distribuirla). L'ho messa perchè uno dei nani ha degli occhiali da sole --> servono per vedere --> viste di Oracle --> sclero :-D