在 Oracle 数据库中删除连接的用户

Question

提问by Chaitanya

I want to drop some users in Oracle DB using sqlplus but I am getting error:

我想使用 sqlplus 在 Oracle DB 中删除一些用户，但出现错误：

SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

I followed the link in SO to find out the sessions - Dropping a connected user from an Oracle 10g database schema

我按照 SO 中的链接查找会话 -从 Oracle 10g 数据库模式中删除连接的用户

But when I ran the command I am not getting any results:

但是当我运行命令时，我没有得到任何结果：

SQL> select sid,serial# from v$session where username = 'test';

no rows selected

Please help me how to drop users in this case.

请帮助我如何在这种情况下删除用户。

Answer 1

回答by Art

Users are all capitals in v$session(and data dictionary views). If you match with capitals you should find your session to kill.

用户都是大写的v$session（和数据字典视图）。如果您匹配大写字母，您应该找到要杀死的会话。

SELECT s.sid, s.serial#, s.status, p.spid 
  FROM v$session s, v$process p 
 WHERE s.username = 'TEST' --<<<--
  AND p.addr(+) = s.paddr
 /

Pass actual SID and SERIAL# values for user TEST then drop user...:

为用户 TEST 传递实际的 SID 和 SERIAL# 值，然后删除用户...：

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'
/

Answer 2

回答by Amir

Solution :

解决方案：

login as sysdaba:

以 sysdaba 身份登录：

sqlplus  / as sysdba

then:

然后：

sql>Shutdown immediate;

sql>startup restrict;

sql>drop user TEST cascade;

If you want to re-activate DB normally either reset the server or :

如果您想正常重新激活数据库，请重置服务器或：

sql>Shutdown immediate;

sql>startup;

:)

Answer 3

回答by mahesh agrawal

Issue has been fixed using below procedure :

问题已使用以下程序修复：

DECLARE
  v_user_exists NUMBER;
  user_name CONSTANT varchar2(20) := 'SCOTT';
BEGIN
  LOOP
    FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE upper(s.username) = user_name)
    LOOP
      EXECUTE IMMEDIATE
        'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE';
    END LOOP;
    BEGIN
      EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade';
      EXCEPTION WHEN OTHERS THEN
      IF (SQLCODE = -1940) THEN
        NULL;
      ELSE
        RAISE;
      END IF;
    END;
    BEGIN
      SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name;
      EXIT WHEN v_user_exists = 0;
    END;
  END LOOP;
END;
/

Answer 4

回答by Roman Proshin

Do a query:

做一个查询：

SELECT * FROM v$session s;

Find your user and do the next query (with appropriate parameters):

找到您的用户并执行下一个查询（使用适当的参数）：

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>';

Answer 5

回答by Diceaurora

go to services in administrative tools and select oracleserviceSID and restart it

转到管理工具中的服务并选择 oracleserviceSID 并重新启动它

Answer 6

回答by m.zhelieznov

I was trying to follow the flow described here - but haven't luck to completely kill the session.. Then I fond additional step here:
http://wyding.blogspot.com/2013/08/solution-for-ora-01940-cannot-drop-user.html

What I did:
1. select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>';- as described below.
Out put will be something like this:
alter system kill session '22,15' immediate;
2. alter system disconnect session '22,15' IMMEDIATE ;- 22-sid, 15-serial - repeat the command for each returned session from previous command
3. Repeat steps 1-2 while select...not return an empty table
4. Call drop user...

我试图按照这里描述的流程 - 但没有运气完全终止会话..然后我喜欢这里的额外步骤：
http: //wyding.blogspot.com/2013/08/solution-for-ora-01940 -cannot-drop-user.html

我做了什么：
1. select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>';- 如下所述。
输出将是这样的：
alter system kill session '22,15' immediate;
2. alter system disconnect session '22,15' IMMEDIATE ;- 22-sid, 15-serial - 对从前一个命令返回的每个会话重复该命令
3. 重复步骤 1-2select...而不返回空表
4. 调用 drop user...

What was missed - call alter system disconnect session '22,15' IMMEDIATE ;for each of session returned by select 'alter system kill session '..

错过了什么 - 调用alter system disconnect session '22,15' IMMEDIATE ;返回的每个会话select 'alter system kill session '..

Answer 7

回答by Nurlan

I had the same problem, Oracle config in default affects letter register. In exact my Scheme_Name was written all Capital letters. You can see your Scheme_Name on "Other Users" tab, if you are using Oracle S

我遇到了同样的问题，默认情况下的 Oracle 配置会影响字母寄存器。确切地说，我的 Scheme_Name 全部是大写字母。如果您使用的是 Oracle S，您可以在“其他用户”选项卡上看到您的 Scheme_Name

Answer 8

回答by Betlista

Basically I believe that killing all sessions should be the solution, but...

基本上我认为杀死所有会话应该是解决方案，但是......

I found similar discussion - https://community.oracle.com/thread/1054062to my problem and that was I had no sessions for that users, but I still received the error. I tried also second the best answer:

我发现了类似的讨论 - https://community.oracle.com/thread/1054062 解决了我的问题，那是我没有针对该用户的会话，但我仍然收到错误消息。我还尝试了第二个最佳答案：

sql>Shutdown immediate;

sql>startup restrict;

sql>drop user TEST cascade;

What worked for me at the end was to login as the user, drop all tables manually - select for creating drop statements is

最后对我有用的是以用户身份登录，手动删除所有表 - 选择创建删除语句是

select 'drop table ' || TABLE_NAME || ';'  from user_tables;

(Needs to be re-run several times because of references)

（因为引用需要重新运行几次）

I have no idea how is that related, I dropped also functions and sequences (because that was all I had in schema)

我不知道这有什么关系，我还删除了函数和序列（因为这是我在模式中的全部内容）

When I did that and I logged off, I had several sessions in v$sessiontable and when I killed those I was able to drop user.

当我这样做并注销时，我在v$session表中有几个会话，当我杀死那些会话时，我能够删除用户。

My DB was still started in restricted mode (not sure if important or not).

我的数据库仍然以受限模式启动（不确定是否重要）。

Might help someone else.

可能会帮助别人。

BTW: my Oracle version is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

顺便说一句：我的 Oracle 版本是 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Answer 9

回答by Sergiy Globa

If you use RAC then you need to use GV$*views instead V$*. Try to find your session by

如果您使用 RAC，则需要改用GV$*视图V$*。尝试通过以下方式找到您的会话

select * from gv$session where username = 'test';

and then you can kill the session by

然后你可以通过以下方式终止会话

alter system kill session 'sid, serial#, @inst_id' immediate;

Answer 10

回答by grokster

Here's how I "automate" Dropping connected users in Oracle database:

以下是我如何“自动化”删除 Oracle 数据库中的连接用户：

# A shell script to Drop a Database Schema, forcing off any Connected Sessions (for example, before an Import) 
# Warning! With great power comes great responsibility.
# It is often advisable to take an Export before Dropping a Schema

if [ "" = "" ] 
then
    echo "Which Schema?"
    read schema
else
    echo "Are you sure? (y/n)"
    read reply
    [ ! $reply = y ] && return 1
    schema=
fi

sqlplus / as sysdba <<EOF
set echo on
alter user $schema account lock;
-- Exterminate all sessions!
begin     
  for x in ( select sid, serial# from v$session where username=upper('$schema') )
  loop  
   execute immediate ( 'alter system kill session '''|| x.Sid || ',' || x.Serial# || ''' immediate' );  
  end loop;  
  dbms_lock.sleep( seconds => 2 ); -- Prevent ORA-01940: cannot drop a user that is currently connected
end;
/
drop user $schema cascade;
quit
EOF

在 Oracle 数据库中删除连接的用户

提问by Chaitanya

回答by Art

回答by Amir

回答by mahesh agrawal

回答by Roman Proshin

回答by Diceaurora

回答by m.zhelieznov

回答by Nurlan

回答by Betlista

回答by Sergiy Globa

回答by grokster

相关推荐

最近更新

标签

在 Oracle 数据库中删除连接的用户

提问by Chaitanya

回答by Art

回答by Amir

回答by mahesh agrawal

回答by Roman Proshin

回答by Diceaurora

回答by m.zhelieznov

回答by Nurlan

回答by Betlista

回答by Sergiy Globa

回答by grokster

相关推荐

oracle 表中允许的最大列数是多少？

oracle 从 shell 脚本运行 sqlplus 时管理错误处理

ORACLE 中多列的总和

ORACLE IIF 声明

相关推荐

最近更新

标签