非分区表转换成分区表

1.partition a non-partitioned table in one of four ways:
A)  Export/import method
  1, exp usr/pswd tables=numbers file=exp.dmp
  2, drop table numbers;
  3,
    create table numbers (qty number(3), name varchar2(15))
      partition by range (qty)
      (partition p1 values less than (501),
       partition p2 values less than (maxvalue));
  4,imp usr/pswd file=exp.dmp ignore=y
    The ignore=y causes the import to skip the table creation and continues to load all rows.
    
B)  Insert with a subquery method

C)  Partition exchange method
   ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or subpartition) into a non-partitioned table and a non-partitioned table into a
   partition (or subpartition) of a partitioned table by exchanging their data and index segments.
   1.create table p_emp                           
     ( sal number(7,2) )                          
     partition by range(sal)                      
      ( partition emp_p1 values less than ( 2000),
        partition emp_p2 values less than ( 4000) ) ;
        
   2.create table dummy_y as select rownum sal from dual connect by rownum > 1000 and rownum < 2000 ;
     eate table dummy_z as select * from  select rownum sal from dual connect by rownum  < 4000) where sal > 2000
   
   3.qn@RAC> alter table p_emp exchange partition emp_p1 with table dummy_y ;
     alter table p_emp exchange partition emp_p1 with table dummy_y          
     *                                                                       
     ERROR at line 1:                                                        
     ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
     
     qn@RAC> alter table dummy_y modify sal number(7,2) ;
     alter table dummy_y modify sal number(7,2)          
                                *                        
     ERROR at line 1:                                    
     ORA-01440: column to be modified must be empty to decrease precision or scale
   
     qn@RAC> truncate table dummy_y ;
     qn@RAC> insert into dummy_y select * from ( select rownum sal from dual connect by rownum  < 2000 ) where sal > 1000
     qn@RAC> commit;
     qn@RAC> alter table p_emp exchange partition emp_p1 with table dummy_y ;
     

D)  DBMS_REDEFINITION
example 1 :
/*非分区表自定义为分区表,同时增加字段  */
This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno.
The table isredefined as follows:
1.Online Table Redefinition Examples
  qn.admin_emp
create table admin_emp (
   empno   NUMBER(5) PRIMARY KEY
  ,ename   VARCHAR2(15) NOT NULL
  ,job     VARCHAR2(10)
  ,deptno  NUMBER(3) NOT NULL
  )

1.Verify that the table is a candidate for online redefinition. In this case you specify
that the redefinition is to be done using primary keys or pseudo-primary keys

/* 必须要权限，否则DBMS_REDEFINITION运行报错 */
grant execute on DBMS_REDEFINITION to qn ;  
grant   EXECUTE_CATALOG_ROLE to qn ;  --Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.

grant CREATE TABLE to qn ;
grant CREATE MATERIALIZED VIEW to qn ;  /* 说明同步过程  需要物化视图  */
grant CREATE  INDEX to qn ;

The CREATE TRIGGER privilege is also required to execute the COPY_TABLE_DEPENDENTS procedure.

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('QN','admin_emp',DBMS_REDEFINITION.CONS_USE_PK);
END;

2.Create an interim table hr.int_admin_emp
create tablespace admin_tbs  datafile size 200m ;
create tablespace admin_tb2  datafile size 200m ;

 CREATE TABLE int_admin_emp
( empno NUMBER(5) PRIMARY KEY,
  ename VARCHAR2(15) NOT NULL,
    job VARCHAR2(10),
    mgr      NUMBER(5),
    hiredate DATE DEFAULT (sysdate),
    sal      NUMBER(7,2),
    deptno   NUMBER(3) NOT NULL,
    bonus    NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tb2);


3.Start the redefinition process
/* 对应字段才能同步数据，否则只能同步部分数据   */
/* dbms_redefinition.cons_use_pk  与  CONS_USE_ROWID
/* 自定义方法:cons_use_pk     根据primary key
              CONS_USE_ROWID  根据rowid    
*/

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('qn', 'admin_emp','int_admin_emp','empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',dbms_redefinition.cons_use_pk);
END;


4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_admin_emp.)
/* 自动制复制triggers, indexes, materialized view logs, grants, , constraints */
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('QN', 'admin_emp','int_admin_emp',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint,
 and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors,
 but you must run the query shown in the next step to see if there are other errors.

5.Query the DBA_REDEFINITION_ERRORS view to check for errors.
 /* 要查询报错 ，不并是所有报错都是需要执行的  */
sys@RAC> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

OBJECT_NAME               BASE_TABLE_NAME              DDL_TXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_C0011092               ADMIN_EMP              CREATE UNIQUE INDEX "QN"."TMP$$_SYS_C00110920" ON "QN"."INT_ADMIN_EMP" ("EMPNO")
                                                          PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                                                          STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                                          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                                                          BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                                                          TABLESPACE "USERS"

SYS_C0011090               ADMIN_EMP              ALTER TABLE "QN"."INT_ADMIN_EMP" MODIFY ("ENAME" CONSTRAINT "TMP$$_SYS_C00110900" NOT NULL ENABLE NOVALIDATE)

SYS_C0011091               ADMIN_EMP              ALTER TABLE "QN"."INT_ADMIN_EMP" MODIFY ("DEPTNO" CONSTRAINT "TMP$$_SYS_C00110910" NOT NULL ENABLE NOVALIDATE)

SYS_C0011092               ADMIN_EMP              ALTER TABLE "QN"."INT_ADMIN_EMP" ADD CONSTRAINT "TMP$$_SYS_C00110920" PRIMARY KEY ("EMPNO")
                                                          USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                                                          STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                                          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                                                          BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                                                          TABLESPACE "USERS"  ENABLE NOVALIDATE


6.Optionally, synchronize the interim table hr.int_admin_emp.
/* 这步会将数据从admin_emp刷新到中间表int_admin_emp，没有改变表名 */
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QN', 'admin_emp', 'int_admin_emp');
END;

 


7.Complete the redefinition
/* 这步会再次装数据从admin_emp刷新到中间表int_admin_emp，出现exclusive mode模式,int_admin_emp 改为admin_emp,admin_emp改为int_admin_emp */
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('qn', 'admin_emp', 'int_admin_emp');
END;
   


8. Wait for any long-running queries against the interim table to complete, and then
drop the interim table.


select dbms_metadata.get_ddl('TABLE','ADMIN_EMP') from dual;