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

最新推荐文章于 2024-11-01 12:20:35 发布

cnje5551

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量107

点赞数

文章标签：数据库

下面的例子将演示如何使用联机重定义操作来对表进行多处改变，原始表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.完成联机重定义操作

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-2142206/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-2142206/