下面的例子将演示如何使用联机重定义操作来对表进行多处改变，原始表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.完成联机重定义操作
SQL> begin 2 dbms_redefinition.finish_redef_table( 3 uname => 'jy', 4 orig_table => 'original', 5 int_table => 'int_original'); 6 end; 7 / PL/SQL procedure successfully completed.