目录
一、预警值设置案例
1、查看默认表空间
SQL>select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%DEFAULT%';
2、恢复pct预警值
SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD(dbms_server_alert.tablespace_pct_full, NULL,NULL,NULL,NULL,1,1,NULL,dbms_server_alert.object_type_tablespace,NULL);
3、查看默认的值
col critical_value for a20 col warning_value for a20 select warning_value,critical_value from dba_thresholds where object_name='USERS';
4、确认表是自动段管理的（手动管理的表，是无法shrink的）
其中tablespace是system的，undo,temp的表空间的segment_space_management是手动管理的。
如果把表建在system上，是无法进行shrink的。
SQL>select tablespace_name,segment_space_management from dba_tablespaces;
5、查看当前表空间CDATA的PCT_FREE大小
SQL>select df.tablespace_name tablespace,fs.bytes free,df.bytes,fs.bytes *100/ df.bytes pct_free from dba_data_files df,dba_free_space fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name = 'CDATA' ;
6、设置最新的预警值（55,70）
SQL/PLUS执行,object_name是存在的才可以。
SQL>exec dbms_server_alert.set_threshold(- metrics_id=> dbms_server_alert.tablespace_pct_full,- warning_operator=> dbms_server_alert.operator_ge,- warning_value=>'55',- critical_operator=> dbms_server_alert.operator_ge,- critical_value=>'70',- observation_period=> 1,- consecutive_occurrences=> 1, - instance_name=>'OCPORCL',- object_type => dbms_server_alert.object_type_tablespace,- object_name =>'USERS');
二、数据表收缩案例
1、创建表空间，用户及表
SQL>create tablespace tbs_test datafile '/oracle/oradata/orclpdb3.dbf' size 10m autoextend off; SQL>create user test identified by 123 default tablespace tbs_test;
SQL>insert into t_test values(1,'LLL01');
2、查看压缩前块数量
SQL>select blocks from user_segments where segment_name= 'T_TEST' ; BLOCKS ---------- 8
3、查看PCT值
SQL>select df.tablespace_name tablespace,fs.bytes free,df.bytes,fs.bytes*100/df.bytes pct_free from dba_data_files df,dba_free_space fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name = 'TBS_TEST';
4、插入数据直到pct_free到40%左右
insert into t_test select * from t_test;
5、查看pct
SQL>select df.tablespace_name tablespace,fs.bytes free,df.bytes,fs.bytes*100/df.bytes pct_free from dba_data_files df,dba_free_space fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name = 'TBS_TEST';
TABLESPACE FREE BYTES PCT_FREE ---------- --------- ----------- ----------- TBS_TEST 4194304 10485760 40
6、声明一个TASK
SQL>DECLARE
tname VARCHAR2(128) := 'my_seg.task3';
tname_desc VARCHAR2(128) := 'Gec shrink advice for segments in USERS';
task_id NUMBER;
object_id NUMBER;
objeccname VARCHAR2 (100);
objectcype VARCHAR2 (100);
BEGIN
dbms_advisor.create_task('Segment Advisor', task_id, tname, tname_desc, NULL);
dbms_advisor.create_object(tname, 'TABLESPACE', 'TBS_TEST',' ',' ',NULL,' ',object_id);
END;
/
如果有重复，可以先删除任务
SQL>begin
dbms_advisor.delete_task('my_seg.task3');
end;
/
7、执行这个task
SQL>DECLARE tname VARCHAR2(128) := 'my_seg.task3'; BEGIN dbms_advisor.EXECUTE_TASK(tname); END; /
8、获取优化建议结果
SQL>SElECT DESCRIPTION FROM dba_advisor_tasks WHERE TASK_NAME='my_seg.task3'; SQL>select attr1,attr2,message from dba_advisor_findings f,dba_advisor_objects o where f.task_name=o.task_name and f.object_id=o.object_id and f.task_name='my_seg.task3';
9、得到优化语句
SQL> select tablespace_name, segment_name, segment_type, recommendations, c1 from table( dbms_space . asa_recommendations( 'TRUE '， 'TRUE', 'FALSE')) ;
10\
>select blocks from user_segments where segment_name= 'T_TEST' ;
BLOCKS
----------
896
10、空间收缩
（1）开启表的行迁移功能
SQL>alter table test.t_test enable row movement;
（2）开始压缩
分两个步骤，迁移和压缩
SQL>alter table test.t_test shrink space;
（3）查看压缩后块数量
SQL>select blocks from user_segments where segment_name= 'T_TEST' ; BLOCKS ---------- 880
（4）关闭行迁移
SQL>alter table test.t_test disable row movement;
