[go: up one dir, main page]
Scribd
Upload
582 views4 pages

Restricted Session: - This Privilege Allows You To Log in Even If The Database Has Been Opened in Restricted Mode

The document discusses restricting user access and terminating user sessions in an Oracle database. It describes how to enable and disable restricted sessions, which blocks new non-DBA users from logging in. It also explains how to identify and terminate active and inactive user sessions using the ALTER SYSTEM KILL SESSION statement.

Uploaded by

Khan Bahi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
582 views4 pages

Restricted Session: - This Privilege Allows You To Log in Even If The Database Has Been Opened in Restricted Mode

The document discusses restricting user access and terminating user sessions in an Oracle database. It describes how to enable and disable restricted sessions, which blocks new non-DBA users from logging in. It also explains how to identify and terminate active and inactive user sessions using the ALTER SYSTEM KILL SESSION statement.

Uploaded by

Khan Bahi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

RESTRICTED SESSION

• This privilege allows you to log in even if the database has been opened in
restricted mode.

• SQL> select logins from v$instance;


• LOGINS
• ----------
• ALLOWED
•
• SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
• System altered.
•
• SQL> select logins from v$instance;
• LOGINS
• ----------
• RESTRICTED
•
• $ sqlplus <username>/<password>@<serviceid>
• SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 24 09:26:48 2011
• Copyright (c) 1982, 2005, Oracle.  All rights reserved.
• ERROR:
• ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
•
• SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
• System altered.

‘alter system enable restricted session ‘ does not “kick out” user automatically. It
just blocks new user who does not have the ‘restrict session’ privilege to login, similar to ‘ALTER
SYSTEM QUIESCE RESTRICTED’, which blocks new non-dba users. However, I can scarely see the
instances when non-dba users have been granted to the ‘restrict session’ privilege.

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is
currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is
currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to
become active. For example, If a user issues a SQL query in an attempt to force an inactive session to
become active, the query will appear to be hung. When the database is later unquiesced, the session
is resumed, and the blocked action is processed.

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this
statement is not required to be issued from the same session, or even the same instance, as that
which quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCEstatement
terminates abnormally, then the Oracle Database server ensures that the unquiesce operation
completes.
The ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The
column values has one of these values:
 NORMAL: Normal unquiesced state.
 QUIESCING: Being quiesced, but some non-DBA sessions are still active.
 QUIESCED: Quiesced; no non-DBA sessions are active or allowed.
Terminating Sessions
Sometimes it is necessary to terminate current user sessions. For example, you might want to
perform an administrative operation and need to terminate all non-administrative sessions. This
section describes the various aspects of terminating sessions, and contains the following topics:

 Identifying Which Session to Terminate


 Terminating an Active Session
 Terminating an Inactive Session

When a session is terminated, any active transactions of the session are rolled back, and resources
held by the session (such as locks and memory areas) are immediately released and available to
other sessions.

You terminate a current session using the SQL statement ALTER SYSTEM KILL SESSION. The
following statement terminates the session whose system identifier is 7 and serial number is 15:

ALTER SYSTEM KILL SESSION '7,15';

Identifying Which Session to Terminate


To identify which session to terminate, specify the session index number and serial number. To
identify the system identifier (SID) and serial number of a session, query the V$SESSION dynamic
performance view. For example, the following query identifies all sessions for the user jward:

SELECT SID, SERIAL#, STATUS

FROM V$SESSION

WHERE USERNAME = 'JWARD';

SID SERIAL# STATUS

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

7 15 ACTIVE

12 63 INACTIVE

A session is ACTIVE when it is making a SQL call to Oracle Database. A session is INACTIVE if it is


not making a SQL call to the database.

Terminating an Active Session


If a user session is processing a transaction (ACTIVE status) when you terminate the session, the transaction is
rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the
database, Oracle Database returns the following message:

ORA-01012: not logged on

An active session cannot be interrupted when it is performing network I/O or rolling back a transaction. Such a
session cannot be terminated until the operation completes. In this case, the session holds all resources until it
is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits
up to 60 seconds for the session to be terminated. If the operation that cannot be interrupted continues past one
minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been
marked to be terminated. A session marked to be terminated is indicated in V$SESSION with a status
of KILLED and a server that is something other than PSEUDO.

Terminating an Inactive Session


If the session is not making a SQL call to Oracle Database (is INACTIVE) when it is terminated, the ORA-
00028 message is not returned immediately. The message is not returned until the user subsequently attempts
to use the terminated session.

When an inactive session has been terminated, the STATUS of the session in the V$SESSION view is KILLED.
The row for the terminated session is removed from V$SESSION after the user attempts to use the session
again and receives the ORA-00028 message.

In the following example, an inactive session is terminated. First, V$SESSION is queried to identify


the SID and SERIAL# of the session, and then the session is terminated.

SELECT SID,SERIAL#,STATUS,SERVER

FROM V$SESSION

WHERE USERNAME = 'JWARD';

SID SERIAL# STATUS SERVER

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

7 15 INACTIVE DEDICATED

12 63 INACTIVE DEDICATED

2 rows selected.
ALTER SYSTEM KILL SESSION '7,15';

Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER

FROM V$SESSION

WHERE USERNAME = 'JWARD';

SID SERIAL# STATUS SERVER

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

7 15 KILLED PSEUDO

12 63 INACTIVE DEDICATED

2 rows selected.

You might also like