共计 4008 个字符，预计需要花费 11 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 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. 完成联机重定义操作
看完了这篇文章，相信你对“Oracle 12.2 如何使用联机重定义对表进行多处改变”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
正文完
