Oracle中shrinkspace命令详解

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理(ASSM),就可以使用这个特性缩小段，即降低HWM。这

从10g开始，Oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理(ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。

1 创建实验环境

1.1 创建ASSM的表空间

SQL> set serveroutput on

SQL> create tablespace ASSM datafile '/oradata/ltest/assm.dbf' size 10m autoextend on SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created

SQL> select tablespace_name,

2 block_size,

3 extent_management,

4 allocation_type,

5 segment_space_management

6 from dba_tablespaces

7 where tablespace_name = 'ASSM';

TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT

--------------- ---------- ----------------- --------------- ------------------------

ASSM 8192 LOCAL SYSTEM AUTO

1.2 建表

SQL> create table my_objects tablespace assm as select * from all_objects;

Table created

SQL> select count(*) from my_objects;

COUNT(*)

----------

49903

2 实验前的信息

SQL> exec show_space('MY_OBJECTS');

Total Blocks ..........................768

Total Bytes ..........................6291456

Total MBytes ..........................6

Unused Blocks ..........................62

Unused Bytes ..........................507904

Unused KBytes ..........................496

Last Used Ext FileId....................7

Last Used Ext BlockId...................649

Last Used Block.........................66

The segment is analyzed below

FS1 Blocks (0-25) ....................0

FS2 Blocks (25-50) ....................0

FS3 Blocks (50-75) ....................0

FS4 Blocks (75-100) ....................0

Unformatted Blocks ....................0

Full Blocks ....................686

PL/SQL procedure successfully completed