Shulou(Shulou.com)06/01 Report--

When you shrink a table, Oracle reorganizes the blocks with minimal storage space. Delete operation usually does not lower the high water mark. When scanning a full table, oracle scans blocks below the high water mark. A table may not have much data but takes a long time to execute. It is usually necessary to note that there are many free blocks at high water level. The occupied space can usually be freed by shrink or move the table.

The shrink table requires two steps: 1. Enable row migration; 2. Shrinkage table

Enable row migration

SQL > alter table t enable row movement

Shrinkage table

SQL > alter table t shrink space

Shrink tables and related indexes

SQL > alter table t shrink space cascade

Only shrink the meter, do not raise the water level

SQL > alter table t shrink space compact

Move table requires two steps: 1.move table; 2. Rebuild the relevant index

SQL > alter table t move tablespace users

SQL > alter index idx_t on t (id) rebuild online tablespace idx_tbs

You can use tom's show_space to check for the existence of empty blocks:

Create or replace procedure show_space (p_segname_1 in varchar2

P_owner_1 in varchar2 default user

P_type_1 in varchar2 default 'TABLE'

P_space in varchar2 default 'AUTO'

P_analyzed in varchar2 default'Y') as

P_segname varchar2 (100)

P_type varchar2 (10)

P_owner varchar2 (30)

L_unformatted_blocks number

L_unformatted_bytes number

L_fs1_blocks number

L_fs1_bytes number

L_fs2_blocks number

L_fs2_bytes number

L_fs3_blocks number

L_fs3_bytes number

L_fs4_blocks number

L_fs4_bytes number

L_full_blocks number

L_full_bytes number

L_free_blks number

L_total_blocks number

L_total_bytes number

L_unused_blocks number

L_unused_bytes number

L_LastUsedExtFileId number

L_LastUsedExtBlockId number

L_LAST_USED_BLOCK number

Procedure p (p_label in varchar2, p_num in number) is

Begin

Dbms_output.put_line (rpad (p_label, 40,'.') | | p_num)

End

Begin

P_segname: = upper (p_segname_1);-- rainy changed

P_owner: = upper (p_owner_1)

P_type: = p_type_1

If (p_type_1 ='i' or p_type_1 ='I') then

-- rainy changed

P_type: = 'INDEX'

End if

If (p_type_1 ='t'or p_type_1 ='T') then

-- rainy changed

P_type: = 'TABLE'

End if

If (p_type_1 ='c'or p_type_1 ='C') then

-- rainy changed

P_type: = 'CLUSTER'

End if

Dbms_space.unused_space (segment_owner = > p_owner)

Segment_name = > p_segname

Segment_type = > p_type

Total_blocks = > l_total_blocks

Total_bytes = > l_total_bytes

Unused_blocks = > l_unused_blocks

Unused_bytes = > l_unused_bytes

LAST_USED_EXTENT_FILE_ID = > l_LastUsedExtFileId

LAST_USED_EXTENT_BLOCK_ID = > l_LastUsedExtBlockId

LAST_USED_BLOCK = > l_LAST_USED_BLOCK)

If p_space = 'MANUAL' or (p_space' auto' and p_space 'AUTO') then

Dbms_space.free_blocks (segment_owner = > p_owner)

Segment_name = > p_segname

Segment_type = > p_type

Freelist_group_id = > 0

Free_blks = > l_free_blks)

P ('Free Blocks', l_free_blks)

End if

P ('Total Blocks', l_total_blocks)

P ('Total Bytes', l_total_bytes)

P ('Unused Blocks', l_unused_blocks)

P ('Unused Bytes', l_unused_bytes)

P ('LastUsedExtFileId', l_LastUsedExtFileId)

P ('LastUsedExtBlockId', l_LastUsedExtBlockId)

P ('Last Used Block', l_LAST_USED_BLOCK)

/ IF the segment is analyzed /

If p_analyzed ='Y' then

Dbms_space.space_usage (segment_owner = > p_owner)

Segment_name = > p_segname

Segment_type = > p_type

Unformatted_blocks = > l_unformatted_blocks

Unformatted_bytes = > l_unformatted_bytes

Fs1_blocks = > l_fs1_blocks

Fs1_bytes = > l_fs1_bytes

Fs2_blocks = > l_fs2_blocks

Fs2_bytes = > l_fs2_bytes

Fs3_blocks = > l_fs3_blocks

Fs3_bytes = > l_fs3_bytes

Fs4_blocks = > l_fs4_blocks

Fs4_bytes = > l_fs4_bytes

Full_blocks = > l_full_blocks

Full_bytes = > l_full_bytes)

Dbms_output.put_line (rpad (', 50,'*))

Dbms_output.put_line ('The segment is analyzed')

P ('0%-25% free space blocks', l_fs1_blocks)

P ('0%-25% free space bytes', l_fs1_bytes)

P ('25%-50% free space blocks', l_fs2_blocks)

P ('25%-50% free space bytes', l_fs2_bytes)

P ('50%-75% free space blocks', l_fs3_blocks)

P ('50%-75% free space bytes', l_fs3_bytes)

P ('75-100% free space blocks', l_fs4_blocks)

P ('75-100% free space bytes', l_fs4_bytes)

P ('Unused Blocks', l_unformatted_blocks)

P ('Unused Bytes', l_unformatted_bytes)

P ('Total Blocks', l_full_blocks)

P ('Total bytes', l_full_bytes)

End if

End

/

Usage reference:

Https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5350053031470

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.