ORACLE分区表转换在线重定义DBMS_REDEFINITION

CREATE OR REPLACE PACKAGE SYS.dbms_redefinition AUTHID CURRENT_USER IS

   ------------

   --  OVERVIEW

   --

   -- This package provides the API to perform an online, out-of-place

   -- redefinition of a table

   --- =========

   --- CONSTANTS

   --- =========

   -- Constants for the options_flag parameter of start_redef_table

   cons_use_pk    CONSTANT PLS_INTEGER := 1;

   cons_use_rowid CONSTANT PLS_INTEGER := 2;

   -- Constants used for the object types in the register_dependent_object

   cons_index      CONSTANT PLS_INTEGER := 2;

   cons_constraint CONSTANT PLS_INTEGER := 3;

   cons_trigger    CONSTANT PLS_INTEGER := 4;

   cons_mvlog      CONSTANT PLS_INTEGER := 10;

   -- constants used to specify the method of copying indexes

   cons_orig_params CONSTANT PLS_INTEGER := 1;

   PRAGMA SUPPLEMENTAL_LOG_DATA( default , AUTO_WITH_COMMIT);

   -- NAME:     can_redef_table - check if given table can be re-defined

   -- INPUTS:   uname        - table owner name

   --           tname        - table name

   --           options_flag - flag indicating user options to use

   --           part_name    - partition name

   PROCEDURE can_redef_table(uname        IN VARCHAR2,

                             tname        IN VARCHAR2,

                             options_flag IN PLS_INTEGER := 1,

                             part_name    IN VARCHAR2 := NULL );

   PRAGMA SUPPLEMENTAL_LOG_DATA(can_redef_table, NONE);

   -- NAME:     start_redef_table - start the online re-organization

   -- INPUTS:   uname        - schema name

   --           orig_table   - name of table to be re-organized

   --           int_table    - name of interim table

   --           col_mapping  - select list col mapping

   --           options_flag - flag indicating user options to use

   --           orderby_cols - comma separated list of order by columns

   --                          followed by the optional ascending/descending

   --                          keyword

   --           part_name    - name of the partition to be redefined

   PROCEDURE start_redef_table(uname        IN VARCHAR2,

                               orig_table   IN VARCHAR2,

                               int_table    IN VARCHAR2,

                               col_mapping  IN VARCHAR2 := NULL ,

                               options_flag IN BINARY_INTEGER := 1,

                               orderby_cols IN VARCHAR2 := NULL ,

                               part_name    IN VARCHAR2 := NULL );

   -- NAME:     finish_redef_table - complete the online re-organization

   -- INPUTS:   uname        - schema name

   --           orig_table   - name of table to be re-organized

   --           int_table    - name of interim table

   --           part_name    - name of the partition being redefined

   PROCEDURE finish_redef_table(uname          IN VARCHAR2,

                                orig_table     IN VARCHAR2,

                                int_table      IN VARCHAR2,

                                part_name      IN VARCHAR2 := NULL );

   -- NAME:     abort_redef_table - clean up after errors or abort the

   --                               online re-organization

   -- INPUTS:   uname        - schema name

   --           orig_table   - name of table to be re-organized

   --           int_table    - name of interim table

   --           part_name    - name of the partition being redefined

   PROCEDURE abort_redef_table(uname        IN VARCHAR2,

                               orig_table   IN VARCHAR2,

                               int_table    IN VARCHAR2,

                               part_name    IN VARCHAR2 := NULL );

   -- NAME:     sync_interim_table - synchronize interim table with the original

   --                                table

   -- INPUTS:   uname        - schema name

   --           orig_table   - name of table to be re-organized

   --           int_table    - name of interim table

   --           part_name    - name of the partition being redefined

   PROCEDURE sync_interim_table(uname       IN VARCHAR2,

                                orig_table  IN VARCHAR2,

                                int_table   IN VARCHAR2,

                                part_name   IN VARCHAR2 := NULL );

   -- NAME:     register_dependent_object - register dependent object

   --

   -- INPUTS:   uname        - schema name

   --           orig_table   - name of table to be re-organized

   --           int_table    - name of interim table

   --           dep_type     - type of the dependent object

   --           dep_owner    - name of the dependent object owner

   --           dep_orig_name- name of the dependent object defined on table

   --                          being re-organized

   --           dep_int_name - name of the corressponding dependent object on

   --                          the interim table

   PROCEDURE register_dependent_object(uname         IN VARCHAR2,

                                       orig_table    IN VARCHAR2,

                                       int_table     IN VARCHAR2,

                                       dep_type      IN PLS_INTEGER,

                                       dep_owner     IN VARCHAR2,

                                       dep_orig_name IN VARCHAR2,

                                       dep_int_name  IN VARCHAR2);

   -- NAME:     unregister_dependent_object - unregister dependent object

   --

   -- INPUTS:   uname        - schema name

   --           orig_table   - name of table to be re-organized

   --           int_table    - name of interim table

   --           dep_type     - type of the dependent object

   --           dep_owner    - name of the dependent object owner

   --           dep_orig_name- name of the dependent object defined on table

   --                          being re-organized

   --           dep_int_name - name of the corressponding dependent object on

   --                          the interim table

   PROCEDURE unregister_dependent_object(uname         IN VARCHAR2,

                                         orig_table    IN VARCHAR2,

                                         int_table     IN VARCHAR2,

                                         dep_type      IN PLS_INTEGER,

                                         dep_owner     IN VARCHAR2,

                                         dep_orig_name IN VARCHAR2,

                                         dep_int_name  IN VARCHAR2);

   --  NAME:     copy_table_dependents

   --

   --  INPUTS:  uname             - schema name

   --           orig_table        - name of table to be re-organized

   --           int_table         - name of interim table

   --           copy_indexes      - integer value indicating whether to

   --                               copy indexes

   --                               0 - don't copy

   --                               1 - copy using storage params/tablespace

   --                                   of original index

   --           copy_triggers      - TRUE implies copy triggers, FALSE otherwise

   --           copy_constraints   - TRUE implies copy constraints, FALSE

   --                                otherwise

   --           copy_privileges    - TRUE implies copy privileges, FALSE

   --                                otherwise

   --           ignore errors      - TRUE implies continue after errors, FALSE

   --                                otherwise

   --           num_errors         - number of errors that occurred while

   --                                cloning ddl

   --           copy_statistics    - TRUE implies copy table statistics, FALSE

   --                                otherwise.

   --                                If copy_indexes is 1, copy index

   --                                related statistics, 0 otherwise.

   --           copy_mvlog         - TRUE implies copy table's MV log, FALSE

   --                                otherwise.

   PROCEDURE copy_table_dependents(uname              IN   VARCHAR2,

                                   orig_table         IN   VARCHAR2,

                                   int_table          IN   VARCHAR2,

                                   copy_indexes       IN   PLS_INTEGER := 1,

                                   copy_triggers      IN   BOOLEAN := TRUE ,

                                   copy_constraints   IN   BOOLEAN := TRUE ,

                                   copy_privileges    IN   BOOLEAN := TRUE ,

                                   ignore_errors      IN   BOOLEAN := FALSE ,

                                   num_errors         OUT PLS_INTEGER,

                                   copy_statistics    IN   BOOLEAN := FALSE ,

                                   copy_mvlog         IN   BOOLEAN := FALSE );

END ;

--前置准备：创建用户，表空间，授权用户。

SQL> create tablespace PARTITION;

SQL> create user par identified by par;

SQL> grant dba to par;

--创建表,索引，授权，同义词

SQL> conn par/par

Connected.

-- Create table

create table student(

s_id number(8) PRIMARY KEY ,

s_name varchar2(20) not null ,

s_sex varchar2(8),

s_birdate date ,

constraint u_1 unique (s_name),

constraint c_1 check (s_sex in ( 'MALE' , 'FEMALE' )))

tablespace PARTITION;

-- Add comments to the table

comment on table STUDENT is '学生表' ;

-- Add comments to the columns

comment on column STUDENT.s_name is '姓名' ;

comment on column STUDENT.s_sex is '性别' ;

comment on column STUDENT.s_birdate is '出生日期' ;

-- Create/Recreate indexes

create index S_NAME_IDX on STUDENT (S_NAME, S_SEX) tablespace PARTITION;

-- Create SYNONYM

CREATE SYNONYM stu FOR student;

-- Grant/Revoke object privileges

grant select , insert , delete on STUDENT to SCOTT;

--查看表结构

SQL> desc stu

  Name                       Null ?    Type

  ----------------------------------------- -------- ----------------------------

  S_ID                      NOT NULL NUMBER(8)

  S_NAME                    NOT NULL VARCHAR2(20)

  S_SEX                          VARCHAR2(8)

  S_BIRDATE                      DATE

--插入数据

begin

   for i in 0 .. 24 loop

     insert into student values

       (i,

        'student_' || i,

        decode(mod(i, 2), 0, 'MALE' , 'FEMALE' ),

        add_months(to_date( '2019-1-1' , 'yyyy-mm-dd' ), i));

   end loop;

   commit ;

end ;

/

--查看表主键

SQL> select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'STUDENT' ;

--查看表大小和表空间

--查看表空间

SQL> select tablespace_name from dba_segments where segment_type= 'TABLE' and segment_name= 'STUDENT' and owner= 'PAR' ;

--查看表大小

SQL> select sum (bytes/1024/1024) from dba_segments where segment_type= 'TABLE' and segment_name= 'STUDENT' and owner= 'PAR' ;

--查看表空间

select tbs_used_info.tablespace_name,

        tbs_used_info.alloc_mb,

        tbs_used_info.used_mb,

        tbs_used_info.max_mb,

        tbs_used_info.free_of_max_mb,

        tbs_used_info.used_of_max || '%' used_of_max_pct

   from ( select a.tablespace_name,

                round(a.bytes_alloc / 1024 / 1024) alloc_mb,

                round((a.bytes_alloc - nvl(b.bytes_free,

                                           0)) / 1024 / 1024) used_mb,

                round((a.bytes_alloc - nvl(b.bytes_free,

                                           0)) * 100 / a.maxbytes) used_of_max,

                round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,

                                                        0)) / 1048576) free_of_max_mb,

                round(a.maxbytes / 1048576) max_mb

           from ( select f.tablespace_name,

                        sum (f.bytes) bytes_alloc,

                        sum (decode(f.autoextensible,

                                   'YES' ,

                                   f.maxbytes,

                                   'NO' ,

                                   f.bytes)) maxbytes

                   from dba_data_files f

                  group by tablespace_name) a,

                ( select f.tablespace_name,

                        sum (f.bytes) bytes_free

                   from dba_free_space f

                  group by tablespace_name) b

          where a.tablespace_name = b.tablespace_name(+)) tbs_used_info

  order by tbs_used_info.used_of_max desc ;

--如果表空间不够，提前增加表空间大小

alter tablespace PARTITION add datafile;

--收集统计信息(可忽略)

EXEC DBMS_STATS.gather_table_stats( 'PAR' , 'STUDENT' , cascade => TRUE );

SQL> EXEC Dbms_Redefinition.can_redef_table( 'PAR' , 'STUDENT' );

PL/SQL procedure successfully completed.

--创建间隔分区(增加列s_phone)

create table STUDENT_PAR

(

   s_id      NUMBER(8) not null ,

   s_name    VARCHAR2(20) not null ,

   s_sex     VARCHAR2(8),

   s_birdate DATE ,

   s_phone   number

)

tablespace PARTITION

PARTITION BY RANGE(s_birdate)

INTERVAL (NUMTOYMINTERVAL(1, 'MONTH' )) STORE IN (partition)

(PARTITION STUDENT_201901 VALUES LESS THAN (TO_DATE( '2019-02-01 0' , 'SYYYY-MM-DD Hh34:MI:SS' , 'NLS_CALENDAR=GREGORIAN' )));

--临时中间表上创建如下：

--创建主键约束

alter table STUDENT_PAR add primary key (S_ID) using index tablespace PARTITION;

--创建唯一索引约束

alter table STUDENT_PAR add constraint U_1_PAR unique (S_NAME) using index tablespace PARTITION;

--创建check约束

alter table STUDENT_PAR add constraint C_1_PAR check (s_sex in ( 'MALE' , 'FEMALE' ));

--创建索引

CREATE INDEX S_NAME_IDX_PAR ON STUDENT_PAR (S_NAME,S_SEX) tablespace PARTITION;

--创建同义词

CREATE SYNONYM stu_par FOR STUDENT_PAR;

--添加描述

COMMENT ON TABLE   STUDENT_PAR IS '学生表' ;

COMMENT ON COLUMN STUDENT_PAR.s_name IS '姓名' ;

COMMENT ON COLUMN STUDENT_PAR.s_sex IS '性别' ;

COMMENT ON COLUMN STUDENT_PAR.s_birdate IS '出生日期' ;

--授权

GRANT SELECT , INSERT , DELETE ON STUDENT_PAR TO scott;

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => 'PAR' ,

orig_table => 'STUDENT' ,

int_table => 'STUDENT_PAR' );

END ;

/

PL/SQL procedure successfully completed.

--模拟业务不停，DML表数据写入

insert into STUDENT values (25, 'student_25' , 'MALE' ,to_date( '2020-8-1' , 'yyyy-mm-dd' ));

update student set s_sex= 'FEMALE' where s_id = 20;

commit ;

--比对student和student_par数据

select s_id,s_name,s_sex,s_birdate from student

minus

select s_id,s_name,s_sex,s_birdate from student_par;

       S_ID S_NAME       S_SEX    S_BIRDATE

---------- -------------------- -------- ------------------

     20 student_20       FEMALE   01-SEP-20

     25 student_25       MALE     01-AUG-20

--同步数据到临时表

BEGIN

dbms_redefinition.sync_interim_table(

uname => 'PAR' ,

orig_table => 'STUDENT' ,

int_table => 'STUDENT_PAR' );

END ;

/

--数据已全部同步到临时表

select s_id,s_name,s_sex,s_birdate from student

minus

select s_id,s_name,s_sex,s_birdate from student_par;

no rows selected

--收集par table的统计信息

EXEC DBMS_STATS.gather_table_stats( 'PAR' , 'STUDENT_PAR' , cascade => TRUE );

--结束在线重定义过程

BEGIN

dbms_redefinition.finish_redef_table(

uname => 'PAR' ,

orig_table => 'STUDENT' ,

int_table => 'STUDENT_PAR' );

END ;

/

SQL> select table_name，PARTITION_NAME from user_tab_partitions where table_name in ( 'STUDENT' , 'STUDENT_PAR' );

SQL> select table_name,index_name from user_indexes where table_name in ( 'STUDENT' , 'STUDENT_PAR' );

--drop中间表或者rename原来的约束

a. drop table STUDENT_PAR;

b.

ALTER TABLE STUDENT_PAR RENAME CONSTRAINT U_1 TO U_1_20210411;

ALTER TABLE STUDENT_PAR RENAME CONSTRAINT C_1 TO C_1_20210411;

ALTER INDEX S_NAME_IDX RENAME TO S_NAME_IDX_20210411;

ALTER INDEX U_1 RENAME TO U_1_20210411;

--rename 新分区表的约束和索引

ALTER TABLE STUDENT RENAME CONSTRAINT U_1_PAR TO U_1;

ALTER TABLE STUDENT RENAME CONSTRAINT C_1_PAR TO C_1;

ALTER INDEX S_NAME_IDX_PAR RENAME TO S_NAME_IDX;

ALTER INDEX U_1_PAR RENAME TO U_1;

--查看索引，约束名称是否正确

select table_name,index_name from user_indexes where table_name in ( 'STUDENT' , 'STUDENT_PAR' ) order by table_name;

TABLE_NAME             INDEX_NAME

------------------------------ ------------------------------

STUDENT                S_NAME_IDX

STUDENT                SYS_C0011401

STUDENT                U_1

STUDENT_PAR            S_NAME_IDX_20210411

STUDENT_PAR            U_1_20210411

STUDENT_PAR            SYS_C0011395

SQL> desc stu

  Name                       Null ?    Type

  ----------------------------------------- -------- ----------------------------

  S_ID                      NOT NULL NUMBER(8)

  S_NAME                    NOT NULL VARCHAR2(20)

  S_SEX                          VARCHAR2(8)

  S_BIRDATE                      DATE

  S_PHONE                        NUMBER