Determine database size

Determine database size and free space.

This script reports the following:

  • the names of all tablespaces
  • the datafiles and locations that are associated with the tablespaces
  • the size of each tablespace
  • the amount of used and free space in each tablespace (which is also represented as a percentage)

It has been tested on versions 8 and up. Log in as a user with DBA privileges or as SYS or SYSTEM to run this script.

 declare dbname VARCHAR2(8); CURSOR c1 IS SELECT tablespace_name, file_id, max(bytes) largeextent, sum(bytes) totalfreespace FROM sys.dba_free_space GROUP BY tablespace_name, file_id ORDER BY tablespace_name, file_id; CURSOR c2 (fileid INTEGER) IS SELECT name, bytes FROM v$datafile WHERE file# = fileid; BEGIN SELECT name INTO dbname FROM v$database; DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Space Report for Database '||dbname); DBMS_OUTPUT.PUT_LINE('-----------------------------------'); DBMS_OUTPUT.PUT_LINE(''); FOR rec1 IN c1 LOOP DBMS_OUTPUT.PUT_LINE('Tablespace Name: '||rec1.tablespace_name); DBMS_OUTPUT.PUT_LINE(''); FOR rec2 IN c2 (rec1.file_id) LOOP DBMS_OUTPUT.PUT_LINE('Data File Name: ' ||rec2.name); DBMS_OUTPUT.PUT_LINE('Total File Size (MB): ' ||ROUND(rec2.bytes/1024/1024,0)); DBMS_OUTPUT.PUT_LINE('Free Space in File (MB): ' ||ROUND(rec1.totalfreespace/1024/1024,0)); DBMS_OUTPUT.PUT_LINE('% Free Space in File: ' ||ROUND (((rec1.totalfreespace/rec2.bytes)* 100),0)||'%'); DBMS_OUTPUT.PUT_LINE('Largest Extent (MB): ' ||ROUND(rec1.largeextent/1024/1024,0)); DBMS_OUTPUT.PUT_LINE(''); END LOOP; END LOOP; END; /

 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.


 

This was first published in October 2003

Dig deeper on Oracle DBA jobs, training and certification

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close