Monday, March 9, 2009

Query to find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

No comments: