shrink怎样回收分区表碎片

shrink怎样回收分区表碎片，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法免费云主机域名。 实验如下：

CREATE TABLE “SCOTT”.”T4″
( “A” NUMBER,
“B” NUMBER
)
PARTITION BY RANGE (“A”)
(PARTITION “PART1” VALUES LESS THAN (10),
PARTITION “PART2” VALUES LESS THAN (20) ) ;

begin
for v1 in 1..19
loop
insert into scott.t4 values(v1,dbms_random.value(1,100000));
commit;
end loop;
end;
/
INSERT INTO scott.T4 SELECT * FROM sT4;

SQL> CREATE TABLE “SCOTT”.”T4″
( “A” NUMBER,
“B” NUMBER
2 3 4 )
5 PARTITION BY RANGE (“A”)
6 (PARTITION “PART1” VALUES LESS THAN (10),
7 PARTITION “PART2” VALUES LESS THAN (20) ) ;

Table created.

SQL> begin
2 for v1 in 1..19
3 loop
4 insert into scott.t4 values(v1,dbms_random.value(1,100000));
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>

SQL> conn scott/tiger;
Connected.
SQL> INSERT INTO T4 SELECT * FROM T4;

19 rows created.

省略………

SQL> INSERT INTO T4 SELECT * FROM T4;

77824 rows created.

SQL> INSERT INTO T4 SELECT * FROM T4;

155648 rows created.

SQL> INSERT INTO T4 SELECT * FROM T4;

311296 rows created.

SQL> commit;

Commit complete.

SQL> commit;

Commit complete.

SQL> select count(*) from t4;

COUNT(*)
———-
622592

SQL>

–sys用户执行查询表大小及碎片
SQL> col SEGMENT_NAME for a15
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
—————————— ————— —————————— —————— ———-
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16

SQL>
SQL> set lines 200
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables
7 where table_name = ‘&table_name’ and owner=’&owner’;
Enter value for table_name: T4
Enter value for owner: SCOTT
old 7: where table_name = ‘&table_name’ and owner=’&owner’
new 7: where table_name = ‘T4′ and owner=’SCOTT’

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4

SQL>

–分析表：
SQL> analyze table scott.T4 compute statistics;

Table analyzed.

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name=’T4′;

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4 622592 16.625 31.46875 14.84375

SQL>

–删除表数据：
SQL> delete scott.t4 where rownum

599999 rows deleted.

SQL>

–再查看表发小及碎片情况：
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
—————————— ————— —————————— —————— ———-
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16

SQL>
SQL>
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name=’T4′;

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4 622592 16.625 31.46875 14.84375

SQL>

–再次分析一下表
SQL> analyze table scott.T4 compute statistics;

Table analyzed.

SQL>
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
—————————— ————— —————————— —————— ———-
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16

SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name=’T4′;

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4 22593 .603298187 31.46875 30.8654518

SQL>

发现使劲的表大小已经变为0.603298187MB了.

–使用dbms_stat包分析分区表，如下：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> ‘SCOTT’,TABNAME=> ‘T4’,METHOD_OPT=> ‘for all indexed columns size auto’,CASCADE=> TRUE,ESTIMATE_PERCENT=> 100);

PL/SQL procedure successfully completed.

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
—————————— ————— —————————— —————— ———-
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16

SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name=’T4′;

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4 22593 .538659096 31.46875 30.9300909

结果同上。

–查询每个分区的碎片情况；
SQL> select t3.table_name, t3.partition_name,t3.num_rows * t3.AVG_ROW_LEN / 1024 / 1024 actual_mb,T3.blocks * 8 / 1024 high_water_mb,(blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024 ) need_recovery_mb,t3.empty_blocks,t3.last_analyzed from dba_tab_partitions t3 where t3.table_name =’T4′;

TABLE_NAME PARTITION_NAME ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB EMPTY_BLOCKS LAST_ANALYZED
—————————— —————————— ———- ————- —————- ———— ——————-
T4 PART1 0 15.734375 15.734375 34 2017-10-18 06:06:49
T4 PART2 .538659096 15.734375 15.1957159 34 2017-10-18 06:06:49

SQL>

–进行shrink表t4（整个分区shrink，当然也可以针对子分区shrink,例如alter table PTABLE MODIFY PARTITION PTABLE_P2 shrink space）

SQL> alter table scott.t4 enable row movement;

Table altered.

SQL>
SQL> alter table scott.t4 shrink space cascade;

Table altered.

SQL> alter table scott.t4 disable row movement;

Table altered.

–查看表大小及碎片情况：
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
—————————— ————— —————————— —————— ———-
SCOTT T4 PART1 TABLE PARTITION .1875
SCOTT T4 PART2 TABLE PARTITION .8125

SQL>
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name=’T4′;

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4 22593 .538659096 31.46875 30.9300909

SQL>

–再次收集统计信息：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> ‘SCOTT’,TABNAME=> ‘T4’,METHOD_OPT=> ‘for all indexed columns size auto’,CASCADE=> TRUE,ESTIMATE_PERCENT=> 100);

PL/SQL procedure successfully completed.

SQL>
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME=’T4′;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
—————————— ————— —————————— —————— ———-
SCOTT T4 PART1 TABLE PARTITION .1875
SCOTT T4 PART2 TABLE PARTITION .8125

SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name=’T4′;

TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
—————————— ———- ———- ————- —————-
T4 22593 .538659096 .6640625 .125403404

–同时查看各子分区情况：
SQL> select t3.table_name, t3.partition_name,t3.num_rows * t3.AVG_ROW_LEN / 1024 / 1024 actual_mb,T3.blocks * 8 / 1024 high_water_mb,(blocks * 8 / 1024 – avg_row_len * num_rows / 1024 / 1024 ) need_recovery_mb,t3.empty_blocks,t3.last_analyzed from dba_tab_partitions t3 where t3.table_name =’T4′;

TABLE_NAME PARTITION_NAME ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB EMPTY_BLOCKS LAST_ANALYZED
—————————— —————————— ———- ————- —————- ———— ——————-
T4 PART1 0 .0078125 .0078125 34 2017-10-18 06:15:37
T4 PART2 .538659096 .65625 .117590904 34 2017-10-18 06:15:37

关于shrink怎样回收分区表碎片问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注百云行业资讯频道了解更多相关知识。

相关推荐: linux7安装rac11.2.0.4 安装数据库软件选择不到节点

解决方式如下：[root@ora02std ContentsXML]# pwd/u01/app/oraInventory/ContentsXML[root@ora02std ContentsXML]#[root@ora0免费云主机域名2std Contents…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。