I have been working on tasks for weekly tablespace segment advisor to shrink all segments residing in tablespace.
There are many blogs out there with the same info; however, it was not to the requirements and this is a combinations after research.
Here is a demo for 19c; however, the preparations have been tested in 11.2.
=======================================================
### SQL Scripts
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ ls -l
total 11
-rwxrwxrwx 1 vagrant vagrant 1048 Aug 23 04:07 10-advise.sql
-rwxrwxrwx 1 vagrant vagrant 257 Aug 23 03:31 20-benefit.sql
-rwxrwxrwx 1 vagrant vagrant 475 Aug 23 04:05 30-space_save.sql
-rwxrwxrwx 1 vagrant vagrant 722 Aug 23 03:36 40-recommendations.sql
-rwxrwxrwx 1 vagrant vagrant 431 Aug 23 04:09 99-delete.sql
-rwxrwxrwx 1 vagrant vagrant 141 Aug 23 04:03 set_global_var.sql
-rwxrwxrwx 1 vagrant vagrant 259 Aug 23 04:00 test.sql
=======================================================
### Create test case.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ test.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:10:55 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> drop table big_table purge;
drop table big_table purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01
SQL> create table big_table (id number, name char(200)) tablespace USERS;
Table created.
Elapsed: 00:00:00.02
SQL> insert into big_table select rownum,'a' from dual connect by rownum<900000; 899999 rows created. Elapsed: 00:00:12.65 SQL> commit;
Commit complete.
Elapsed: 00:00:01.86
SQL> delete from big_table where mod(id,10)<>0;
810000 rows deleted.
Elapsed: 00:00:37.72
SQL> commit;
Commit complete.
Elapsed: 00:00:00.08
SQL> exit
=======================================================
### Run advise.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 10-advise.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:13:40 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> DECLARE
2 l_object_id NUMBER;
3 l_object_type VARCHAR2(32767) := 'TABLESPACE';
4 l_attr1 VARCHAR2(32767) := '&v_tablespace';
5 l_task_name VARCHAR2(32767) := '&v_task_name';
6 BEGIN
7 DBMS_ADVISOR.create_task (
8 advisor_name => 'Segment Advisor',
9 task_name => l_task_name
10 );
11
12 DBMS_ADVISOR.create_object (
13 task_name => l_task_name,
14 object_type => l_object_type,
15 attr1 => l_attr1,
16 attr2 => NULL,
17 attr3 => NULL,
18 attr4 => 'null',
19 attr5 => NULL,
20 object_id => l_object_id
21 );
22
23 DBMS_ADVISOR.set_task_parameter (
24 task_name => l_task_name,
25 parameter => 'RECOMMEND_ALL',
26 value => 'TRUE');
27
28 DBMS_ADVISOR.execute_task (
29 task_name => l_task_name
30 );
31
32 END;
33 /
old 4: l_attr1 VARCHAR2(32767) := '&v_tablespace';
new 4: l_attr1 VARCHAR2(32767) := 'USERS';
old 5: l_task_name VARCHAR2(32767) := '&v_task_name';
new 5: l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TBS_USERS';
SQL> set feedback on echo on head on
SQL> select task_name, advisor_name
2 from DBA_ADVISOR_TASKS
3 where advisor_name='Segment Advisor'
4 ;
TASK_NAME ADVISOR_NAME
------------------------------ ---------------------------------------
SEGMENT_ADVISOR_TBS_USERS Segment Advisor
1 row selected.
SQL> exit
=======================================================
### Review benefit.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 20-benefit.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:14:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='&v_task_name' order by 1;
old 1: SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='&v_task_name' order by 1
new 1: SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='SEGMENT_ADVISOR_TBS_USERS' order by 1
BENEFIT_TYPE
------------------------------------------------------------------------------------------------------------------------------------------------------
Enable row movement of the table SYS.BIG_TABLE and perform shrink, estimated savings is 180447064 bytes.
SQL> exit
=======================================================
### Review space saving.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 30-space_save.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:15:48 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> col segment_name for a30
SQL> SELECT
2 segment_name,
3 round(allocated_space/1024/1024,1) alloc_mb,
4 round(used_space/1024/1024,1) used_mb,
5 round(reclaimable_space/1024/1024) reclaim_mb,
6 round(reclaimable_space/allocated_space*100,0) pctsave
7 FROM TABLE(dbms_space.asa_recommendations())
8 where tablespace_name='&v_tablespace'
9 order by pctsave desc
10 ;
SEGMENT_NAME ALLOC_MB USED_MB RECLAIM_MB PCTSAVE
------------------------------ ---------- ---------- ---------- ----------
BIG_TABLE 216 43.9 172 80
SQL> exit
=======================================================
### Create recommendations.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 40-recommendations.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:16:24 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
***********************************************************************
*** nohup sqlplus "/ as sysdba" @run.sql > run.out 2>&1 & ***
***********************************************************************
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle@db-fs-1:hawk:/sf_working/segment_advisor
=======================================================
### Review SQL script.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ cat run.sql
set echo on timing on
alter table "SYS"."BIG_TABLE" enable row movement;
alter table "SYS"."BIG_TABLE" shrink space COMPACT;
alter table "SYS"."BIG_TABLE" shrink space;
exit
=======================================================
### Run SQL script using nohup.
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ nohup sqlplus "/ as sysdba" @run.sql > run.out 2>&1 &
[1] 27417
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$
[1]+ Done nohup sqlplus "/ as sysdba" @run.sql > run.out 2>&1
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$
=======================================================
### Review results.
=======================================================
Live Run for comparison.
/home/oracle/segment_advisor$ grep ORA- run.out | sort | uniq -c
6 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2 ORA-10631: SHRINK clause should not be specified for this object
12 ORA-10636: ROW MOVEMENT is not enabled
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ grep ORA- run.out | sort | uniq -c
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ grep Elapsed run.out | sort | uniq -c
1 Elapsed: 00:00:00.02
1 Elapsed: 00:00:02.91
1 Elapsed: 00:00:06.84
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ cat run.out
nohup: ignoring input
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:18:07 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> alter table "SYS"."BIG_TABLE" enable row movement;
Table altered.
Elapsed: 00:00:00.02
SQL> alter table "SYS"."BIG_TABLE" shrink space COMPACT;
Table altered.
Elapsed: 00:00:06.84
SQL> alter table "SYS"."BIG_TABLE" shrink space;
Table altered.
Elapsed: 00:00:02.91
SQL>
SQL> exit
=======================================================
### Delete Advisor Task
=======================================================
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$ sqlplus / as sysdba @ 99-delete.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 23 04:20:04 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> DECLARE
2 l_object_id NUMBER;
3 l_object_type VARCHAR2(32767) := 'TABLESPACE';
4 l_attr1 VARCHAR2(32767) := 'v_tablespace';
5 l_task_name VARCHAR2(32767) := '&v_task_name';
6 BEGIN
7 DBMS_ADVISOR.delete_task (
8 task_name => l_task_name
9 );
10 END;
11 /
old 5: l_task_name VARCHAR2(32767) := '&v_task_name';
new 5: l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TBS_USERS';
PL/SQL procedure successfully completed.
SQL> select task_name, advisor_name
2 from DBA_ADVISOR_TASKS
3 where advisor_name='Segment Advisor'
4 ;
no rows selected
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle@db-fs-1:hawk:/sf_working/segment_advisor
$
Thinking Out Loud 