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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our Oracle and SQL gurus are waiting to answer your toughest questions.