Search code examples
sqloraclesysdba

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

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.

Solution

  • 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.