Problem solve Get help with specific problems with your technologies, process and projects.

Actual disk space usage of tables

A nice table size script.

Have you ever desired a quick-fire way of knowing exactly how much space a table within a tablespace is taking up on the physical disk? This script will give you that answer:

--
-- For this script to work, tables must have been analyzed on a regular basis.
-- If not, then analyze table 
 
   compute statistics; before you begin.
--
-- Note: Must be connected as SYSTEM
--

SET LINESIZE 100
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT t_name PROMPT 'Please enter a table name: '

DECLARE
   v_total_blocks	number;
   v_block_size		number;
   cursor bsize_cursor is
   select value 
   from v$parameter
   where name = 'db_block_size';
BEGIN
   open bsize_cursor;
   fetch bsize_cursor into v_block_size;
   close bsize_cursor;
   select sum(blocks + empty_blocks)
   into v_total_blocks
   from dba_tables 
   where table_name = upper('&t_name');
   dbms_output.put_line('The exact size of '||'&t_name '||'is: '||to_char(v_total_blocks*v_block_size)||' bytes.'||' Or '||round(to_char(v_total_blocks*v_block_size/1024/1024),2)||' megabytes.');
END;
/
 

For More Information


Dig Deeper on Oracle database performance problems and tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close