Calculate actual space usage for tables or indexes (dbms_space)

There are several ways to calculate actual size of tables or indexes. In my example I'll use procedure object_space_usage from DBMS_SPACE package.

dbms_space.object_space_usage (
object_owner IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN VARCHAR2,
sample_control IN NUMBER,
space_used OUT NUMBER,
space_allocated OUT NUMBER,
chain_pcent OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL,
preserve_result IN BOOLEAN DEFAULT TRUE,
timeout_seconds IN NUMBER DEFAULT NULL);

I will create testing table with 1000000 rows using script from T. Kyte pages. On this table I will also create several indexes.



Script is shown below.

create table msutic.bigtab
as
select rownum id, a.*
from all_objects a
where 1=0
/

Table created.


alter table msutic.bigtab nologging;

Table altered.


declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append*/
into msutic.bigtab
select rownum, a.*
from all_objects a
where rownum <= 1000000;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)
loop
insert /*+APPEND */ into msutic.bigtab
select rownum+l_cnt,
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID,OBJECT_TYPE,
CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,
GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
from msutic.bigtab
where rownum <= l_rows - l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/


alter table msutic.bigtab add constraint
bigble_pk primary key(id);


create index owner_idx on msutic.bigtab(owner);


create index name_idx on msutic.bigtab(object_name,subobject_name);


analyze table msutic.bigtab compute statistics;


select count(*) from msutic.bigtab;

COUNT(*)
----------
1000000

When I query size of segments:

select segment_name, segment_type, bytes/1024 size_K
from dba_segments
where segment_name = 'BIGTAB' or
segment_name in (select index_name from dba_indexes
where table_name='BIGTAB' and table_owner='MSUTIC');


SEGMENT_NAME    SEGMENT_TYPE           SIZE_K
--------------- ------------------ ----------
BIGBLE_PK       INDEX                   17408
OWNER_IDX       INDEX                   19456
NAME_IDX        INDEX                   41984
BIGTAB          TABLE                  131072

Now I'll delete 333333 rows and use procedure dbms_space.object_space_usage to calculate used space.

system@TEST11> delete from msutic.bigtab where mod(id,3)=0;

333333 rows deleted.

commit;

analyze table msutic.bigtab compute statistics;

I will use script from Morgan's Library:

set serveroutput on

DECLARE
su NUMBER;
sa NUMBER;
cp NUMBER;
BEGIN
dbms_space.object_space_usage('&segment_owner', '&segment_name', 
'&segment_type', NULL, su, sa, cp);

dbms_output.put_line('Space Used: ' || TO_CHAR(su)/1024||' K');
dbms_output.put_line('Space Allocated: ' || TO_CHAR(sa)/1024 ||' K');
dbms_output.put_line('Chained Percentage: ' || TO_CHAR(cp));
END;
/

Usage estimations: BIGTAB Space Used: 88236.12890625 K Space Allocated: 131072 K Chained Percentage: 0 BIGBLE_PK Space Used: 11857.6826171875 K Space Allocated: 17408 K Chained Percentage: 0 OWNER_IDX Space Used: 13180.302734375 K Space Allocated: 19456 K Chained Percentage: 0 NAME_IDX Space Used: 29232.814453125 K Space Allocated: 41984 K Chained Percentage: 0 Now I'll rebuild table and indexes just to check those estimations.

system@TEST11> alter table msutic.bigtab move;

Table altered.

system@TEST11> alter index msutic.bigble_pk rebuild;

Index altered.

system@TEST11> alter index system.owner_idx rebuild;

Index altered.

system@TEST11> alter index system.name_idx rebuild;

Index altered.

Now to check size of segments:

select segment_name, segment_type, bytes/1024 size_K
from dba_segments
where segment_name = 'BIGTAB' or
segment_name in (select index_name from dba_indexes
where table_name='BIGTAB' and table_owner='MSUTIC');

SEGMENT_NAME      SEGMENT_TYPE     SIZE_K
-------------------------- ------------------ ----------
BIGBLE_PK              INDEX                   12288
BIGTAB                 TABLE                   90112
OWNER_IDX              INDEX                   13312
NAME_IDX               INDEX                   28672

BIGTAB Actual: 90112k Estimation: 88236.12k Difference: -2,1 % BIGBLE_PK Actual: 12288k Estimation: 11857.68k Difference: -3.5% OWNER_IDX Actual: 13312k Estimation: 13180.30 Difference: -0,98% NAME_IDX Actual: 28672k Estimation: 29232.81 Difference: +1.01% Procedure DBMS_SPACE.OBJECT_SPACE_USAGE could be very useful for estimating actual space usage. Clean up:

system@TEST11> drop table msutic.bigtab purge;

Table dropped.