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