Khi delete, truncate table dung lượng bảng thay đổi như thế nào?

Câu hỏi: Khi delete, truncate table thì dung lượng thay đổi như thế nào?

Trả lời:

Có một vài lựa chọn của truncate, với các tùy chọn truncate sẽ ảnh hưởng đến đến không gian lưu trữ được cấp phát và HWM (High Water Mark)

• delete để lại tất cả không gian được phân bổ cho bảng. HWM vẫn giữ nguyên
• truncate table ... reuse storage để lại tất cả không gian được phân bổ cho bảng. Nhưng HWM về mức đầu tiên
• truncate table ... drop storage (mặc định) giải phóng tất cả không gian phía trên minextents của bảng. Và do đó đặt lại mốc HWM
• truncate table ... drop all storage giải phóng tất cả không gian được phân bổ  từ bảng

Ví dụ 1: Bạn có thể xác minh điều này bằng dbms_space.unused_space:

create table t as 
  select level id, lpad ( 'x', 1000, 'x' ) stuff 
  from   dual
  connect by level <= 1000;

create or replace procedure show_size as

   out_total_blocks               integer;
   out_total_bytes                integer;
   out_unused_blocks              integer;
   out_unused_bytes               integer;
   out_last_used_extent_file_id   integer;
   out_last_used_extent_block_id  integer;
   out_last_used_block            integer;

begin

  dbms_space.unused_space(
    segment_owner              =>  user
    ,segment_name              =>  'T'
    ,segment_type              =>  'TABLE'
    ,total_blocks              =>  out_total_blocks
    ,total_bytes               =>  out_total_bytes 
    ,unused_blocks             =>  out_unused_blocks
    ,unused_bytes              =>  out_unused_bytes
    ,last_used_extent_file_id  =>  out_last_used_extent_file_id
    ,last_used_extent_block_id =>  out_last_used_extent_block_id
    ,last_used_block           =>  out_last_used_block
  );

  dbms_output.put_line ( 'Total Blocks:    ' ||  out_total_blocks || ' (blocks in the segment)');
  dbms_output.put_line ( 'Total Bytes:     ' ||  out_total_bytes || ' (segment size in bytes)');
  dbms_output.put_line ( 'Unused Blocks:   ' ||  out_unused_blocks || ' (empty blocks)');
  dbms_output.put_line ( 'Unused Bytes:    ' ||  out_unused_bytes || ' (size of empty space in bytes)');
  dbms_output.put_line ( 'Last Used Block: ' ||  out_last_used_block || ' (high water mark)');
end;
/ 

exec show_size;

Total Blocks:    256 (blocks in the segment)
Total Bytes:     2097152 (segment size in bytes)
Unused Blocks:   101 (empty blocks)
Unused Bytes:    827392 (size of empty space in bytes)
Last Used Block: 27 (high water mark)

delete t;
commit;

exec show_size;

Total Blocks:    256 (blocks in the segment)
Total Bytes:     2097152 (segment size in bytes)
Unused Blocks:   101 (empty blocks)
Unused Bytes:    827392 (size of empty space in bytes)
Last Used Block: 27 (high water mark)

truncate table t
  reuse storage;

exec show_size;

Total Blocks:    256 (blocks in the segment)
Total Bytes:     2097152 (segment size in bytes)
Unused Blocks:   253 (empty blocks)
Unused Bytes:    2072576 (size of empty space in bytes)
Last Used Block: 3 (high water mark)

truncate table t
  drop storage;

exec show_size;

Total Blocks:    8 (blocks in the segment)
Total Bytes:     65536 (segment size in bytes)
Unused Blocks:   5 (empty blocks)
Unused Bytes:    40960 (size of empty space in bytes)
Last Used Block: 3 (high water mark)

truncate table t
  drop all storage;

exec show_size;

Total Blocks:    0 (blocks in the segment)
Total Bytes:     0 (segment size in bytes)
Unused Blocks:   0 (empty blocks)
Unused Bytes:    0 (size of empty space in bytes)
Last Used Block: 0 (high water mark)

Chú ý: Từ phiên bản 11g R2 và sau, có option  DROP ALL STORAGE

Ví dụ 2: