通过段调优顾问回收大表的分配空间和高水位线

--通过段调优顾问回收大表的分配空间和高水位线
--创建用户
SQL> create tablespace hzqtbs datafile '/u01/app/oracle/oradata/prod/hzqtbs.dbf' size 30m;

Tablespace created.

SQL> create user hzq identified by hzq default tablespace hzqtbs quota unlimited on hzqtbs account unlock;

User created.

SQL> grant create session to hzq;

Grant succeeded.

SQL> grant resource to hzq;

Grant succeeded.

SQL> conn hzq/hzq
Connected.

create table test (id number,name varchar2(20));
begin
for i in  1..1000000 loop
insert into test values(i,'user'||i);
commit;
end loop;
end;
/

delete test;
insert into test values(1,'user1');

--查看有多少调优顾问类型
select * from DBA_ADVISOR_DEFINITIONS;

ADVISOR_ID ADVISOR_NAME             PROPERTY
---------- ------------------------------ ----------
     1 ADDM                    1
     2 SQL Access Advisor             271
     3 Undo Advisor                1
     4 SQL Tuning Advisor             935
     5 Segment Advisor              67
     6 SQL Workload Manager            0
     7 Tune MView                  31
     8 SQL Performance Analyzer         935
     9 SQL Repair Advisor             679
    10 Compression Advisor               3


--查看段调优顾问作业是否工作
select
segments_processed
,end_time
from dba_auto_segadv_summary
order by end_time;

--手动生成段顾问建议

DECLARE
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
BEGIN
my_task_name := 'MYTEST Advice';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1 create a task
---------
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
---------
-- Step 2 create a object
---------
dbms_advisor.create_object (
task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'HZQ',
attr2 => 'TEST',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter(
task_name => my_task_name,
parameter => 'recommend_all',
value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/


--查看昨天到今天运行的调优顾问（11G）
select
'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;

--查看表是否有回收空间
SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));


--查看MYTEST Advice段调优顾问的建议，看是否有回收空间
SELECT
'Task Name : ' || f.task_name || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info TASK_ADVICE
FROM dba_advisor_findings f
,dba_advisor_objects o
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_name like 'MYTEST Advice'
ORDER BY f.task_name;

Task Name : MYTEST Advice
Segment Name : TEST
Segment Type : TABLE
Partition Name :
Message : Enable row movement of the table HZQ.TEST and perform shrink, estimate
d savings is 19416317 bytes.
More Info : Allocated Space:24117248: Used Space:4700931: Reclaimable Space :19416317:


--回收表空间
alter table test enable row movement; --开启行迁移功能。
alter table test shrink space compact;--(可以在压缩期间进行DML操作和查询) ,收缩表，不会降低hwm
alter table test shrink space; --( 调整HWM时将阻塞DML操作),收缩表，并且降低hwm
alter table test_idx shrink space cascade;--收缩表并降低hwm，并且回收相应的索引。

--删除，重新收集手动运行顾问
DECLARE
BEGIN
dbms_advisor.delete_task (
task_name => 'MYTEST Advice'
);
END;
/

DECLARE
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
BEGIN
my_task_name := 'MYTEST Advice';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1 create a task
---------
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
---------
-- Step 2 create a object
---------
dbms_advisor.create_object (
task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'HZQ',
attr2 => 'TEST',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter(
task_name => my_task_name,
parameter => 'recommend_all',
value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/


--查看昨天到今天运行的调优顾问（11G）
select
'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;

--查看表是否有回收空间
SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

--再次通过段调优顾问查看可回收空间
 SELECT
'Task Name : ' || f.task_name || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info TASK_ADVICE
FROM dba_advisor_findings f
,dba_advisor_objects o
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_name like 'MYTEST Advice'
ORDER BY f.task_name;  

TASK_ADVICE
--------------------------------------------------------------------------------
Task Name : MYTEST Advice
Segment Name : TEST
Segment Type : TABLE
Partition Name :
Message : The free space in the object is less than 10MB.
More Info : Allocated Space:65536: Used Space:1824: Reclaimable Space :63712: