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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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%
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.