ALTER SYSTEM ENABLE RESTRICTED SESSION;ALTER SYSTEM ENABLE RESTRICTED SESSION;

Managing Sessions

ALTER SYSTEM ENABLE RESTRICTED SESSION;ALTER SYSTEM ENABLE RESTRICTED SESSION;

{ENABLE|DISABLE}RESTRICTED SESSION

where:

ENABLE RESTRICTED enables future logins only for users

SESSION who have the RESTRICTED

SESSION privilege

DISABLE RESTRICTED disables RESTRICTED SESSION so that SESSION users who do not have the privilege can log on

Enable and Disable Restricted Session

• Use the STARTUP command to restrict access to a database:

• Use the ALTER SYSTEM command to place an instance in restricted mode:

STARTUP RESTRICT STARTUP RESTRICT

ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM ENABLE RESTRICTED SESSION;

Restricted Session (continued)

Note: The ALTER SYSTEM command does not disconnect current sessions but

allows future connections only to users with the RESTRICTED SESSION privilege. The V$INSTANCE dynamic performance view contains information about the restricted mode.

SQL> SELECT logins FROM v$instance; LOGINS

--- RESTRICTED 1 row selected.

...Managing Sessions

How to Terminate Sessions

After placing an instance in restricted mode, you may want to kill all current user sessions before performing administrative tasks.

ALTER SYSTEM KILL SESSION ’integer1,integer2’

where: KILL SESSION Identifies the session with both of the following values from the V$SESSION view: integer1: value of the SID column

integer2: value of the SERIAL# column

Note: The session ID and serial number are used to uniquely identify a session. This

guarantees that the ALTER SYSTEM command is applied to the correct session even if the user logs off and a new session uses the same session ID.

Effects of Terminating a Session

The ALTER SYSTEM KILL SESSION command causes the background process PMON to perform the following steps upon execution:

• Roll back the user’s current transaction • Release all currently held table or row locks • Free all resources currently reserved by the user

Terminating Sessions

SELECT sid, serial# FROM v$session WHERE username=‘SCOTT’;

ALTER SYSTEM KILL SESSION ‘7,15’; ALTER SYSTEM KILL SESSION ‘7,15’;

• Identify which session to terminate with the V$SESSION dynamic performance view:

Effects of Terminating a Session (continued)

You query the V$SESSION view to identify the session ID and serial number of user sessions.

Terminating an Active Session If a user session is making an SQL call to the Oracle server—that is, the session is ACTIVE—when it is terminated, the transaction is rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If the user session is performing some activity that must be completed and cannot be interrupted, the Oracle server waits for this activity to finish.

Terminating an Inactive Session If the session is inactive when it is terminated, the ORA-00028 message is not returned immediately, but the STATUS column in the V$SESSION view is marked killed.

When the user attempts to use the terminated session again, the ORA-00028 message is returned and the row for the terminated session is removed from V$SESSION.

Note: When a session is terminated, the Oracle server does not kill the operating

system processes.

However, the following command, normally used in a parallel server environment, disconnects a session when its current transaction is finished and terminates the server process:

ALTER SYSTEM DISCONNECT SESSION ’integer1, integer2’ POST_TRANSACTION

...Managing Sessions

The ALERT File and the Trace Files

If an error occurs while your Oracle instance is running, the messages are written to the ALERT file. During startup of the database, if the ALERT file does not exist, Oracle creates one.

The ALERT file of a database is a chronological log of messages and errors. Oracle uses the ALERT file as an alternative to displaying such information.

If an error is detected by a background process, the information is dumped into a trace file.

Trace files can also be generated by server processes at user request.

Tracing can be enabled or disabled by the initialization parameter SQL_TRACE; the value is True or False.

The following statement enables writing to a trace file for a particular session:

SQL>ALTER SESSION SET sql_trace=TRUE;

The ALERT File and the Trace Files

• Trace files can be written by server and background processes.

• The Oracle server dumps information about errors in trace files.

• The ALERT file consists of a chronological log of messages and errors.

• Server process tracing can be enabled or disabled by:

– An ALTER SESSION command – The parameter SQL_TRACE

Location of the ALERT File and the Trace Files

The following parameters control the location and size of the ALERT file and the trace files:

Note

• The MAX_DUMP_FILE_SIZE and USER_DUMP_DEST parameters are dynamic initialization parameters.

• On UNIX, the ALERT file is named alert_SID.log and is located in the

Initialization Parameter Description

BACKGROUND_DUMP_DEST Defines the location of the background trace file and ALERT file

USER_DUMP_DEST Defines where trace files will be created at the request of the users

MAX_DUMP_FILE_SIZE Specified in O/S blocks; limits the size of user trace files, not the ALERT file or background trace files

Instance SGA

Shared pool Controlling the ALERT File

and the Trace Files

USER_DUMP_DEST BACKGROUND_DUMP_DEST

In document Enterprise DBA Part 1A: Architecture and Administration (Page 123-128)