Determine database size

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)

 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.