In my oracle database, one tablespace gives out of space alert frequently even am adding space, How to know the cause?

Question

In my oracle database, one tablespace gives 'out of space' alert frequently even am adding space, How to know the cause?

Intially I created tablespace with size 1GB, now it's size 4GB.

How to know the reason, when am querying for used object for that perticular tablespace form dba_segments.. it is giving only segment names.

Answer 1

You could use dba_segments to find what is taking up the space:

select tablespace_name, sum(bytes)/1024/1024 MB from dba_segments group by tablespace_name order by tablespace_name

This lists the tablespaces and their usage.

select owner, sum(bytes)/1024/1024 mb
from dba_segments
where tablespace_name = 'SYSAUX'
group by owner
order by sum(bytes)/1024/1024 desc, owner

This lists how much space a user id taking in your tablespace. Normally alerts fire for a reason so ignoring them might not be the smartest thing to do.

You could of course zoom in to the objects of a particular user and repeat this over time to find which object is growing. I leave that as an exercise for the reader.

Answer 2

I think the problem that you describe here is the Tablespace Space Threshold. You can get your current threshold settings via (also have a look at the documentation):

SELECT * FROM dba_thresholds
WHERE object_type = 'TABLESPACE' AND object_name = <Your tablespace>;

In my case that gives me:

METRICS_NAME:             Tablespace Space usage
WARNING_OPERATOR:         GE
WARNING_VALUE:            85
CRITICAL_OPERATOR:        GE
CRITICAL_VALUE:           97
OBSERVATION_PERIOD:        1
CONSECUTIVE_OCCURRENCES:   1
INSTANCE_NAME:            database_wide
OBJECT_TYPE:              TABLESPACE
OBJECT_NAME:              null
STATUS:                   VALID

You can modify your thresholds or deactivate them altogether via the DBMS_SERVER_ALERT.SET_THRESHOLD method. For example, to deactivate the threshold on my system I use:

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
   warning_value           => '0',
   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
   critical_value          => '0',
   observation_period      => 1,
   consecutive_occurrences => 1,
   instance_name           => NULL,
   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
   object_name             => NULL);
END;
/

By querying DBA_THRESHOLDS again I now have:

METRICS_NAME:             Tablespace Space usage
WARNING_OPERATOR:         DO NOT CHECK
WARNING_VALUE:             0
CRITICAL_OPERATOR:        DO_NOT_CHECK
CRITICAL_VALUE:            0
OBSERVATION_PERIOD:        1
CONSECUTIVE_OCCURRENCES:   1
INSTANCE_NAME:            database_wide
OBJECT_TYPE:              TABLESPACE
OBJECT_NAME:              null
STATUS:                   VALID