Oracle 12.2 使用聯機重定義對錶進行多處改變

下面的例子將演示如何使用聯機重定義操作來對錶進行多處改變，原始表jy.original的建立語句如下:

SQL> create table jy.original(
  2  col1 number primary key,
  3  col2 varchar2(10),
  4  col3 clob,
  5  col4 date)
  6  organization index;

Table created.

表jy.original將按以下規則進行重定義:
.表啟用高階行壓縮方法進行壓縮
.LOB列將被改變為SecureFiles LOB儲存
.表的儲存表空間將由test改變為example，並且表的塊大小由8KB改變為 16KB。
.表將基於col1列進行分割槽
.將增加列col5
.列col2將被刪除
.列col3與col4會被重新命名，並且它們的位置會發生改變
.列col3的資料型別將從date改變為timestamp
.表將由索引組織表改變為堆表
.表的碎片將會被整理

為了演示碎片整理，使用下面的語句來向表載入資料:

SQL> declare
  2  v_clob clob;
  3  begin
  4   for i in 0..999 loop
  5    v_clob := null;
  6    for j in 1..1000 loop
  7     v_clob := v_clob||to_char(i,'0000');
  8    end loop;
  9    insert into jy.original values(i,to_char(i),v_clob,sysdate+i);
 10    commit;
 11   end loop;
 12   commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

執行下面的語句來使用表被碎片化

SQL> delete from jy.original where (col1/3) <> trunc(col1/3);

666 rows deleted.

SQL> commit;

Commit complete.

SQL> set serveroutput on;
SQL> declare
  2      l_fs1_bytes number;
  3      l_fs2_bytes number;
  4      l_fs3_bytes number;
  5      l_fs4_bytes number;
  6      l_fs1_blocks number;
  7      l_fs2_blocks number;
  8      l_fs3_blocks number;
  9      l_fs4_blocks number;
 10      l_full_bytes number;
 11      l_full_blocks number;
 12      l_unformatted_bytes number;
 13      l_unformatted_blocks number;
 14  begin
 15      dbms_space.space_usage(
 16           segment_owner      => 'JY',
 17           segment_name       => 'ORIGINAL',
 18           segment_type       => 'TABLE',
 19           fs1_bytes          => l_fs1_bytes,
 20           fs1_blocks         => l_fs1_blocks,
 21           fs2_bytes          => l_fs2_bytes,
 22           fs2_blocks         => l_fs2_blocks,
 23           fs3_bytes          => l_fs3_bytes,
 24           fs3_blocks         => l_fs3_blocks,
 25           fs4_bytes          => l_fs4_bytes,
 26           fs4_blocks         => l_fs4_blocks,
 27           full_bytes         => l_full_bytes,
 28           full_blocks        => l_full_blocks,
 29           unformatted_blocks => l_unformatted_blocks,
 30           unformatted_bytes  => l_unformatted_bytes
 31            );
 32
 33        dbms_output.put_line('0-25% free = '||l_fs1_blocks||' and bytes = '||l_fs1_bytes);
 34        dbms_output.put_line('25-50% free = '||l_fs2_blocks||' and bytes = '||l_fs2_bytes);
 35        dbms_output.put_line('50-75% free = '||l_fs3_blocks||' and bytes = '||l_fs3_bytes);
 36        dbms_output.put_line('75-100% free = '||l_fs4_blocks||' and bytes = '||l_fs4_bytes);
 37        dbms_output.put_line(' full blocks = '||l_full_blocks||' and bytes = '||l_full_bytes);
 38  end;
 39  /
0-25% free = 0 and bytes = 0
25-50% free = 3 and bytes = 24576
50-75% free = 0 and bytes = 0
75-100% free = 0 and bytes = 0
full blocks = 10 and bytes = 81920

PL/SQL procedure successfully completed.

1.用要執行聯機重定義操作的使用者登入資料庫

SQL> conn jy/jy@jypdb
Connected.

2.驗證原始表是否可以執行聯機重定義

SQL> begin
  2  dbms_redefinition.can_redef_table(
  3  uname => 'jy',
  4  tname => 'original',
  5  options_flag => dbms_redefinition.cons_use_pk);
  6  end;
  7  /

PL/SQL procedure successfully completed.

3.建立中間表jy.int_original

SQL> create table jy.int_original(
  2  col1 number,
  3  col3 timestamp,
  4  col4 clob,
  5  col5 varchar2(3))
  6  lob(col4) store as securefile (nocache filesystem_like_logging)
  7  partition by range (col1) (
  8  partition par1 values less than (333),
  9  partition par2 values less than (666),
 10  partition par3 values less than (maxvalue))
 11  tablespace example
 12  row store compress advanced;

Table created.

4.開始聯機重定義操作

SQL> begin
  2  dbms_redefinition.start_redef_table(
  3  uname => 'jy',
  4  orig_table => 'original',
  5  int_table => 'int_original',
  6  col_mapping => 'col1 col1, to_timestamp(col4) col3, col3 col4',
  7  options_flag => dbms_redefinition.cons_use_pk);
  8  end;
  9  /

PL/SQL procedure successfully completed.

5.複製依賴物件

SQL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents(
  5  uname => 'jy',
  6  orig_table => 'original',
  7  int_table => 'int_original',
  8  copy_indexes => dbms_redefinition.cons_orig_params,
  9  copy_triggers => true,
 10  copy_constraints => true,
 11  copy_privileges => true,
 12  ignore_errors => true,
 13  num_errors => num_errors);
 14  end;
 15  /

PL/SQL procedure successfully completed.

6.可選操作同步中間表

SQL> begin
  2  dbms_redefinition.sync_interim_table(
  3  uname => 'jy',
  4  orig_table => 'original',
  5  int_table => 'int_original');
  6  end;
  7  /

PL/SQL procedure successfully completed.

7.完成聯機重定義操作