下面的例子將演示如何使用聯機重定義操作來對表進行多處改變，原始表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批量联机,Oracle 12.2 使用联机重定义对表进行多处改变的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。