Why and How to check and table size/actual size in oracle ?

 Why ?

In Oracle after the deletion of number of rows in the table you will expected a size change however due the high water mark things which mark the deleted space and keep the HWM thus the size will not be affected unless you do further action, however it is useful to know the actual size after this deletion and whether you will reclaim or not this is can be decided based on that.

How ?

Below are queries to this matter:

1- To check a table reserved size  :

col owner format A20

col SEGMENT_NAME format A20

set linesize 300

SELECT SEGMENT_NAME ,  OWNER , SUM(bytes)/1024/1024 "MB"

FROM DBA_SEGMENTS

WHERE segment_name=upper('&object_name') and segment_type= upper('&object_type')

Group by segment_name , owner;

 2- To check the waisted size and how many reclaimed size , you may check the below query , however it is mostly same to things in segment adviser that I will add about it last things , it is can help you to reach 90% of things in this matter:

SELECT TABLE_NAME

         ROUND((BLOCKS * 8/1024),0) "SIZE (MB)",

         ROUND((NUM_ROWS * AVG_ROW_LEN / 1024/1024), 0) "ACTUAL DATA (MB)",

         (ROUND((BLOCKS * 8/1024),0) - ROUND((NUM_ROWS * AVG_ROW_LEN / 1024/1024), 0)) "WASTED (MB)"

    FROM DBA_TABLES

   WHERE  OWNER LIKE upper('&owner_name') and table_name = upper('&table_name') ;

 3- If you just do the clean you , you may need to update the DB about this object using the below  :

analyze table &owner..&object COMPUTE STATISTICS;

 

4- Another option is to use the Segment advisor which part of tuning pack license so if you got this license you can run the advisor or it is daily run by default to check all these details , using Oracle Enterprise Manager ( OEM) ,

 

You may run it manually if you don’t have the OEM agent in this DB as below :

The example that follows shows how to use the DBMS_ADVISOR procedures to run the Segment Advisor for the sample table hr.employees.

variable id number;

begin

  declare

  name varchar2(100);

  descr varchar2(500);

  obj_id number;

  begin

  name:='Manual_Employees';

  descr:='Segment Advisor Example';

 

  dbms_advisor.create_task (

    advisor_name     => 'Segment Advisor',

    task_id          => :id,

    task_name        => name,

    task_desc        => descr);

 

  dbms_advisor.create_object (

    task_name        => name,

    object_type      => 'TABLE',

    attr1            => 'HR',

    attr2            => 'EMPLOYEES',

    attr3            => NULL,

    attr4            => NULL,

    attr5            => NULL,

    object_id        => obj_id);

 

  dbms_advisor.set_task_parameter(

    task_name        => name,

    parameter        => 'recommend_all',

    value            => 'TRUE');

 

  dbms_advisor.execute_task(name);

  end;

end;

/

** Then use select from view or dbms_space.asa_recommendations

The following example shows how to query the DBA_ADVISOR_* views to retrieve findings from all Segment Advisor runs submitted by user STEVE:

 

select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message

  from dba_advisor_findings af, dba_advisor_objects ao

  where ao.task_id = af.task_id

  and ao.object_id = af.object_id

  and ao.owner = 'STEVE';

TASK_NAME          SEGNAME      PARTITION       TYPE             MESSAGE

 

select tablespace_name, segment_name, segment_type, partition_name,

recommendations, c1 from

table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

** the above example is from Oracle document 

https://docs.oracle.com/cd/E18283_01/server.112/e17120/schema003.htm.