If you perform some sort of cleanup on a table, then the deleted space will be reused upon further insertion, but what if
That cleanup was the last task that we perform on a table, but not expecting new data to come in.
You are performing many full scan queries on that table, but want to make sure they are as efficient as possible.
Then there are many benefits of performing shrink on that table to reclaim that space, one of the cool thing about segment advisor is that it will detect, if there are some benefits to be gained by shrinking the segments based on the space management technique of their tablespace attributes.
demo@XEPDB1> select tablespace_name,segment_space_management
2 from dba_tablespaces
3 where tablespace_name in ('USERS','TS_DATA');
TABLESPACE_NAME SEGMEN
------------------------------ ------
TS_DATA MANUAL
USERS AUTO
So got two tablespace one is ASSM and other is non-ASSM.
demo@XEPDB1> create table t_auto
2 tablespace users
3 nologging as
4 select a.*
5 from dba_objects a,
6 dba_users u
7 where rownum <=1000000;
Table created.
demo@XEPDB1> create table t_manual
2 tablespace ts_data
3 nologging as
4 select a.*
5 from dba_objects a,
6 dba_users u
7 where rownum <=1000000;
Table created.
we build two different table of same size on each tablespace.
demo@XEPDB1> delete from t_auto where mod(object_id,2) =0;
500440 rows deleted.
demo@XEPDB1> delete from t_manual where mod(object_id,2) =0;
500433 rows deleted.
demo@XEPDB1> commit;
Commit complete.
Then we delete all the even rows on them, to make it even more fragmented, and then we use DBMS_ADVISOR packages, to review the findings on allocated and unused spaced on ASSM objects.
demo@XEPDB1> declare
2 l_task_name varchar2(20) :='TEST_TASK';
3 l_object_id number;
4 begin
5 begin
6 dbms_advisor.delete_task(task_name => l_task_name);
7 exception
8 when others then
9 null;
10 end;
11 dbms_advisor.create_task( advisor_name=> dbms_advisor.ADV_NAME_SEGMENT,
12 task_name => l_task_name );
13 dbms_advisor.create_object( task_name=> l_task_name,
14 object_type=> 'TABLE',
15 attr1 =>'DEMO',
16 object_id => l_object_id,
17 attr2 =>'T_AUTO' );
18 dbms_advisor.set_task_parameter( task_name=> l_task_name,
19 parameter =>'RECOMMEND_ALL',
20 value=>'TRUE');
21 dbms_advisor.execute_Task(task_name=> l_task_name);
22 end;
23 /
PL/SQL procedure successfully completed.
demo@XEPDB1> select message,more_info
2 from user_advisor_findings
3 where task_name ='TEST_TASK';
MESSAGE MORE_INFO
--------------------------------------------- ----------------------------------------
Enable row movement of the table DEMO.T_AUTO Allocated Space:167772160: Used Space:90
and perform shrink, estimated savings is 7696 811867: Reclaimable Space :76960293:
0293 bytes.
But what if the segments sits on non-ASSM tablespaces, on which we cannot perform shrink style operations.
demo@XEPDB1> declare
2 l_task_name varchar2(20) :='TEST_TASK';
3 l_object_id number;
4 begin
5 begin
6 dbms_advisor.delete_task(task_name => l_task_name);
7 exception
8 when others then
9 null;
10 end;
11 dbms_advisor.create_task( advisor_name=> dbms_advisor.ADV_NAME_SEGMENT,
12 task_name => l_task_name );
13 dbms_advisor.create_object( task_name=> l_task_name,
14 object_type=> 'TABLE',
15 attr1 =>'DEMO',
16 object_id => l_object_id,
17 attr2 =>'T_MANUAL' );
18 dbms_advisor.set_task_parameter( task_name=> l_task_name,
19 parameter =>'RECOMMEND_ALL',
20 value=>'TRUE');
21 dbms_advisor.execute_Task(task_name=> l_task_name);
22 end;
23 /
PL/SQL procedure successfully completed.
demo@XEPDB1> select message,more_info
2 from user_advisor_findings
3 where task_name ='TEST_TASK';
MESSAGE MORE_INFO
--------------------------------------------- ----------------------------------------
Perform re-org on the object T_MANUAL, estima Allocated Space:167772160: Used Space:90
ted savings is 77277135 bytes. 495025: Reclaimable Space :77277135:
as you can see the segment advisor will take that into account, and adjust its recommendation accordingly.
