Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
    
Do you have diagnostic pack licenses? –  FreshPhilOfSO May 11 '12 at 8:37
    
Have you identified the "guilty" objects yet? –  ipip May 11 '12 at 9:06
1  
@ipip Haven't yet. Now I am analyzing archived log files using Log Miner. –  kupa May 11 '12 at 9:15
    
Using logminer is a tedious job but should provide you with the correct insight. –  ik_zelf May 11 '12 at 9:20
add comment

2 Answers

up vote 4 down vote accepted

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!

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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