oracle中用SQL语句删除已连接的用户

1.查看v$session 结构

  1 SQL> desc v$session;
  2 Name                          Type         Nullable Default Comments 
  3 ----------------------------- ------------ -------- ------- -------- 
  4 SADDR                         RAW(4)       Y                         
  5 SID                           NUMBER       Y                         
  6 SERIAL#                       NUMBER       Y                         
  7 AUDSID                        NUMBER       Y                         
  8 PADDR                         RAW(4)       Y                         
  9 USER#                         NUMBER       Y                         
 10 USERNAME                      VARCHAR2(30) Y                         
 11 COMMAND                       NUMBER       Y                         
 12 OWNERID                       NUMBER       Y                         
 13 TADDR                         VARCHAR2(8)  Y                         
 14 LOCKWAIT                      VARCHAR2(8)  Y                         
 15 STATUS                        VARCHAR2(8)  Y                         
 16 SERVER                        VARCHAR2(9)  Y                         
 17 SCHEMA#                       NUMBER       Y                         
 18 SCHEMANAME                    VARCHAR2(30) Y                         
 19 OSUSER                        VARCHAR2(30) Y                         
 20 PROCESS                       VARCHAR2(24) Y                         
 21 MACHINE                       VARCHAR2(64) Y                         
 22 PORT                          NUMBER       Y                         
 23 TERMINAL                      VARCHAR2(16) Y                         
 24 PROGRAM                       VARCHAR2(64) Y                         
 25 TYPE                          VARCHAR2(10) Y                         
 26 SQL_ADDRESS                   RAW(4)       Y                         
 27 SQL_HASH_VALUE                NUMBER       Y                         
 28 SQL_ID                        VARCHAR2(13) Y                         
 29 SQL_CHILD_NUMBER              NUMBER       Y                         
 30 SQL_EXEC_START                DATE         Y                         
 31 SQL_EXEC_ID                   NUMBER       Y                         
 32 PREV_SQL_ADDR                 RAW(4)       Y                         
 33 PREV_HASH_VALUE               NUMBER       Y                         
 34 PREV_SQL_ID                   VARCHAR2(13) Y                         
 35 PREV_CHILD_NUMBER             NUMBER       Y                         
 36 PREV_EXEC_START               DATE         Y                         
 37 PREV_EXEC_ID                  NUMBER       Y                         
 38 PLSQL_ENTRY_OBJECT_ID         NUMBER       Y                         
 39 PLSQL_ENTRY_SUBPROGRAM_ID     NUMBER       Y                         
 40 PLSQL_OBJECT_ID               NUMBER       Y                         
 41 PLSQL_SUBPROGRAM_ID           NUMBER       Y                         
 42 MODULE                        VARCHAR2(48) Y                         
 43 MODULE_HASH                   NUMBER       Y                         
 44 ACTION                        VARCHAR2(32) Y                         
 45 ACTION_HASH                   NUMBER       Y                         
 46 CLIENT_INFO                   VARCHAR2(64) Y                         
 47 FIXED_TABLE_SEQUENCE          NUMBER       Y                         
 48 ROW_WAIT_OBJ#                 NUMBER       Y                         
 49 ROW_WAIT_FILE#                NUMBER       Y                         
 50 ROW_WAIT_BLOCK#               NUMBER       Y                         
 51 ROW_WAIT_ROW#                 NUMBER       Y                         
 52 TOP_LEVEL_CALL#               NUMBER       Y                         
 53 LOGON_TIME                    DATE         Y                         
 54 LAST_CALL_ET                  NUMBER       Y                         
 55 PDML_ENABLED                  VARCHAR2(3)  Y                         
 56 FAILOVER_TYPE                 VARCHAR2(13) Y                         
 57 FAILOVER_METHOD               VARCHAR2(10) Y                         
 58 FAILED_OVER                   VARCHAR2(3)  Y                         
 59 RESOURCE_CONSUMER_GROUP       VARCHAR2(32) Y                         
 60 PDML_STATUS                   VARCHAR2(8)  Y                         
 61 PDDL_STATUS                   VARCHAR2(8)  Y                         
 62 PQ_STATUS                     VARCHAR2(8)  Y                         
 63 CURRENT_QUEUE_DURATION        NUMBER       Y                         
 64 CLIENT_IDENTIFIER             VARCHAR2(64) Y                         
 65 BLOCKING_SESSION_STATUS       VARCHAR2(11) Y                         
 66 BLOCKING_INSTANCE             NUMBER       Y                         
 67 BLOCKING_SESSION              NUMBER       Y                         
 68 FINAL_BLOCKING_SESSION_STATUS VARCHAR2(11) Y                         
 69 FINAL_BLOCKING_INSTANCE       NUMBER       Y                         
 70 FINAL_BLOCKING_SESSION        NUMBER       Y                         
 71 SEQ#                          NUMBER       Y                         
 72 EVENT#                        NUMBER       Y                         
 73 EVENT                         VARCHAR2(64) Y                         
 74 P1TEXT                        VARCHAR2(64) Y                         
 75 P1                            NUMBER       Y                         
 76 P1RAW                         RAW(8)       Y                         
 77 P2TEXT                        VARCHAR2(64) Y                         
 78 P2                            NUMBER       Y                         
 79 P2RAW                         RAW(8)       Y                         
 80 P3TEXT                        VARCHAR2(64) Y                         
 81 P3                            NUMBER       Y                         
 82 P3RAW                         RAW(8)       Y                         
 83 WAIT_CLASS_ID                 NUMBER       Y                         
 84 WAIT_CLASS#                   NUMBER       Y                         
 85 WAIT_CLASS                    VARCHAR2(64) Y                         
 86 WAIT_TIME                     NUMBER       Y                         
 87 SECONDS_IN_WAIT               NUMBER       Y                         
 88 STATE                         VARCHAR2(19) Y                         
 89 WAIT_TIME_MICRO               NUMBER       Y                         
 90 TIME_REMAINING_MICRO          NUMBER       Y                         
 91 TIME_SINCE_LAST_WAIT_MICRO    NUMBER       Y                         
 92 SERVICE_NAME                  VARCHAR2(64) Y                         
 93 SQL_TRACE                     VARCHAR2(8)  Y                         
 94 SQL_TRACE_WAITS               VARCHAR2(5)  Y                         
 95 SQL_TRACE_BINDS               VARCHAR2(5)  Y                         
 96 SQL_TRACE_PLAN_STATS          VARCHAR2(10) Y                         
 97 SESSION_EDITION_ID            NUMBER       Y                         
 98 CREATOR_ADDR                  RAW(4)       Y                         
 99 CREATOR_SERIAL#               NUMBER       Y                         
100 ECID                          VARCHAR2(64) Y

2.检索出需要的列

 1 SQL> select username,sid,serial# from v$session;
 2  
 3 USERNAME                              SID    SERIAL#
 4 ------------------------------ ---------- ----------
 5                                         1          1
 6                                         2          1
 7                                         3          1
 8                                         4          1
 9                                         6       1431
10                                         7         19
11                                         9       1346
12                                        63         11
13                                        64          1
14                                        65          1
15                                        66          1
16                                        68       1654
17 SCOTT                                  70       1611
18                                       125          1
19                                       126          1
20                                       127          1
21                                       128          1
22 ZZZ                                   131        596
23 SYSTEM                                132       1949
24 SYSTEM                                133       2358
25  
26 USERNAME                              SID    SERIAL#
27 ------------------------------ ---------- ----------
28                                       187          1
29                                       188          1
30                                       189          1
31                                       190          1
32                                       193          4
33                                       194          1
34                                       195          3
35  
36 27 rows selected

3.找出要删除的用"ZZZ"对应的SID,SERIAL#.结束回话!

1 SQL> alter system kill session '131,596';
2  
3 System altered

4.删除用户.

1 SQL> drop user zzz cascade;
2  
3 User dropped

 

 

 

 

posted @ 2013-08-28 11:04  nbyue  阅读(449)  评论(0)    收藏  举报