Re: Dropping user with logged in sessions

From: gazzag <gareth_at_jamms.org>
Date: 8 Feb 2007 06:14:18 -0800
Message-ID: <1170944058.293393.279550@a75g2000cwd.googlegroups.com>

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 the user A.
>
> The first time I tried it, the loop ended immediatly
> after session kill. However, neither did the truncate
> nor the drop user work. 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 the sessions log, 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 DROP USER worked 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> create user a
  2 identified by a
  3 default tablespace users
  4 temporary tablespace temp
  5 quota unlimited on users
  6 account unlock;

User created.

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> drop user a cascade;

User dropped.

SESSION 1

begin
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL> create user a
  2 identified by a
  3 default tablespace users
  4 temporary tablespace temp
  5 quota unlimited on users
  6 account unlock;

User created.

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> drop user a cascade;

User dropped.

SESSION 1

begin
*
ERROR at line 1:
ORA-00028: your session has been killed

HTH -g Received on Thu Feb 08 2007 - 08:14:18 CST