My database dramatically increased in size. Why?

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?

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.

select 
  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!

StackExchange Database Administrators Q#17725, answer score: 5