Oracle中shrink space命令详解

简介:

从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

3 删除后的信息

然后我们随机地从table MY_OBJECTS中删除一部分数据:

 

SQL> delete from my_objects where object_name like '%C%';

17674 rows deleted

SQL> delete from my_objects where object_name like '%U%';

4687 rows deleted

SQL> delete from my_objects where object_name like '%A%';

7010 rows deleted

 

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)  ....................212

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

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

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

Full Blocks         ....................48

 

PL/SQL procedure successfully completed

 

这里,table my_objects的HWM下有706(768 - 62)个block,其中,free space为25-50%的block有205个,free space为50-75%的block有180个,free space为75-100%的block有229个,full space的block只有45个,这种情况下,我们需要对这个table的现有数据行进行重组。

4 shink操作

要使用assm上的shink,首先我们需要使该表支持行移动,可以用这样的命令来完成:

SQL> alter table my_objects enable row movement;

Table altered

现在,就可以来降低my_objects的HWM,回收空间了,使用命令:

SQL> alter table my_objects shrink space;

Table altered

 

SQL> exec show_space('MY_OBJECTS');

Total Blocks  ..........................280

Total Bytes   ..........................2293760

Total MBytes  ..........................2.1875

Unused Blocks ..........................5

Unused Bytes  ..........................40960

Unused KBytes ..........................40

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

Last Used Ext BlockId...................265

Last Used Block.........................19

 

The segment is analyzed below

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

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

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

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

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

Full Blocks         ....................259

 

PL/SQL procedure successfully completed

 

在执行玩shrink命令后,此时表my_objects的HWM现在降到了276(280 - 5 + 1)的位置,而且HWM下的block的空间使用状况,full space的block有259个,free space 为25-50% 和50-75% Block只有1个。

5 shrink space原理剖析

5.1 实验环境

SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;

Table created

SQL> insert into TEST_HWM values (1, 'aa');

1 row inserted

SQL> insert into TEST_HWM values (2, 'bb');

1 row inserted

SQL> insert into TEST_HWM values (3, 'cc');

1 row inserted

SQL> insert into TEST_HWM values (4, 'ds');

1 row inserted

SQL> insert into TEST_HWM values (5, 'dss');

1 row inserted

SQL> insert into TEST_HWM values (6, 'dss');

1 row inserted

SQL> insert into TEST_HWM values (7, 'ess');

1 row inserted

SQL> insert into TEST_HWM values (8, 'es');

1 row inserted

SQL> insert into TEST_HWM values (9, 'es');

1 row inserted

SQL> insert into TEST_HWM values (10, 'es');

1 row inserted

5.2 删除前rowid状态

SQL> select id,

  2         name,

  3         rowid,

  4         dbms_rowid.rowid_object(rowid) object_id,

  5         dbms_rowid.rowid_relative_fno(rowid) file_id,

  6         dbms_rowid.rowid_block_number(rowid) block_id,

  7         dbms_rowid.rowid_row_number(rowid) num

  8    from test_hwm;

 

    ID NAME  ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM

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

     1 aa    AAANAqAAHAAAAElAAA      53290          7        293          0

     2 bb    AAANAqAAHAAAAElAAB      53290          7        293          1

     3 cc    AAANAqAAHAAAAElAAC      53290          7        293          2

     4 ds    AAANAqAAHAAAAEmAAA      53290          7        294          0

     5 dss   AAANAqAAHAAAAEmAAB      53290          7        294          1

     6 dss   AAANAqAAHAAAAEmAAC      53290          7        294          2

     7 ess   AAANAqAAHAAAAEnAAA      53290          7        295          0

     8 es    AAANAqAAHAAAAEnAAB      53290          7        295          1

     9 es    AAANAqAAHAAAAEnAAC      53290          7        295          2

    10 es    AAANAqAAHAAAAEoAAA      53290          7        296          0

10 rows selected

5.3 删除后rowid状态

然后从table test_hwm中删除一些数据:

SQL> delete from TEST_HWM where id = 2;

1 row deleted

SQL> delete from TEST_HWM where id = 3;

1 row deleted

SQL> delete from TEST_HWM where id = 4;

1 row deleted

SQL> delete from TEST_HWM where id = 7;

1 row deleted

SQL> delete from TEST_HWM where id = 8;

1 row deleted

 

SQL> select id,

  2         name,

  3         rowid,

  4         dbms_rowid.rowid_object(rowid) object_id,

  5         dbms_rowid.rowid_relative_fno(rowid) file_id,

  6         dbms_rowid.rowid_block_number(rowid) block_id,

  7         dbms_rowid.rowid_row_number(rowid) num

  8    from test_hwm;

 

    ID NAME  ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM

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

     1 aa    AAANAqAAHAAAAElAAA      53290          7        293          0

     5 dss   AAANAqAAHAAAAEmAAB      53290          7        294          1

     6 dss   AAANAqAAHAAAAEmAAC      53290          7        294          2

     9 es    AAANAqAAHAAAAEnAAC      53290          7        295          2

    10 es    AAANAqAAHAAAAEoAAA      53290          7        296          0

 

从以上的信息,可知表test_hwm中,剩下的数据是分布在AAAAEl,AAAAEm,AAAAEn,AAAAEo这样四个连续的block中。

 

SQL> exec show_space('TEST_HWM');

Total Blocks  ..........................8

Total Bytes   ..........................65536

Total MBytes  ..........................0.0625

Unused Blocks ..........................0

Unused Bytes  ..........................0

Unused KBytes ..........................0

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

Last Used Ext BlockId...................289

Last Used Block.........................8

 

The segment is analyzed below

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

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

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

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

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

Full Blocks         ....................0

 

PL/SQL procedure successfully completed

 

通过show_space_assm我们可以看到目前这四个block的空间使用状况,AAAAEl,AAAAEn,AAAAEo上各有一行数据,可以猜测free space为50-75%的3个block是这三个block,那么free space为25-50%的1个block就是AAAAEm了,剩下free space为 75-100% 的3个block,是HWM下已格式化的尚未使用的block。

5.4 shrink后rowid状态

SQL> alter table my_objects enable row movement;

Table altered

SQL> alter table my_objects shrink space;

Table altered

 

SQL> select id,

  2         name,

  3         rowid,

  4         dbms_rowid.rowid_object(rowid) object_id,

  5         dbms_rowid.rowid_relative_fno(rowid) file_id,

  6         dbms_rowid.rowid_block_number(rowid) block_id,

  7         dbms_rowid.rowid_row_number(rowid) num

  8    from test_hwm;

 

    ID NAME  ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM

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

     9 es    AAANAqAAHAAAAEkAAA      53290          7        292          0

    10 es    AAANAqAAHAAAAEkAAB      53290          7        292          1

     1 aa    AAANAqAAHAAAAElAAA      53290          7        293          0

     5 dss   AAANAqAAHAAAAEmAAB      53290          7        294          1

     6 dss   AAANAqAAHAAAAEmAAC      53290          7        294          2

 

当执行了shrink操作后,可以发现shrink操作与move不太一样。在move操作的时候,所有行的rowid都发生了变化,table所位于的block的区域也发生了变化,但是所有行物理存储的顺序都没有发生变化,所以我们得到的结论是,oracle以block为单位,进行了block间的数据copy。而在shrink后,部分行数据的rowid发生了变化,同时,部分行数据的物理存储的顺序也发生了变化,而table所位于的block的区域却没有变化(ID为1,5,6的rowid没有发生变化,ID为9,10两行数据,原来在AAAAEn,AAAAEo上都移到AAAAEk上)。以上说明,shrink只移动了table其中一部分的行数据,来完成释放空间,而且,这个过程是在table当前所使用的block中完成的。

6 shrink的注意点

1. move时产生的日志比shrink时少.参看http://blog.csdn.net/huang_xw/article/details/7016365

2. shrink在移动行数据时,也一起维护了index上相应行的数据rowid的信息,当然shrink过程中用来维护index的成本也会比较高。而表move后index的状态是UNUSABLE的,需要进行rebuild。参见http://blog.csdn.net/huang_xw/article/details/7016415

3. oracle是从后向前移动行数据,那么,shrink的操作就不会像move一样,shrink不需要使用额外的空闲空间。



本文转自einyboy博客园博客,原文链接:http://www.cnblogs.com/einyboy/archive/2012/08/10/2631464.html,如需转载请自行联系原作者。


目录
相关文章
|
2月前
|
Oracle 关系型数据库 分布式数据库
PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
1月前
|
SQL Oracle 关系型数据库
探索 Linux 命令 `db_archive`:Oracle 数据库归档日志的工具
探索 Linux 中的 `db_archive`,实际与 Oracle 数据库归档日志管理相关。在 Oracle 中,归档日志用于恢复,当在线重做日志满时自动归档。管理员可使用 SQL*Plus 查看归档模式,通过 `RMAN` 进行备份和恢复操作。管理归档日志需谨慎,避免数据丢失。了解归档管理对 Oracle 管理员至关重要,确保故障时能快速恢复数据库。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SET命令:你的数据库会话“调色板”
【4月更文挑战第19天】Oracle SQL*Plus的SET命令是数据库会话的“调色板”,用于设置输出格式、反馈信息和各种偏好。它能调整PAGESIZE和LINESIZE以优化显示,控制ECHO和FEEDBACK开关以定制反馈,以及统计命令执行时间(TIMING)和调试SQL(VERIFY)。更高级的选项如HEADING和COLSEP可改善输出的可读性。通过灵活运用SET命令,能提升工作效率和体验,是数据库管理员和开发者的必备工具。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的HELP命令:你的数据库“百事通”
【4月更文挑战第19天】`Oracle SQL*Plus` 的 `HELP` 命令是数据库查询的强大工具,犹如“百事通”。在遇到困惑时,`HELP` 可提供详细命令解释和用法示例,解答基础到高级的 SQL 和 PL/SQL 疑问。它还是“活字典”,揭示命令关联与区别,如 `SET` 和 `ALTER SESSION`。此外,`HELP` 解释数据库概念,如“事务”,并支持模糊查询。无论新手还是专家,`HELP` 都是数据库探索的得力助手。
|
2月前
|
SQL Oracle 关系型数据库
Oracle spool格式化数据命令
在这个示例中,通过设置不同的 `SET`命令参数,你可以控制输出的格式,包括每页行数、每行字符数、列分隔符等。你也可以使用其他的 `SET`命令参数来进一步定制输出格式。
33 0
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的TTITLE和BTITLE命令:为你的数据报告加上精美的“画框”
【4月更文挑战第19天】`SQL*Plus`的`TTITLE`和`BTITLE`命令用于为数据报告添加吸引人的标题和边框。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的COLUMN命令:数据展示的“化妆师”
【4月更文挑战第19天】Oracle SQL*Plus的COLUMN命令是数据展示的利器,能美化和格式化输出。它可定制列标题、调整显示格式(如数字的小数位数和日期格式),添加前缀和后缀(如货币符号),以及控制列宽和是否折行,使得数据呈现更直观、专业。利用COLUMN命令,能将原始数据转化为易于理解和视觉吸引力强的展示形式。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SPOOL命令:数据库世界的“录像机”
【4月更文挑战第19天】`SQL*Plus`的`SPOOL`命令是Oracle数据库中的“录像机”,能记录所有操作和输出。它在用户开始“SPOOL ON”时启动,记录SQL查询、输出、错误信息等。完成后,“SPOOL OFF”停止记录并生成日志文件,便于回顾和检查。日志文件可自定义保存位置和命名,支持多文件录制,方便分类管理。无论数据分析、SQL脚本编写还是日常维护,`SPOOL`都是强大的工具,值得一试!
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的DESCRIBE命令:数据结构的“侦探”
【4月更文挑战第19天】`DESCRIBE`命令是Oracle SQL*Plus中的明星工具,用于快速揭示数据结构。它能显示表或视图的列名、数据类型、长度和空值限制,帮助用户理解数据库对象详情。此命令对数据库设计、查询优化和故障排除至关重要,且支持描述视图和同义词。通过有效利用DESCRIBE提供的信息,可以提升SQL查询效率,优化表结构,成为数据管理的得力助手。
|
10月前
|
SQL Oracle 关系型数据库
Oracle杂谈二 SQL*PLUS命令的使用大全
Oracle杂谈二 SQL*PLUS命令的使用大全
42 0

推荐镜像

更多