If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must be the equivalent of DBHIGH.
ENABLE RESTRICTED SESSION
allows only users with RESTRICTED SESSION system privilege to logon to Oracle7.
DISABLE RESTRICTED SESSION
reverses the effect of the ENABLE RESTRICTED SESSION option, allowing all users with CREATE SESSION system privilege to logon to Oracle7.
FLUSH SHARED_POOL
clears all data from the shared pool in the System Global Area (SGA).
You can use the following options when your instance has the database mounted, open or closed:
CHECKPOINT
GLOBAL performs a checkpoint for all instances that have opened the database.
If you omit both the GLOBAL and LOCAL options, Oracle7 performs a global checkpoint.
CHECK DATAFILES
verifies access to online data files.
GLOBAL verifies that all instances that have opened the database can access all online data files.
LOCAL verifies that your instance can access all online data files.
If you omit both the GLOBAL and LOCAL options, Oracle7 uses GLOBAL by default.
You can only use the following parameters and options when your instance has the database open:
RESOURCE_LIMIT
controls resource limits
TRUE enables resource limits.
FALSE disables resource limits.
GLOBAL_NAMES
controls the enforcement of global naming:
TRUE enables the enforcement of global names.
FALSE disables the enforcement of global names.
SCAN_INSTANCES
in a parallel server, specify the number of instances to participate in parallelized operations.
CACHE_INSTANCES
in a parallel server, specify the number of instances that will cache a table.
For more information on parallel operations, see the "Parallel Query Option" chapter of Oracle7 Server Tuning.
MTS_SERVERS
specifies a new minimum number of shared server processes.
MTS_DISPATCHERS
protocol is the network protocol of the dispatcher processes.
integer is the new number of dispatcher processes of the specified protocol.
You can specify multiple MTS_DISPATCHERS parameters in a single command for multiple network protocols.
LICENSE_MAX_SESSIONS
limits the number of sessions on your instance. A value of 0 disables the limit.
LICENSE_SESSIONS_WARNING
establishes a threshold of sessions over which Oracle7 writes warning messages to the ALERT file for subsequent sessions. A value of 0 disables the warning threshold.
LICENSE_MAX_USERS
limits the number of concurrent users on your database. A value of 0 disables the limit.
REMOTE_DEPENDENCIES_MODE
specifies how dependencies of remote stored procedures are handled by the server. For more information, refer to "Remote Dependencies" in the Oracle7 Server Application Developer's Guide.
SWITCH LOGFILE
switches
ENABLE DISTRIBUTED RECOVERY
enables
distributed recovery. In a single-process environment, you must use this option to initiate distributed recovery.
DISABLE DISTRIBUTED RECOVERY
disables distributed recovery.
ARCHIVE LOG
KILL SESSION
terminates a session. You must identify the session with both of the following values from the V$SESSION view:
integer1 is the value of the SID column.
integer2 is the value of the SERIAL# column.
You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on. To restrict logons, issue the following statement:
ALTER SYSTEM
ENABLE RESTRICTED SESSION
You can then terminate any existing sessions using the KILL SESSION clause of the ALTER SYSTEM command.
After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION system privilege to log on:
ALTER SYSTEM
DISABLE RESTRICTED SESSION
ALTER SYSTEM FLUSH SHARED_POOL
The above statement does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages, or triggers that are currently being executed or for SQL SELECT statements for which all rows have not yet been fetched.
The following statement forces a checkpoint:
ALTER SYSTEM CHECKPOINT
Oracle7 does not return control to you until the checkpoint is complete.
The following statement verifies that all instances that have opened the database can access all online data files:
ALTER SYSTEM
CHECK DATAFILES GLOBAL
Enabling resource limits only causes Oracle7 to enforce the resource limits assigned to users. To choose resource limit values for a user, you must create a profile, or a set of limits, and assign that profile to the user. For more information on this process, see the CREATE PROFILE command ![[*]](jump.gif)
and the CREATE USER command .
This ALTER SYSTEM statement dynamically enables resource limits:
ALTER SYSTEM
SET RESOURCE_LIMIT = TRUE
It is recommended that you enable global name resolution. For more information on global name resolution and how Oracle7 enforces it, see section "Referring to Objects in Remote Databases" and Oracle7 Server Distributed Systems, Volume I.
MTS_SERVERS
This parameter specifies the initial and minimum number of shared server processes. Oracle7 may automatically change the number of shared server processes if the load on the existing processes changes. While your instance is running, the number of shared server processes can vary between the values of the initialization parameters MTS_SERVERS and MTS_MAX_SERVERS.
MTS_DISPATCHERS
For more information on the multi-threaded server architecture, see Oracle7 Server Concepts.
You can subsequently use the MTS_SERVERS and MTS_DISPATCHERS parameters of the ALTER SYSTEM command to perform one of the following operations while the instance is running:
To create additional shared server processes:
You can cause Oracle7 to create additional shared server processes by increasing the minimum number of shared server processes.
To terminate existing shared server processes:
Oracle7 terminates the shared server processes after finishing processing their current calls,unless the load on the server processes is so high that it cannot be managed by the remaining processes.
To create more dispatcher processes for a specific protocol:
You can create additional dispatcher processes up to a maximum across all protocols specified by the initialization parameter MTS_MAX_DISPATCHERS.
You cannot use this command to create dispatcher processes for network protocols that are not specified by the initialization parameter MTS_DISPATCHERS. To create dispatcher processes for a new protocol, you must change the value of the initialization parameter.
To terminate existing dispatcher processes for a specific protocol:
Oracle7 terminates the dispatcher processes only after their current user processes disconnect from the instance.
Example I
The following statement changes the minimum number of shared server processes to 25:
ALTER SYSTEM
SET MTS_SERVERS = 25
If there are currently fewer than 25 shared server processes, Oracle7 creates more. If there are currently more than 25, Oracle7 terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.
Example II
The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the DECNET protocol to 10:
ALTER SYSTEM
SET MTS_DISPATCHERS = 'TCP, 5'
MTS_DISPATCHERS = 'DECnet, 10'
If there are currently fewer than 5 dispatcher processes for TCP, Oracle7 creates new ones. If there are currently more than 5, Oracle7 terminates some of them after the connected users disconnect.
If there are currently fewer than 10 dispatcher processes for DECnet, Oracle7 creates new ones. If there are currently more than 10, Oracle7 terminates some of them after the connected users disconnect.
If there are currently existing dispatchers for another protocol, the above statement does not affect the number of dispatchers for this protocol.
LICENSE_MAX_SESSIONS
This parameter establishes the concurrent usage licensing limit, or the limit for concurrent sessions. Once this limit is reached, only users with RESTRICTED SESSION system privilege can connect.
LICENSE_SESSIONS_WARNING
This parameter establishes a warning threshold for concurrent usage. Once this threshold is reached, Oracle7 writes a warning message to the database ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.
LICENSE_MAX_USERS
This parameter establishes the limit for users connected to your database. Once this limit for users is reached, more users cannot connect.
You can subsequently use the LICENSE_MAX_SESSIONS, LICENSE_SESSIONS_WARNING, and LICENSE_MAX_USERS parameters of the ALTER SYSTEM command to dynamically change or disable limits or thresholds while your instance is running. Do not disable or raise session or user limits unless you have appropriately upgraded your Oracle7 license. For information on upgrading your license, contact your Oracle sales representative.
New limits apply only to future sessions and users:
The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:
ALTER SYSTEM
SET LICENSE_MAX_SESSIONS = 64
LICENSE_SESSIONS_WARNING = 54
If the number of sessions reaches 54, Oracle7 writes a warning message to the ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.
If the number of sessions reaches 64, only users with RESTRICTED SESSION system privilege can begin new sessions until the number of sessions falls below 64 again.
Example IV
The following statement dynamically disables the limit for sessions on your instance:
ALTER SYSTEM
SET LICENSE_MAX_SESSIONS = 0
After you issue the above statement, Oracle7 no longer limits the number of sessions on your instance.
Example V
The following statement dynamically changes the limit on the number of users in the database to 200:
ALTER SYSTEM
SET LICENSE_MAX_USERS = 200
After you issue the above statement, Oracle7 prevents the number of users in the database from exceeding 200.
The following statement forces a log switch:
ALTER SYSTEM
SWITCH LOGFILE
If you are using Oracle7 in multiple-process mode, this distributed recovery is performed automatically. If you are using Oracle7 in single-process (single user) mode, such as on the MS-DOS operating system, you must explicitly initiate distributed recovery with the following statement.
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY
You may need to issue the above statement more than once to recover an in-doubt transaction, especially if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view DBA_2PC_PENDING. You can tell that the transaction is recovered when it no longer appears in DBA_2PC_PENDING. For more information about distributed transactions and distributed recovery, see Oracle7 Server Distributed Systems, Volume I.
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY
You may want to disable distributed recovery for demonstration purposes. You can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.
If you try to kill a session that is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, Oracle7 waits for this activity to complete, kills the session, and then returns control to you. If the waiting lasts as long as a minute, Oracle7 marks the session to be killed and returns control to you with a message indicating that the session is marked to be killed. Oracle7 then kills the session when the activity is complete.
Example VI
Consider this data from the V$SESSION dynamic performance table:
SELECT sid, serial#, username
FROM v$session
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 1
2 1
3 1
4 1
5 1
7 1
8 28 OPS$BQUIGLEY
10 211 OPS$SWIFT
11 39 OPS$OBRIEN
12 13 SYSTEM
13 8 SCOTT The following statement kills the session of the user SCOTT using the SID and SERIAL# values from V$SESSION:
ALTER SYSTEM KILL SESSION '13, 8'
CREATE PROFILE command
CREATE USER command