Oracle : Finding User/Database Size
Finding User(s) Size
Sometimes one needs to know the 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;
Similarly it is possible 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;
Finding Database Size
The above query only returns the space occupied by various users. Following section describes how the total database size can be computed:
The database mainly comprises of datafiles, temp files and redo log files.
The biggest portion of a database’s size comes from the datafiles. To find out how many megabytes are allocated to all datafiles:
SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;
To get the size of all TEMP files:
SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;
To get the size of the on-line redo-logs:
SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;
Finally, summing up the three above, total database size can be found:
SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size" FROM (SELECT sum(bytes) data_size FROM dba_data_files ) dsize, (SELECT nvl(sum(bytes),0) temp_size FROM dba_temp_files ) tsize, (SELECT sum(bytes) redo_size FROM sys.v_$log ) rsize;
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment