Shulou(Shulou.com)05/31 Report--

Editor to share with you how Oracle 12.2 uses online redefinition to make many changes to the table. I hope you will gain something after reading this article. Let's discuss it together.

The following example shows how to use the online redefinition operation to make multiple changes to the table. The original table jy.original is created with the following statement:

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.

Table jy.original will be redefined according to the following rules:

. Table enables advanced row compression method for compression

The .lob column will be changed to SecureFiles LOB storage

. The storage table space of the table will be changed from test to example, and the block size of the table will be changed from 8KB to 16KB.

. Tables will be partitioned based on col1 columns

. The column col5 will be added

. Column col2 will be deleted

. The columns col3 and col4 will be renamed and their positions will change

. The data type of column col3 will be changed from date to timestamp

. The table will be changed from index organization table to heap table

. The fragments of the table will be sorted out.

To demonstrate defragmentation, use the following statement to load data into the table:

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 (iQuinchar (I)); 8 end loop; 9 insert into jy.original values (iQuinchar (I), vicious Clob Magnum sysdatedatei); 10 commit; 11 end loop; 12 commit; 13 end; 14 / PL/SQL procedure successfully completed.

Execute the following statement to use the fragmentation of the table

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. Log in to the database with the user to perform the online redefinition operation

SQL > conn jy/jy@jypdbConnected.

two。 Verify that the original table can perform online redefinition

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. Create an intermediate table 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), 10 partition par3 values less than (maxvalue)) 11 tablespace example 12 row store compress advanced;Table created.

4. Start an online redefine operation

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. Copy dependent objects

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. Optional operation to synchronize intermediate tables

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. Complete the online redefinition operation

After reading this article, I believe you have a certain understanding of "Oracle 12.2 how to use online redefinition to make multiple changes to the table". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.