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