4 Managing Oracle Database Processes

ORA-00028: your session has been killed

ORA-01012: not logged on

An active session cannot be interru pted when it is performing network I/O or rolling back a transaction. Such a session cannot be terminated until the operation complet es. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTE M statement to terminate a session waits up to 60 seconds for the session to be terminated. If the operation that cannot be in terrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the ses sion 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

< a id="sthref638" name="sthref638">If the session is not making a SQL call to Oracle Database (is INACTIVE) when it i s 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.< /a>

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 sel
ected.

ALTER SYSTEM KILL SESSION '7,15';
Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER
   FROM V$SESSION
   WHERE USERNA
ME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  KILLED     PSEUDO
   12
   63  INACTIVE   DEDICATED
2 rows selected.

Server-Generated Alerts

A server-generated alert is a notification from the Oracle Database server of an impending problem. The notification may contain suggestions for correcting the problem. Notifications are also provided when the problem condition has been cleared.

Alerts are automatically generated when a problem occurs or when data does not ma tch expected values for metrics, such as the following:

• Physical Reads Per Sec

• User Commits Per Sec

• SQL Service Response Time

Server-generated alerts can be based on threshold levels or can issue simply because an event has occurred. Threshold-based alerts can be triggered at both threshold war ning and critical levels. The value of these levels can be customer-defined or internal values, and some alerts have default threshol d levels which you can change if appropriate. For example, by default a server-generated alert is generated for tablespace space usag e when the percentage of space usage exceeds either the 85% warning or 97% critical threshold level. Examples of alerts not based on threshold levels are:

• Snapshot Too Old

• Resumable Session Suspended

• Recovery Area Space Usage

An alert message is sent to th e predefined persistent queue ALERT_QUE owned by the user SYS. Oracle Enterprise Manager reads this queue a nd provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem. The alerts are displayed on the Enterprise Manager console and can be configured to send email or pager notifications to selected administrators. If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.

Backgro und processes periodically flush the data to the Automatic Workload Repository to capture a history of metric values. The alert histo ry table and ALERT_QUE are purged automatically by the system at regular intervals.

The most convenient way to set and view threshold values is to use Enterprise Manager. To manage threshold-based alerts thr ough Enterprise Manager:

• On the Database Home page, click on the Manage Metrics link at the bottom of the page to display the Thresholds page.

• On the Thresholds page, you can edit the threshold values.

DBMS_SERVER_ALERT.SET_THRESH
OLD(
 DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000', 
 DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'in
st1',
 DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.oracle.com');

DECLARE
 warning_operator         BINARY_INTEGER;
 warning_value
        VARCHAR2(60);
 critical_operator        BINARY_INTEGER; 
 critical_value           VARCHAR2(60);
 observation_period       BI
NARY_INTEGER;
 consecutive_occurrences  BINARY_INTEGER;
BEGIN
 DBMS_SERVER_ALERT.GET_THRESHOLD(
 DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
 warning_operator, warning_value,
    critical_operator, critical_value, observation_period, 
    consecutive_occurrences, 'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.oracle.com');
 DBMS_OUTPUT.PUT_LINE('Warning operator:       ' || w
arning_operator);
 DBMS_OUTPUT.PUT_LINE('Warning value:          ' || warning_value);
 DBMS_OUTPUT.PUT_LINE('Critical operator:
' || critical_operator);
 DBMS_OUTPUT.PUT_LINE('Critical value:         ' || critical_value);     
 DBMS_OUTPUT.PUT_LINE('Observation
_period:     ' || observation_period);
 DBMS_OUTPUT.PUT_LINE('Consecutive occurrences:' || consecutive_occurrences);
END;
/ 

SELECT metrics_name, warning_value, critical_value, consecutive_occurrences 
   FROM DBA_THRESHOLDS 
   WHERE metrics_name LIKE '%
CPU Time%';