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.
