Hi guys,
I'm looking for reasons why this happens & how to avoid it.
I open two windows (linux), the first is connected to SQL*Plus. The second is connected to the OS.
1. I then use window 2 to identify to process id connected to SQL*Plus (lets say 70).
2. In the SQL window I then run the following code:
set serveroutput on
spool query.test
DECLARE
v_online number;
BEGIN
dbms_lock.sleep(30); -- this is for testing hanging sessions
select count(*) into v_online from v$instance where status='OPEN';
IF v_online = 1 THEN
EXECUTE IMMEDIATE 'CREATE USER CC PROFILE "DEFAULT"
IDENTIFIED BY "CC" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK';
END IF;
END;
/
3. I then kill the process id of the SQL*Plus session after around 5 seconds. The SQL*Plus window shows Terminated. The other window can no longer find SQL*Plus sessions.
4. I re-connect to the database and run the following after around 20 seconds:
SELECT USERNAME FROM DBA_USERS WHERE USERNAME='CC';
ISSUE: This shows no user as I would expect since the session was terminated during the sleep, before the create user. However, if I run the same query again after 10-15 seconds (duration of the sleep) then the user appears. Shouldn't the actions of the PL/SQL block be rolled back when the PID is killed? Is there a way to force this?
Edit: surely it's not because i should use kill -9 70 instead of kill 70?
Dird
Message was edited by: Dird