Oracle tablespace (LOB_TABLESPACE, USERS, etc.) for DLP is full, almost full, or critically full

Products

Data Loss Prevention Enforce Data Loss Prevention

Issue/Introduction

You see one of the following in Data Loss Prevention (DLP) which indicates that the Oracle tablespace (LOB_TABLESPACE, USERS, etc.) is either full or almost full.

A system alert warning that a tablespace in Oracle is almost full (>80%) or critically full (>90%).
“Corrupt incident” alerts because a tablespace is completely full and can no longer store incident data.

Example:

Code: 2301 Summary Tablespace is almost full Detail Oracle tablespace LOB_TABLESPACE is over 83% full

Resolution

Overview

You need to extend one or more tablespaces in the Oracle database for DLP.

Caution: Pay close attention to which tablespace names the logs or alerts identify for an extension. This issue usually affects LOB_TABLESPACE, but it can be the USERS tablespace or other tablespaces. You extend all tablespaces the same way; substitute the appropriate tablespace name.

Tablespace summary

In DLP 14.x and later, you can view the “Tablespace Summary” in the DLP Enforce console.

Note: This summary does not fully describe the tablespace, such as the location of the database files.

Locate the Oracle database files

Before you can extend the tablespace, you need to know where the current Oracle database files are located.

To locate the database files, query the database directly using SQL*Plus, which is available on the DLP Enforce server. To learn how to use SQL*Plus, see the Implementing the Database or Starting SQL*Plus and Connecting to the Database.

Note: You can also use Oracle Enterprise Manager to locate the database files.

Use the following command logged in as protect:

select file_name from sys.dba_data_files;

The resulting output is like the following, which indicates each database file's directory and file name (.DBF extension):

E:\ORACLE\ORADATA\PROTECT\SYSTEM01.DBF
E:\ORACLE\ORADATA\PROTECT\SYSAUX01.DBF
E:\ORACLE\ORADATA\PROTECT\UNDOTBS.DBF
E:\ORACLE\ORADATA\PROTECT\DRSYS01.DBF
E:\ORACLE\ORADATA\PROTECT\LOB01.DBF
E:\ORACLE\ORADATA\PROTECT\LOB02.DBF
E:\ORACLE\ORADATA\PROTECT\LOB03.DBF
E:\ORACLE\ORADATA\PROTECT\USERS01.DBF
E:\ORACLE\ORADATA\PROTECT\USERS02.DBF
E:\ORACLE\ORADATA\PROTECT\USERS03.DBF

Verify Free Space

If necessary, to verify which tablespace is full, log in using sys as sysdba and run the following command to see the free space:

set pages 100
set lines 100
SELECT d.status "Status",
d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99999990D900') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
order by "Free %";

Extend the tablespace

To add tablespace through SQL, log in—connecting to the database using sys as sysdba—and enter the following:

ALTER TABLESPACE <tablespace name>
ADD 
DATAFILE '<directory and datafile name>' SIZE 138240K
REUSE AUTOEXTEND 
ON NEXT 10240K MAXSIZE 32767M;

Based on the sample output under "Locate the Oracle database files", if it is “LOB_TABLESPACE” which is full, the newly created database file is named "LOB04.DBF."

Here is a specific example that adds a new database file to the “LOB_TABLESPACE”:

ALTER TABLESPACE LOB_TABLESPACE
ADD
DATAFILE 'D:\ORACLE\ORADATA\PROTECT\LOB04.DBF' SIZE 138240K
REUSE AUTOEXTEND
ON NEXT 10240K MAXSIZE 32767M;

Additional Information

On some systems using Oracle 11g, you may need to omit the quotes surrounding the tablespace name (e.g., ALTER TABLESPACE LOB_TABLESPACE ADD DATAFILE...).
You can gain tablespace by deleting incidents, as Oracle overwrites the deleted data when new incidents come in. However, deleting incidents will not work if your tablespace is already full. For more information, see Why doesn't the database shrink after incidents are deleted?