Oracle 10g开始采用了shrink技术
Shrink是通过事务的方式将数据行从一个数据块转移到另一个数据块。收缩过程中，表仍然可以进行DML操作
当然，事务要能够进行DML操作，还是需要等待收缩引起的事务锁释放。
收缩虽然是事务，但是数据并没有发生变化，因此不会引起触发器的触发。
使用shrink的前提条件
1、表所在的表空间必须使用ASSM（自动段空间管理）
2、在收缩表上必须启用row movement选项
建立一张表
SQL> create table test_shrik (id number ) tablespace users;
Table created.
查看各个表空间管理信息
SQL> select tablespace_name,t.segment_space_management
2 from dba_tablespaces t
3 where tablespace_name in ('SYSTEM','USERS');
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
USERS AUTO
插入数据
SQL> insert into test_shrik
2 select rownum from dual connect by level <=200000;
200000 rows created.
SQL> commit ;
Commit complete.
查看统计信息
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS
2 from user_tables t
3 where table_name = upper('test_shrik');
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
TEST_SHRIK
收集统计信息
SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');
PL/SQL procedure successfully completed.
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS
2 from user_tables t
3 where table_name = upper('test_shrik');
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
TEST_SHRIK 306 0 197855
删除数据
SQL> delete from test_shrik where id >=100000 ;
100001 rows deleted.
SQL> commit ;
Commit complete.
查看统计信息
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS
2 from user_tables t
3 where table_name = upper('test_shrik');
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
TEST_SHRIK 306 0 197855
再次收集，查看信息
SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');
PL/SQL procedure successfully completed.
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS
2 from user_tables t
3 where table_name = upper('test_shrik');
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
TEST_SHRIK 306 0 101835
启用表的行转移功能
SQL> alter table test_shrik enable row movement ;
Table altered.
压缩高水位线
SQL> alter table test_shrik shrink space ;
Table altered.
查看统计信息
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS
2 from user_tables t
3 where table_name = upper('test_shrik');
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
TEST_SHRIK 306 0 101835
收集统计信息
SQL> exec dbms_stats.gather_table_stats('sys','test_shrik');
PL/SQL procedure successfully completed.
再次查看
SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS
2 from user_tables t
3 where table_name = upper('test_shrik');
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
TEST_SHRIK 151 0 99344
高水位线已经下降了
其他说明
第一次做这个实验的时候并没有成功。
shrink space报ora-10635
ORA-10635: Invalid segment or tablespace type
Cause: Cannot shrink the segment because it is not in auto segment space managed tablespace or it is not a data, index or lob segment.
Action: Check the tablespace and segment type and reissue the statement
原因：表建立在了system表空间。
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
而system表空间的段管理方式为手动。不满足shrink的要求。
shrink_clause 允许手动地释放表、索引组织表、索引、分区、物化视图和物化视图日志的空间。
这个语句只对自增长的表空间起作用。
这个语句起作用的前提是row movement。所以，你必须先enable row movement。
另外，如果你在表上有行级的触发器，你必须先disable掉触发器才行。
shrink_clause的限制条件
========================================
1. 对cluster，cluster table，或具有Long类型列的对象 不起作用。
2. 不支持具有function-based indexes 或 bitmap join indexes的表
3. 不支持mapping 表或index-organized表。
4. 不支持compressed 表
其他压缩高水位线的方法
Oracle10g之前采用了
move、exp/imp
以前方法的缺点
1 需要停掉业务
2 需要重建索引
下面语句来自
http://yangtingkun.itpub.net/post/468/11764
关于row movement 的副作用
可以看到，一个UPDATE语句被ORACLE拆成了三条语句。首先是对当前的记录进行更新（指定ROWID），第二步删除指定ROWID的记录，第三步用更新后的值重新插入记录。执行完第三步，新的记录已经插入到分区P2中了
如果按照相反的顺序执行SQL_UNDO，却发 现无法恢复到以前的状态。首先删除分区P2中的记录，这没有问题。第二步，插入新的记录，这里已经有问题了，插入时无法指定ROWID，记录仍然会插入到 P2分区中。最后进行更新，这是指定的ROWID是P1分区中的ROWID，这个ROWID在当前根本就不存在，因此，已经无法恢复到原来的状态了。
官方文档的说明：
Shrinking Database Segments Online
You use online segment shrink to reclaim fragmented free space below the high water
mark in an Oracle Database segment. The benefits of segment shrink are these:
■ Compaction of data leads to better cache utilization, which in turn leads to better
online transaction processing (OLTP) performance.
■ The compacted data requires fewer blocks to be scanned in full table scans, which
in turns leads to better decision support system (DSS) performance.
Segment shrink is an online, in-place operation. DML operations and queries can be
issued during the data movement phase of segment shrink. Concurrent DML
operation are blocked for a short time at the end of the shrink operation, when the
space is deallocated. Indexes are maintained during the shrink operation and remain
usable after the operation is complete. Segment shrink does not require extra disk
space to be allocated.
Segment shrink reclaims unused space both above and below the high water mark. In
contrast, space deallocation reclaims unused space only above the high water mark. In
shrink operations, by default, the database compacts the segment, adjusts the high
water mark, and releases the reclaimed space.
Segment shrink requires that rows be moved to new locations. Therefore, you must
first enable row movement in the object you want to shrink and disable any
rowid-based triggers defined on the object.
Shrink operations can be performed only on segments in locally managed tablespaces
with automatic segment space management (ASSM). Within an ASSM tablespace, all
segment types are eligible for online segment shrink except these:
■ IOT mapping tables
■ Tables with rowid based materialized views
■ Tables with function-based indexes
Invoking Online Segment Shrink
Before invoking online segment shrink, view the findings and recommendations of the
Segment Advisor. For more information, see "Using the Segment Advisor" on
page 14-16.
You invoke online segment shrink with Enterprise Manager (EM) or with SQL
commands in SQL*Plus. The remainder of this section discusses the command line
method.
You can shrink space in a table, index-organized table, index, partition, subpartition,
materialized view, or materialized view log. You do this using ALTER TABLE, ALTER
INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement
with the SHRINK SPACE clause. Refer to Oracle Database SQL Reference for the syntax
and additional information on shrinking a database object, including restrictions.
Two optional clauses let you control how the shrink operation proceeds:
■ The COMPACT clause lets you divide the shrink segment operation into two phases.
When you specify COMPACT, Oracle Database defragments the segment space and
compacts the table rows but postpones the resetting of the high water mark and
the deallocation of the space until a future time. This option is useful if you have
long-running queries that might span the operation and attempt to read from
blocks that have been reclaimed. The defragmentation and compaction results are
saved to disk, so the data movement does not have to be redone during the second
phase. You can reissue the SHRINK SPACE clause without the COMPACT clause
during off-peak hours to complete the second phase.
The CASCADE clause extends the segment shrink operation to all dependent
segments of the object. For example, if you specify CASCADE when shrinking a
table segment, all indexes of the table will also be shrunk. (You need not specify
CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent
segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS
procedure of the DBMS_SPACE package.
As with other DDL operations, segment shrink causes subsequent SQL statements to
be reparsed because of invalidation of cursors unless you specify the COMPACT clause.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12474069/viewspace-620714/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12474069/viewspace-620714/
3513
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
