SQL> select type,message,more_info from dba_advisor_findings
2 where task_name in ('SEGMENTADV_4328829','SEGMENTADV_9662658','SEGMENTADV_9854361');
TYPE MESSAGE MORE_INFO
---------------------- ---------------------------------------- ----------------------------------------------------
PROBLEM The free space in the object is less tha Allocated Space:5242880: Used Space:5067168: Reclaim
n 10MB. able Space :175712:
PROBLEM The free space in the object is less tha Allocated Space:41943040: Used Space:40156119: Recla
n 10MB. imable Space :1786921:
INFORMATION Perform shrink, estimated savings is 123 Allocated Space:41943040: Used Space:29633599: Recla
09441 bytes. imable Space :12309441:
SQL>
任务执行之后出现no segment found之后,dba_advisor_findings里面的type是PROBLEM,oracle认为这个任务是有问题的,否则的话type是INFORMATION,需要shrink的话会给出shrink建议。不习惯使用em的话也可以通过api来执行:
segment advisor Segment Advisor是10g中新增的一个工具,可以用来估算表的空间利用率,并给出相应的建议,确定是否需要进行shrink。以下为测试过程,下面的内容来着连接:
http://hi.baidu.com/whxaszxcv/item/58cc5610e4d32fa3ffded5c7
防止连接失效,把内容拷贝了过来:
1. 表test大小为80M,初始时数据比较紧凑,对其执行批量delete操作
CREATE TABLE TEST AS SELECT * FROM DBA_SEGMENTS;
INSERT INTO TEST SELECT * FROM TEST;
INSERT INTO TEST SELECT * FROM TEST;
SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='TEST';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------- ------------------ ---------------
TEST TABLE 80
SQL> select count(*) from test;
COUNT(*)
----------
738624
SQL> delete from test where rownum<=400000;
400000 rows deleted.
SQL> commit;
Commit complete.
2.执行Segment Advisor,对表的存储空间进行检查
SQL> variable id number;
SQL> begin
declare
object_id number;
name varchar2(100);
task_desc varchar2(100);
begin
name := 'ASS';-----(任务名字)
task_desc := 'Segment Advisor A';
dbms_advisor.create_task(advisor_name => 'Segment Advisor',
task_id => :task_id,
task_name => name,
task_desc => task_desc
);
dbms_advisor.create_object(task_name => name,
object_type => 'TABLE',
attr1 => 'SYS',--------系统帐号
attr2 => 'A',------表的名字
attr3 => 'SS',------分区名字(如果没有,就用NULL)
attr4 => NULL,
attr5 => NULL,
object_id =>object_id
);
dbms_advisor.set_task_parameter(task_name => name,
parameter => 'recommend_all',
value => 'TRUE'
);
dbms_advisor.execute_task(task_name => name);
end;
end;
PL/SQL procedure successfully completed.
SQL> select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
2 from dba_advisor_findings af, dba_advisor_objects ao
3 where ao.task_id = af.task_id
4 and ao.object_id = af.object_id
5 and ao.owner = 'SYS';
TASK_NAME SEGNAME PARTITION TYPE MESSAGE
-------------------- -------------------- ---------- ---------- -------------------------
TEST TEST TABLE Enable row movement of th
e table SYS.TEST and perf
orm shrink, estimated sav
ings is 43132568 bytes.
从可以看出,Segment Advisor给出的建议是对表做shrink,并且估算出可以节省的空间约为43132568 bytes。
3. 执行shrink操作
SQL> select table_name,row_movement from user_tables where table_name='TEST';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST DISABLED
SQL> alter table test enable row movement;
Table altered.
SQL> alter table test shrink space;
Table altered.
SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='TEST';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------ ---------------
TEST TABLE 34
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1060139/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1060139/