DBMS_SERVER_ALERT in Oracle

Back to: Oracle DBA Tutorials

DBMS_SERVER_ALERT in Oracle with Examples

In this article, I will explain DBMS_SERVER_ALERT in Oracle with Examples. Please read our previous article discussing Monitoring Tablespace Usage in Oracle with Examples. We will be learning about how to set the threshold for the tablespace.

Step 1: Connect to the database

Let’s check the database name and the username that we have connected.

Command: show con_name

Step 2: Create a tablespace for the alert

First, we have to create the tablespace to set up the alert pattern. Let’s do that.

CREATE TABLESPACE tbsalert
DATAFILE '/u01/app/oracle/oradata/JAYADB/JAYAPDB/tbsalert.dbf'
SIZE 50M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

So, the tablespace is created. We have mentioned the datafile for the tablespace as tbsalert.dbf, and its size is 50M. The segment space management is set to auto, and the logging extent management is local. The size of the data file is 50M. If we add more than 50M, we will get a space error. Now, let’s check the data file details.

Statement: select * from dba_data_files where TABLESPACE_NAME=’TBSALERT’;

From the output we can see the datafile size is 52428800 bytes. Now, let’s look at the free space for the tablespace.

Statement: select * from dba_free_space where TABLESPACE_NAME=’TBSALERT’;

So, the free space available for the tablespace is 51380224 bytes. We will join the data dictionary dba_data_Files a dn dba_free_space, which provides the free bytes and the PCT_FREE available in the tablespace.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df, dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

So, the PCT_FREE is 98%. We currently have 98%. Let’s set the threshold value.

begin
DBMS_SERVER_ALERT.SET_THRESHOLD( 
metrics_id => dbms_server_alert.tablespace_pct_full,    
warning_operator =>DBMS_SERVER_ALERT.OPERATOR_GE, 
warning_value => '55', 
critical_operator =>DBMS_SERVER_ALERT.OPERATOR_GE, 
critical_value => '70', 
observation_period => 1,
consecutive_occurrences => 1, 
instance_name => 'JAYAPDB', 
object_type =>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, 
object_name => 'TBSALERT');
end;

As we already discussed the package, let’s run it and check the threshold value. The threshold value for the warning is 55, and the critical value is 70. The instance name is JAYAPDB. Let’s now execute the statement.

Let’s now check the warning value and critical value for using the dba_thresholds data dictionary.

SELECT warning_value, critical_value
FROM dba_thresholds 
WHERE object_name='TBSALERT';

The threshold value for the warning is 55%, and the critical value is 70%. These values come from this previous package. A data dictionary, dba_outstanding_alerts, shows the outstanding details.

SELECT * FROM dba_outstanding_alerts WHERE object_name=’TBSALERT’;

So, this is empty, and we don’t have any outstanding alerts.

Step 4: Create a table and add the records.

Let’s create a table and add multiple records to it to check the threshold value.

create table test100 ( emp_id number, name varchar2(100) ) tablespace TBSALERT;

So, the table TEST100 has been created. Let’s insert the 1 million records into the table and see how the threshold values react.

begin
 for i in 1..1000000
 loop
 insert into test100 values ( i, 'any dummy name' );
 end loop;
 commit;
 end;

So, we can see there are 1000000 records inserted into the database. Now, lets check the PCT_FREE in the tablespace and total bytes free.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

So, the PCT_FREE in the tablespace TBSALERT is 42. As the percent is 42, we have to wait for 10 to 15 minutes to get the update of dba_outstanding_alerts. After 15 minutes, the data dictionary will be updated, and then we can see the threshold alerts.

SELECT reason, message_type , message_level
FROM dba_outstanding_alerts
WHERE object_name='TBSALERT';

We can see why it is triggered: 58 percent is full, and the message type is in a warning state. If the DBA receives this message, he will either add a new data file or autotune the current existing data file. This is how we monitor the tablespace, and the DBA will do the rest to keep the database in a healthy state.

In the next article, I will discuss Reclaiming Wasted Space in Oracle with Examples. In this article, I explain DBMS_SERVER_ALERT in Oracle with Examples. I hope you enjoy this article on DBMS_SERVER_ALERT in Oracle.