方案四:通过 DBMS_REDEFINITION 方法
此种方式可以在线将普通表转换为分区表,不需要停应用。
1. Create the Partition Table structure required, known as the Interim table.
CREATE TABLE WZ_REAL_DATA_tmp4
(STIME DATE NOT NULL,
PI_AI_POINT VARCHAR2(30) NOT NULL,
FVALUE NUMBER(16,4),
CONSTRAINT PK_WZ_REAL_DATA4 PRIMARY KEY (STIME,PI_AI_POINT))
partition by range (stime)
(partition p1_2012 values less than (to_date('2013-01-01','yyyy-mm-dd')),
partition p1_2013 values less than (to_date('2014-01-01','yyyy-mm-dd')),
partition p1_2014 values less than (to_date('2015-01-01','yyyy-mm-dd')),
partition p1_2015 values less than (to_date('2016-01-01','yyyy-mm-dd')),
partition p_max values less than (maxvalue)) tablespace XDWZJS;
2. Execute DBMS_REDEFINITION.can_redef_table…
grant EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE to xdwz;
SQL> EXEC Dbms_Redefinition.can_redef_table('XDWZ','WZ_REAL_DATA');
BEGIN Dbms_Redefinition.can_redef_table('XDWZ','WZ_REAL_DATA'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
ORA-06550: line 1, column 7:
PL/SQL