shrink的语法及如何降低高水位线

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