Re: Dropping user with logged in sessions

On 8 fév, 15:14, "gazzag" <gar..._at_jamms.org> wrote:
> On 8 Feb, 06:55, hasta..._at_hotmail.com wrote:
>
>
>
> > Joel and Gareth,
>
> > I did a quick test (under Oracle 10g) :
>
> > Session A inserts a row in a table, then enters
> > an infinite pl/sql loop.
>
> > Session SYS kills session A (without immediate keyword),
> > then tries to truncate the table and drop theuserA.
>
> > The first time I tried it, the loop ended immediatly
> > after session kill. However, neither did the truncate
> > nor the dropuserwork. Both failed with ORA-00054
> > resource busy. I had to wait a few minutes.
>
> > I repeated the test three times afterwards, and
> > in all cases the truncate DID work immediatly
> > after killing the session.
>
> > I tripled-checked thesessionslog, and am
> > pretty sure I didnt do a mistake in my first test
> > run.
>
> > I still need to repeat the test on O8 / O9, but would
> > any of you have a reproducible test case, by
> > any chance ? It would be much appreciated.
>
> > --- Raoul- Hide quoted text -
>
> > - Show quoted text -
>
> Raoul,
>
> I tried a couple of tests. One with the COMMIT immediately after the
> INSERT, the other with the COMMIT after the infinite loop. I got two
> slightly different results, but the TRUNCATE and DROPUSERworked as
> expected:
>
> SESSION 1
> =========
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
> Prod
> PL/SQL Release 10.2.0.1.0 -
> Production
> CORE 10.2.0.1.0
> Production
> TNS for Linux: Version 10.2.0.1.0 -
> Production
> NLSRTL Version 10.2.0.1.0 -
> Production
>
> SQL> createusera
> 2 identified by a
> 3 default tablespace users
> 4 temporary tablespace temp
> 5 quota unlimited on users
> 6 account unlock;
>
> Usercreated.
>
> SQL> grant create session, create table to a;
>
> Grant succeeded.
>
> SQL> connect a/a
> Connected.
> SQL> create table t (
> 2 col1 varchar2(10)
> 3 );
>
> Table created.
>
> SQL> begin
> 2 insert into t (col1)
> 3 values ('abcdefg');
> 4 commit;
> 5
> 6 loop
> 7 if 1=2
> 8 then exit;
> 9 end if;
> 10 end loop;
> 11 end;
> 12 /
>
> SESSION 2
> =========
>
> SQL> select sid, serial# from v$session
> 2 where username='A';
>
> SID
> SERIAL#
> ----------
> ----------
> 149
> 10135
>
> SQL> alter system kill session '149, 10135';
> alter system kill session '149, 10135'
> *
> ERROR at line 1:
> ORA-00031: session marked for kill
> SQL>
> SQL> select sid, serial# from v$session
> 2 where username='A';
>
> no rows selected
>
> SQL> truncate table a.t;
>
> Table truncated.
>
> SQL> dropusera cascade;
>
> Userdropped.
>
> SESSION 1
> =========
>
> begin
> *
> ERROR at line 1:
> ORA-00028: your session has been killed
>
> SQL> createusera
> 2 identified by a
> 3 default tablespace users
> 4 temporary tablespace temp
> 5 quota unlimited on users
> 6 account unlock;
>
> Usercreated.
>
> SQL> grant create session, create table to a;
>
> Grant succeeded.
>
> SQL> connect a/a
> Connected.
> SQL> create table t (
> 2 col1 varchar2(10)
> 3 );
>
> Table created.
>
> SQL> begin
> 2 insert into t (col1)
> 3 values ('abcdefg');
> 4 loop
> 5 if 1=2
> 6 then exit;
> 7 end if;
> 8 end loop;
> 9 commit;
> 10 end;
> 11 /
>
> SESSION 2
> =========
>
> SQL> select sid, serial# from v$session
> 2 where username='A';
>
> SID
> SERIAL#
> ----------
> ----------
> 142
> 6586
>
> SQL> alter system kill session '142, 6586';
>
> System altered.
>
> SQL> truncate table a.t;
>
> Table truncated.
>
> SQL> dropusera cascade;
>
> Userdropped.
>
> SESSION 1
> =========
>
> begin
> *
> ERROR at line 1:
> ORA-00028: your session has been killed
>

Thanks, Gareth

I tested again, on O10 and O9,
and indeed I could not get the test to fail.

Have very nice day

• Raoul