Here's a little script I wrote... Gets you the actual space used by the
data (up to the highwater mark in the table.
Change the OWNER (table owner). Save the file and execute it in sqlplus
using @

HTH
Gregg

set serveroutput on ECHO OFF
/*****************************************************/
/* Find the highwater mark in a table */
/* ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K; */
/* gb - PSAConsulting */
/* */
/* @C:\DBA\Oracle9\Space_mgmt\Table_Free_Space.sql */
/* */
/*****************************************************/
prompt
prompt
declare
v_user varchar2(50) := 'SYSADM';
v_object_name varchar2(50) := '&TABLE';
v_object_type varchar2(15) := 'TABLE';
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
v_partition_name varchar2(50);
v_initial number;
v_next number;
v_used_blocks number;
v_number_extents number;
v_num_rows number;
v_avg_row_len number;
v_tablespace_name varchar2(30);
v_PCT_FREE number;
v_PCT_USED number;
v_INI_TRANS number;
v_FREELISTS number;
v_last_analyzed date;
begin
V_OBJECT_NAME := UPPER(V_OBJECT_NAME);
IF v_object_type = 'TABLE'
THEN
SELECT initial_extent,next_extent,num_rows,avg_row_len, tablespace_name,
pct_free,pct_used,ini_trans,freelists,last_analyzed
INTO v_initial, v_next, v_num_rows, v_avg_row_len, v_tablespace_name,
v_pct_free,v_pct_used,v_ini_trans,v_freelists,v_last_analyzed
FROM all_tables
WHERE owner = v_user
AND table_name = v_object_name;
ELSE
SELECT initial_extent,next_extent, tablespace_name
INTO v_initial, v_next, v_tablespace_name
FROM all_indexes
WHERE owner = v_user
AND index_name = v_object_name;
END IF;
SELECT count(*)
INTO v_number_extents
FROM dba_extents
WHERE owner = v_user
AND segment_name = v_object_name;
DBMS_SPACE.UNUSED_SPACE(

v_user,v_object_name,v_object_type,v_total_blocks,v_total_bytes,v_unused_blo
cks

,v_unused_bytes,v_last_used_extent_file_id,v_last_used_extent_block_id,v_las
t_used_block,v_partition_name);
dbms_output.put_line('. ');

dbms_output.put_line('######################################################
######## ');
dbms_output.put_line('Object Name: '||v_object_name||' -
'||v_object_type||' Tablespace: '||v_tablespace_name);

dbms_output.put_line('######################################################
######## ');
dbms_output.put_line('. ');
dbms_output.put_line('Number of Extents: '||v_number_extents||' Initial:
'||v_initial||' Next: '||v_next||' Pct_Free: '||v_pct_free||' Pct_Used:
'||v_pct_used);
dbms_output.put_line(' INI_Trans: '||v_ini_trans||' Freelists:
'||v_freelists||' Last Analyzed: '||v_last_analyzed);
dbms_output.put_line('......Approx Rows: '||v_num_rows||' Avg Row Len:
'||v_avg_row_len);
dbms_output.put_line('Blocks in segment:
'||v_total_blocks);
dbms_output.put_line('Bytes in segment(Size):
'||v_total_bytes);
dbms_output.put_line('MegaBytes in segment:
'||v_total_bytes/1048576);
dbms_output.put_line('Blocks not used:
'||v_unused_blocks);
dbms_output.put_line('Bytes not used:
'||v_unused_bytes);
dbms_output.put_line('MegaBytes not used:
'||v_unused_bytes/1048576);
dbms_output.put_line('File ID - last extent with data(Block):
'||v_last_used_extent_file_id);
dbms_output.put_line('File ID - last extent with data(Byte):
'||v_last_used_extent_block_id);
dbms_output.put_line('Last block with data:
'||v_last_used_block);
end;
/
SET ECHO ON

----- Original Message -----
From: "Leinen Christian (EXT) "
<oracledba-ezmlmshield-x66150958.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, May 07, 2007 8:52 AM
Subject: Actual size of data in a table


> Hi all,
>
> how can i query the actual size of data in a table? Querying sum(bytes)
> from user_segments will return the size of the extents that have been
> allocated, right?
>
>
> Chris
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>


Oracle LazyDBA home page