Query and delete sessions in Oracle

Query and delete sessions in Oracle

Query and delete sessions in Oracle

1. query the current session

SQL> select username, sid, serial # from v $ session where username is not null;


Username sid serial #
--------------------------------------------------
SYS 144 4
HYL 146 48
SCOTT 147 64
HR 159 15
-- SERIAL #: the SID may be repeated. When the SID of two sessions is repeated, SERIAL # is used to differentiate sessions.

2. Delete the current session

SQL> alter system kill session '2017, 48 ';
System altered.


Perform the following operations in a hyl session:


SQL> show user
USER is "HYL"
SQL> select * from test1;
Select * from test1
*
ERROR at line 1:
ORA-00028: your session has been killed

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

3. Delete the user of the current session

Idea: Close the session first, and then delete the user


Demo: delete a user without closing the session:
SQL> select username, account_status from dba_users;
-- View the current users
USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
CSMIG OPEN
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
HYL OPEN
Oe expired & LOCKED
6 rows selected.


SQL> select username, sid, serial # from v $ session where username is not null;
-- View the session sid and serial # in the v $ session view #
Username sid serial #
--------------------------------------------------
SYS 144 4
HYL 146 54
SCOTT 147 64
HR 159 15


SQL> drop user hyl; -- delete: an error is returned, indicating that the user in the session cannot be deleted. You need to disable it first.
Drop user hyl
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL> alter system kill session '2014, 54 ';
System altered.

 

-- View data under the hyl session. The information shows that the session has been killed, indicating that the session has been deleted successfully.
SQL> select * from test1;
Select * from test1
*
ERROR at line 1:
ORA-00028: your session has been killed


-- Delete the user
SQL> drop user hyl; -- an error is returned when the user is deleted. An object exists in the hyl user.
Drop user hyl
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'hybrid'


SQL> drop user hyl cascade; -- delete all users and all objects
User dropped.


-- Hyl connection attempt. An error is reported, and hyl cannot be used to log on to the session.
SQL> conn hyl/oracle
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.


-- View the dba_users data dictionary, indicating that the hyl user has been deleted.
SQL> select username, account_status from dba_users;


USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
CSMIG OPEN
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
Oe expired & LOCKED
5 rows selected.


Summary:

 

Query the current session: select username, sid, serial # from v $ session where username is not null;
Delete the current session: alter system kill session 'sid, serial #';
Delete the user of the current session, kill the session first, and then drop the user (if the user has an object, use the cascade command)

For more details, please continue to read the highlights on the next page:

1 2 Next Page