如何杀掉一个用户下的所有进程并drop掉这个用户

如何杀掉一个用户下的所有进程并drop掉这个用户

Copy the sample code below into a file named kill_drop_user.sql.
Open SQL*Plus and connect as user SYS to your database
SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
Create a user called TEST with password TEST
SQL> GRANT connect, resource TO test IDENTIFIED BY test;
Create the procedure kill_drop_user
SQL> @”C:/scripts/kill_drop_user.sql”
Open three (3) SQL*Plus sessions and connect as user TEST.
Execute the PL/SQL script
SQL> SET serveroutput ON size 1000000 SQL> SET timing ON SQL> EXEC kill_drop_user(‘TEST’);
CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
SAMPLE CODE

CREATE OR REPLACE PROCEDURE kill_drop_user (in_username IN VARCHAR2,
                                            sleep_interval IN NUMBER DEFAULT 10)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;

  cannot_drop_user EXCEPTION;
  PRAGMA EXCEPTION_INIT(cannot_drop_user, -1940);

  user_count    NUMBER := -1;

BEGIN

  SELECT count(*) INTO user_count FROM dba_users WHERE username = in_username;
  IF user_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE(‘User ‘ || in_username || ‘ does not exist.’);
    RETURN;
  END IF;

  FOR i IN (SELECT sid, serial# FROM v$session WHERE username = in_username) LOOP
    EXECUTE IMMEDIATE ‘alter system kill session ‘ || ”” || i.sid || ‘,’ || i.serial# || ”’ immediate';
    DBMS_OUTPUT.PUT_LINE(‘Killing user ‘ || i.sid || ‘, ‘ || i.serial#);
  END LOOP;

  LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘Attempting to drop user ‘ || in_username || ‘…’);
      EXECUTE IMMEDIATE ‘DROP USER ‘ || in_username || ‘ CASCADE';

      EXIT WHEN SQLCODE <> -1940;
    EXCEPTION
      WHEN cannot_drop_user THEN
        –DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE(‘Waiting ‘ || sleep_interval || ‘ seconds for resource clean-up…’);
        DBMS_LOCK.SLEEP(sleep_interval);
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(‘Inner: ‘ || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(‘Exiting loop with SQLCODE: ‘ || SQLCODE);
  DBMS_OUTPUT.PUT_LINE(‘User ‘ || in_username || ‘ has been dropped.’);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(‘Outer: ‘ || SQLERRM);
END;
/
SAMPLE OUTPUT

SQL*Plus: Release 10.1.0.4.0 – Production on Tue Sep 6 15:34:47 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
Connected.
SQL> GRANT connect, resource TO test IDENTIFIED BY test;

Grant succeeded.

SQL> @”C:/scripts/kill_drop_user.sql”
Procedure created.

SQL> SET serveroutput ON size 1000000

SQL> SET timing ON

SQL> EXEC kill_drop_user(‘TEST’);
Killing user 152, 6915
Killing user 153, 326
Killing user 154, 156
Attempting to drop user TEST…
Waiting 10 seconds for resource clean-up…
Attempting to drop user TEST…
Exiting loop with SQLCODE: 0
User TEST has been dropped.

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.71

SQL> — verify user has been dropped
SQL> CONNECT test/test@orcl
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> 

转自MOS
如何杀掉一个用户下的所有进程并drop掉这个用户

Copy the sample code below into a file named kill_drop_user.sql.
Open SQL*Plus and connect as user SYS to your database
SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
Create a user called TEST with password TEST
SQL> GRANT connect, resource TO test IDENTIFIED BY test;
Create the procedure kill_drop_user
SQL> @”C:/scripts/kill_drop_user.sql”
Open three (3) SQL*Plus sessions and connect as user TEST.
Execute the PL/SQL script
SQL> SET serveroutput ON size 1000000 SQL> SET timing ON SQL> EXEC kill_drop_user(‘TEST’);
CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
SAMPLE CODE

CREATE OR REPLACE PROCEDURE kill_drop_user (in_username IN VARCHAR2,
                                            sleep_interval IN NUMBER DEFAULT 10)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;

  cannot_drop_user EXCEPTION;
  PRAGMA EXCEPTION_INIT(cannot_drop_user, -1940);

  user_count    NUMBER := -1;

BEGIN

  SELECT count(*) INTO user_count FROM dba_users WHERE username = in_username;
  IF user_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE(‘User ‘ || in_username || ‘ does not exist.’);
    RETURN;
  END IF;

  FOR i IN (SELECT sid, serial# FROM v$session WHERE username = in_username) LOOP
    EXECUTE IMMEDIATE ‘alter system kill session ‘ || ”” || i.sid || ‘,’ || i.serial# || ”’ immediate';
    DBMS_OUTPUT.PUT_LINE(‘Killing user ‘ || i.sid || ‘, ‘ || i.serial#);
  END LOOP;

  LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘Attempting to drop user ‘ || in_username || ‘…’);
      EXECUTE IMMEDIATE ‘DROP USER ‘ || in_username || ‘ CASCADE';

      EXIT WHEN SQLCODE <> -1940;
    EXCEPTION
      WHEN cannot_drop_user THEN
        –DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE(‘Waiting ‘ || sleep_interval || ‘ seconds for resource clean-up…’);
        DBMS_LOCK.SLEEP(sleep_interval);
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(‘Inner: ‘ || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(‘Exiting loop with SQLCODE: ‘ || SQLCODE);
  DBMS_OUTPUT.PUT_LINE(‘User ‘ || in_username || ‘ has been dropped.’);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(‘Outer: ‘ || SQLERRM);
END;
/
SAMPLE OUTPUT

SQL*Plus: Release 10.1.0.4.0 – Production on Tue Sep 6 15:34:47 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
Connected.
SQL> GRANT connect, resource TO test IDENTIFIED BY test;

Grant succeeded.

SQL> @”C:/scripts/kill_drop_user.sql”
Procedure created.

SQL> SET serveroutput ON size 1000000

SQL> SET timing ON

SQL> EXEC kill_drop_user(‘TEST’);
Killing user 152, 6915
Killing user 153, 326
Killing user 154, 156
Attempting to drop user TEST…
Waiting 10 seconds for resource clean-up…
Attempting to drop user TEST…
Exiting loop with SQLCODE: 0
User TEST has been dropped.

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.71

SQL> — verify user has been dropped
SQL> CONNECT test/test@orcl
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> 

转自MOS

Copy the sample code below into a file named kill_drop_user.sql.
Open SQL*Plus and connect as user SYS to your database
SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
Create a user called TEST with password TEST
SQL> GRANT connect, resource TO test IDENTIFIED BY test;
Create the procedure kill_drop_user
SQL> @”C:/scripts/kill_drop_user.sql”
Open three (3) SQL*Plus sessions and connect as user TEST.
Execute the PL/SQL script
SQL> SET serveroutput ON size 1000000 SQL> SET timing ON SQL> EXEC kill_drop_user(‘TEST’);
CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
SAMPLE CODE

CREATE OR REPLACE PROCEDURE kill_drop_user (in_username IN VARCHAR2,
                                            sleep_interval IN NUMBER DEFAULT 10)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;

  cannot_drop_user EXCEPTION;
  PRAGMA EXCEPTION_INIT(cannot_drop_user, -1940);

  user_count    NUMBER := -1;

BEGIN

  SELECT count(*) INTO user_count FROM dba_users WHERE username = in_username;
  IF user_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE(‘User ‘ || in_username || ‘ does not exist.’);
    RETURN;
  END IF;

  FOR i IN (SELECT sid, serial# FROM v$session WHERE username = in_username) LOOP
    EXECUTE IMMEDIATE ‘alter system kill session ‘ || ”” || i.sid || ‘,’ || i.serial# || ”’ immediate';
    DBMS_OUTPUT.PUT_LINE(‘Killing user ‘ || i.sid || ‘, ‘ || i.serial#);
  END LOOP;

  LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘Attempting to drop user ‘ || in_username || ‘…’);
      EXECUTE IMMEDIATE ‘DROP USER ‘ || in_username || ‘ CASCADE';

      EXIT WHEN SQLCODE <> -1940;
    EXCEPTION
      WHEN cannot_drop_user THEN
        –DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE(‘Waiting ‘ || sleep_interval || ‘ seconds for resource clean-up…’);
        DBMS_LOCK.SLEEP(sleep_interval);
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(‘Inner: ‘ || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(‘Exiting loop with SQLCODE: ‘ || SQLCODE);
  DBMS_OUTPUT.PUT_LINE(‘User ‘ || in_username || ‘ has been dropped.’);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(‘Outer: ‘ || SQLERRM);
END;
/
SAMPLE OUTPUT

SQL*Plus: Release 10.1.0.4.0 – Production on Tue Sep 6 15:34:47 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
Connected.
SQL> GRANT connect, resource TO test IDENTIFIED BY test;

Grant succeeded.

SQL> @”C:/scripts/kill_drop_user.sql”
Procedure created.

SQL> SET serveroutput ON size 1000000

SQL> SET timing ON

SQL> EXEC kill_drop_user(‘TEST’);
Killing user 152, 6915
Killing user 153, 326
Killing user 154, 156
Attempting to drop user TEST…
Waiting 10 seconds for resource clean-up…
Attempting to drop user TEST…
Exiting loop with SQLCODE: 0
User TEST has been dropped.

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.71

SQL> — verify user has been dropped
SQL> CONNECT test/test@orcl
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> 

转自MOS

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » 如何杀掉一个用户下的所有进程并drop掉这个用户

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址