Shulou(Shulou.com)05/31 Report--

How to recycle partition table fragments by shrink, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The experiments are as follows:

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 ~ dbmsroom.value (1 ~ 100 000))

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 direction dbmsroom.value (1m 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.

Omit.

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 users execute queries about table size and fragmentation

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 >

-- Analytical table:

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'

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 622592 16.625 31.46875 14.84375

SQL >

-- delete table data:

SQL > delete scott.t4 where rownum

< 600000; 599999 rows deleted. SQL>

-- then check the size and fragmentation of the table:

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 >

-- analyze the table again

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 >

It is found that the size of the hard watch has changed to 0.603298187MB.

-- use dbms_stat package to analyze the partition table, as follows:

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME= > 'SCOTT',TABNAME= >' T4cm) > '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

The result is the same as above.

-- query the fragmentation of each partition

SQL > select t3.table_name, t3. Partitionalnameauthoring t3.numb 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 >

-- do the shrink table T4 (the entire partition shrink, of course, you can also point to the subpartition shrink, such as 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.

-- check the table size and fragmentation:

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 >

-- collect statistics again:

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME= > 'SCOTT',TABNAME= >' T4cm) > '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

-- check the status of each subpartition at the same time:

SQL > select t3.table_name, t3. Partitionalnameauthoring t3.numb 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

This is the answer to the question about how to recycle partition table fragments in shrink. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.