0

I'm trying to redefine a table to be partitioned.

What I did:

CREATE TABLE WERTEVAR_TMP(
    "WVTEIL" NUMBER(10,0),
    "WVMERKMAL" NUMBER(5,0),
    "WVUNTERS" NUMBER(5,0),
    "WVWERTNR" NUMBER(10,0),
    "WVWERT" FLOAT(126),
    "WVATTRIBUT" NUMBER(5,0),
    "WVPRUEFER" NUMBER(10,0),
    "WVPRUEFMIT" NUMBER(10,0),
    "WVMASCHINE" NUMBER(10,0),
    "WVNEST" NUMBER(10,0),
    "WVDATZEIT" DATE,
    "WVSTIANF" NUMBER(3,0),
    "WVCHARGE" VARCHAR2(20 BYTE),
    "WVAUFTRAG" VARCHAR2(20 BYTE),
    "WV0005" VARCHAR2(35 BYTE),
    "WV0009" VARCHAR2(255 BYTE),
    "WV0011" VARCHAR2(250 BYTE),
    "WV0014" VARCHAR2(40 BYTE),
    "WV0015" NUMBER(10,0),
    "WV0016" VARCHAR2(32 BYTE),
    "WV0017" VARCHAR2(32 BYTE),
    "WV0031" RAW(16),
    "MONITORING_VERSION" NUMBER DEFAULT 0,
    "WV0034" NUMBER(10,0),
    "WV0054" VARCHAR2(32 BYTE),
    "WV0055" VARCHAR2(32 BYTE),
    "WV0056" VARCHAR2(32 BYTE),
    "WV0057" VARCHAR2(32 BYTE),
    "WV0058" VARCHAR2(32 BYTE),
    "WV0059" VARCHAR2(32 BYTE),
    "WV0060" VARCHAR2(32 BYTE),
    "WV0061" NUMBER(10,0),
    "WV0062" NUMBER(10,0),
    "WV0063" NUMBER(10,0),
    "WV0080" VARCHAR2(64 BYTE),
    "WV0081" NUMBER(5,0)
)
STORAGE (INITIAL 65K NEXT 65K)
PARTITION BY RANGE (WVDATZEIT) INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY HASH (WVMASCHINE) SUBPARTITIONS 8
(
    PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY'))
);

BEGIN
    DBMS_REDEFINITION.start_redef_table(
        uname         => 'CHYSTAT_STAGING_MONITORING',
        orig_table    => 'WERTEVAR',
        int_table     => 'WERTEVAR_TMP',
        options_flag  => DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

SET SERVEROUTPUT ON;
DECLARE
    l_errors  NUMBER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname            => 'CHYSTAT_STAGING_MONITORING',
        orig_table       => 'WERTEVAR',
        int_table        => 'WERTEVAR_TMP',
        copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
        copy_triggers    => TRUE,
        copy_constraints => TRUE,
        copy_privileges  => TRUE,
        ignore_errors    => FALSE,
        num_errors       => l_errors,
        copy_statistics  => FALSE,
        copy_mvlog       => FALSE
    );

    DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      => 'CHYSTAT_STAGING_MONITORING',
    orig_table => 'WERTEVAR',
    int_table  => 'WERTEVAR_TMP');
END;

But DBMS_REDEFINITION.finish_redef_table ends with:

Error report:
ORA-02448: constraint does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 105
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3520
ORA-06512: at line 2
02448. 00000 -  "constraint does not exist"
*Cause:    The named constraint does not exist
*Action:   Stop trying to do something with a nonexistant constraint

I would appreciate any hint.

  • Did you search on support.oracle.com, there's a couple hits for problems like that. – Mat Mar 8 '16 at 16:17
  • Yes, but I did't found anything about this exception. – Behnil Mar 8 '16 at 16:24
  • Search for DBMS_REDEFINITION and ORA-02448 – Mat Mar 8 '16 at 16:24
1

ORA-02448 during DBMS_REDEFINITION.finish_redef_table ERROR at line 1: ORA-02448: constraint does not exist

CAUSE

Bug 18258770 : ORA-2448 ON DBMS_REDEFINITION.FINISH_REDEF_TABLE

Here is the document link(MOS account required)
ORA-02448 During DBMS_REDEFINITION.finish_redef_table (Doc ID 2021843.1)

| improve this answer | |
  • Thanks for sharing, but when I click on that link it redirects me to the MOS at 'Knowledge' folder where is just an empty space with subtitle 'Document display'. Also searching for 'ORA-02448 during DBMS_REDEFINITION.finish_redef_table' within 'Knowledge' folder shows 'Search Results could not be processed'. Is there any workaround for that bug? – Behnil Mar 8 '16 at 17:31
  • 1
    @Behnil: I have just typed 'ora 2448' on MOS home page search and got the results. If that cause of Bug you have to apply Interim patches which are available through MOS. – JSapkota Mar 8 '16 at 17:56
0

Thanks @JSapkota for pointing me to the MOS knowledge document. The real problem is actually in runnig table redefinition in parallel mode.

See ORA-02448 error is reported while redefining a table in parallel (Doc ID 2065829.1) for more information.

The workaround:

Disable "_px_filter_parallelized" and then retry the table redefinition. This work around will not disable parallel query. It will only make the filter operations run in serial.

Either:
SQL> alter session set "_px_filter_parallelized"=false;
SQL> -- then run the table redefinition

OR
SQL> alter session set "_fix_control" = "10043801:OFF";
SQL> -- then run the table redefinition
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.