--The user of the operation needs to have the following permissionsGRANTCONNECT, RESOURCE toCMIGDW;GRANT EXECUTE onDbms_redefinition toCMIGDW;GRANT ALTER any TABLE toCMIGDW;GRANT DROP any TABLE toCMIGDW;GRANTLOCK any TABLE toCMIGDW;GRANT CREATE any TABLE toCMIGDW;GRANT SELECT any TABLE toCMIGDW;GRANT CREATE any TRIGGER toCMIGDW;GRANT CREATE any INDEX toCMIGDW;--tables that require partitioningSelect * fromCMIGDW. Test_date_dim;--Table StructureCreate TableTest_date_dim (date_id Number, Year_ Number, Half_yearVARCHAR2( -), season_idVARCHAR2( -), seasonVARCHAR2( -), Month_ Number, Week Number);--Create a table that is the same as the Test_date_dim table field, but partitionedCreate Tabletest_date_dim2 (date_id Number, Year_ Number, Half_yearVARCHAR2( -), season_idVARCHAR2( -), seasonVARCHAR2( -), Month_ Number, Week Number) PARTITION bylist (month_) (PARTITION P1Values(201501), PARTITION P2Values(201502), PARTITION P3Values(default));--Start online redefinitionConnected toOracleDatabase11g Enterprise Edition Release11.2.0.3.0Connected asCmigdw@CMIG_TESTSQL> EXECDbms_redefinition.start_redef_table ('CMIGDW','Test_date_dim','test_date_dim2');beginDbms_redefinition.start_redef_table ('CMIGDW','Test_date_dim','test_date_dim2');End; ORA-12089: Cannot redefine table "CMIGDW" without a primary key online. " Test_date_dim "ORA-06512: In "SYS. Dbms_redefinition ", line AboutORA-06512: In "SYS. Dbms_redefinition ", line1490ORA-06512: On line1
Need to have a primary key
--start performing online redefinitionSql> EXECDbms_redefinition.start_redef_table ('CMIGDW','Test_date_dim','test_date_dim2');P L/Sqlproceduresuccessfully completedsql>SQL> DECLARE 2Error_count Pls_integer:= 0; 3 BEGIN 4Dbms_redefinition.copy_table_dependents ('CMIGDW','Test_date_dim','test_date_dim2', 5 0, True, False, True, False,6error_count); 7 8Dbms_output.put_line ('Errors: =' ||To_char (Error_count)); 9 END; Ten /PL/Sqlproceduresuccessfully completed--End Online redefinitionSql> EXECDbms_redefinition.finish_redef_table ('CMIGDW','Test_date_dim','test_date_dim2');P L/Sqlproceduresuccessfully completedsql> Select * fromTest_date_dim PARTITION (p1); SQL> Select * fromtest_date_dim2;--Delete an intermediate tableDROP TABLETEST_DATE_DIM2;
Oracle 11g converts non-partitioned tables to partition table online redefinition