共計 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 行業資訊頻道，感謝各位的閱讀！
正文完
