Oracle 12.2如何使用联机重定义对表进行多处改变

小编给大家分享一下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 = 025-50% free = 3 and bytes = 2457650-75% free = 0 and bytes = 075-100% free = 0 and bytes = 0full blocks = 10 and bytes = 81920PL/SQL procedure successfully completed.

1.用要执行联机重定义操作的用户登录数据库

SQL> conn jy/jy@jypdbConnected.

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.完成联机重定义操作

看完了这篇文章，相信你对"Oracle 12.2如何使用联机重定义对表进行多处改变"有了一定的了解，如果想了解更多相关知识，欢迎关注行业资讯频道，感谢各位的阅读！