I found that my database, specifically the USERS tablespace, dramatically increased in size.

How can I find what DML operations are performing on the objects located in this tablespace?

  • Do you have diagnostic pack licenses?
    – Philᵀᴹ
    May 11, 2012 at 8:37
  • Have you identified the "guilty" objects yet?
    – Iľja
    May 11, 2012 at 9:06
  • 1
    @ipip Haven't yet. Now I am analyzing archived log files using Log Miner.
    – kupa
    May 11, 2012 at 9:15
  • Using logminer is a tedious job but should provide you with the correct insight.
    – user953
    May 11, 2012 at 9:20

2 Answers 2


The query below will show you an overview of the schemas, tablespaces and objects in your database and their respective sizes.

- Change the group by to decrease the level of detail (you might want to see if the size is being caused by tables, indexes or other objects first)
- Add a where clause to only list the schema/tablespace/object_type you want to investigate.

  owner SCHEMA, 
  tablespace_name TABLESPACE, 
  segment_type OBJECT_TYPE, 
  segment_name OBJECT_NAME,
  round((sum(bytes)/1024/1024), 3) MB
from dba_segments
group by owner, tablespace_name, segment_type, segment_name
order by owner, tablespace_name, MB desc;

It does not show you what DML is causing it, but it already shows you what objects are causing it. You can then go trough the query history in Enterprise Manager to find queries that work with these problematic objects.

In Enterprise Manager, go to the performance tab, and at the bottom select "Search SQL". Then search for queries with, for example, the large table in their "SQL text". Like this, for example: %large_table_name%

Good luck!


Another check I would recommend is find out which users have a quota for the USERS tablespace. Also check who has UNLIMITED TABLESPACE system privilege. If you find any account with the UNLIMITED TABLESPACE privilege, unless it is the DBA's account, remove it and provide specific quotas to tablespaces based on need (and reject any "gee, would be nice if I had that"). And to prevent this sudden size increase in the future, insure everyone who can write to USERS has a quota limit. The USERS tablespace is usually used to provide a place for devs or others in case they need to create their own objects. And it is also subject to abuse ("Gee, I can't import a couple of million rows? /whine I didn't know it would break something!"). And don't be generous with quotas, as most folks won't use what you give them, except for the few problem children (those who think ever-expanding hard drive space exists). From what you have written, these protections were not in place, and you have a wild child on the loose in your DB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.