alter table tbname row movement;
保持高水位:
alter table tbname shrink space compact;
回缩表和hwm:
alter table my_t shrink space;
回缩表与相关索引
alter table tbname shrink space cascade;
回缩素引:
alter index idxname shrink space;---->assm (注意索引表空间也要是assm)从10g开始,oracle开始提供shrink 命令,assm ,假如我们的表空间不支持assm就不能使用shrink缩小段
降低高水位线
SQL> create tablespace sk datafile '/u01/app/oracle/oradata/pod/sk.dbf' size 20m segment space management manual;
Tablespace created.
SQL> select TABLESPACE_NAME,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
TEMP LOCAL MANUAL
USERS LOCAL AUTO
UNDOTBS2 LOCAL MANUAL
D3 LOCAL AUTO
D4 LOCAL AUTO
DD5 LOCAL MANUAL
SOLO LOCAL AUTO
TEMP1 LOCAL MANUAL
TEMP2 LOCAL MANUAL
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
TEMP5 LOCAL MANUAL
UNDO1 LOCAL MANUAL
BIGTSB_01 LOCAL AUTO
TBS_02 LOCAL AUTO
TBS_03 LOCAL AUTO
TBS_3 LOCAL AUTO
TBS_4 LOCAL AUTO
TBS_5 LOCAL AUTO
TBS_6 LOCAL AUTO
TEMP6 LOCAL MANUAL
TEMP_251 LOCAL AUTO
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SKY LOCAL AUTO
TT1_E1 LOCAL AUTO
TT1_E2 LOCAL AUTO
TEST LOCAL AUTO
PERFSTAT LOCAL AUTO
SK LOCAL MANUAL
28 rows selected.
注意sk tablespace 不能使用Shrink 正和碎片的
SQL> create tablespace sk2 datafile '/u01/app/oracle/oradata/pod/sk2.dbf' size 20m;
Tablespace created.
SQL> create table t_sk2 tablespace sk2 as select * from dba_objects;
Table created.
SQL> show user
USER is "SYS"
SQL> select count(*) from t_sk2;
COUNT(*)
----------
49372
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> delete from t_sk2 where object_name like '%T%';
16684 rows deleted.
SQL> delete from t_sk2 where object_name like '%U%';
4092 rows deleted.
SQL> set serveroutput on
SQL> exec p_unused_space('t_sk2','table','sys');
total_blocks is 768
total_bytes is 6291456
unused_blocks is 69
unused_bytes is 565248
last_used_extent_file_id is 6
last_used_extent_block_id is 649
last_used_block is 59
PL/SQL procedure successfully completed.
hwm=768-69-1=698
SQL> delete from t_sk2 where object_name like '%R%';
4273 rows deleted.
SQL> delete from t_sk2 where object_name like '%L%';
4130 rows deleted.
SQL> exec p_unused_space('t_sk2','table','sys');
total_blocks is 768
total_bytes is 6291456
unused_blocks is 69
unused_bytes is 565248
last_used_extent_file_id is 6
last_used_extent_block_id is 649
last_used_block is 59
PL/SQL procedure successfully completed.
SQL> alter table t_sk2 enable row movement;
Table altered.
SQL> alter table t_sk2 shrink space;
Table altered.
SQL> exec p_unused_space('t_sk2','table','sys');
total_blocks is 272
total_bytes is 2228224
unused_blocks is 1
unused_bytes is 8192
last_used_extent_file_id is 6
last_used_extent_block_id is 265
last_used_block is 15
PL/SQL procedure successfully completed.
hwm=272-1-1=270
日志挖掘:
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/pod/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
可以看出shrink操作的整个过程都是在作delete,insert操作,与move操作比起来,shrink日志写的大很多
SQL> col name format a30;
SQL> select rowid ,t.* from t2_sk t;
ROWID ID NAME
------------------ ---------- ------------------------------
AAAPXCAAGAAAAEfAAA 11 aa
AAAPXCAAGAAAAEfAAB 22 bb
AAAPXCAAGAAAAEfAAC 33 cc
AAAPXCAAGAAAAEfAAD 44 dd
AAAPXCAAGAAAAEfAAE 55 ee
shrink 几个问题:
1. shrink 后index 是否需要rebuild
因为shrink的操作改变了行数据的rowid,那么如果table上有index,shrink table 后index ---?
结论: valid ---->index 一起维护了
SQL> create table my_t tablespace sk2 as select * from all_objects;
Table created.
SQL> create index i_my on my_t(object_id);
Index created.
SQL> delete from my_t where object_name like '%C%';
17042 rows deleted.
SQL> commit;
Commit complete.
SQL> select index_name,status from user_indexes where index_name='I_MY';
INDEX_NAME STATUS
------------------------------ --------
I_MY VALID
SQL> ALTER table my_t enable row movement;
Table altered.
SQL> alter table my_t shrink space;
Table altered.
SQL> select index_name,status from user_indexes where index_name='I_MY';
INDEX_NAME STATUS
------------------------------ --------
I_MY VALID
SQL> set autotrace on
SQL>
SQL> select * from my_t where object_id=20;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 987734033
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 128 | 1 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_T | 1 | 128 | 1 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_MY | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
369 recursive calls
0 db block gets
128 consistent gets
49 physical reads
0 redo size
995 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed