This discussion is archived
11 Replies Latest reply: Aug 22, 2012 7:27 AM by 955518 RSS

BeginDDL is failing with no data found ORA-01403

954597 Newbie
Currently Being Moderated
It appears to us that running the AlterVersionedTable command to drop an index has caused an issue for one of our tables that is versioned.

begin
dbms_wm.AlterVersionedTable(
table_name=>'PAC_PMD.CURRENT_DMS',
alter_option=>'DDL',
parameter_options=>'ddl=drop index PAC_PMD.CURRENT_DMS_UK1, force=true', ignore_last_error=>true);
end;

After running this command and then trying to execute BeginDLL we are getting the error ORA-01403 – No Data Found.

Also the state of the table in all_wm_versioned_tables is set to VERSIONED.

Any ideas?
  • 1. Re: BeginDDL is failing with no data found ORA-01403
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Using AlterVersionedTable to drop an index is not supported. You need to use beginDDL/commitDDL to do that. You should recreate the index in order to solve the ora-1403 error.

    Regards,
    Ben
  • 2. Re: BeginDDL is failing with no data found ORA-01403
    955518 Newbie
    Currently Being Moderated
    Hi Ben,

    So how do we create the index again? AlterVersionedTable does not allow for creating an index and beginddl is failing. Oracle suggested to disable versioning but we are still getting no data found.

    Error starting at line 1 in command:
    exec dbms_wm.disableversioning('PAC_PMD.CURRENT_DMS', ignore_last_error=>true)
    Error report:
    ORA-01403: no data found
    ORA-06512: at "WMSYS.LT", line 9337
    ORA-06512: at line 1
    01403. 00000 - "no data found"
    *Cause:   
    *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 3. Re: BeginDDL is failing with no data found ORA-01403
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    AlterVersionedTable should be able to create the index. What is the syntax of the command that you tried?

    Regards,
    Ben
  • 4. Re: BeginDDL is failing with no data found ORA-01403
    955518 Newbie
    Currently Being Moderated
    We tried this command:

    exec DBMS_WM.AlterVersionedTable(table_name=>'PAC.TOMS_TEST_OWM', alter_option=>'DDL', parameter_options=>'ddl=create index TOMS_TEST_OWM_UK1 on table pac.toms_test_owm, force=true', ignore_last_error=>true);


    Here is the steps to recreate the issue we are seeing:

    CREATE TABLE "PAC"."TOMS_TEST_OWM"
    (
    "ID" VARCHAR2(50) NOT NULL ENABLE,
    "NAME" VARCHAR2(100) NOT NULL ENABLE,
    "SCHEMA_NAME" VARCHAR2(100) NOT NULL ENABLE,
    CONSTRAINT "TOMS_TEST_OWM_PK" PRIMARY KEY ("ID") ENABLE,
    CONSTRAINT "TOMS_TEST_OWM_UK1" UNIQUE ("NAME") ENABLE
    );

    exec dbms_wm.EnableVersioning('PAC.TOMS_TEST_OWM','VIEW_WO_OVERWRITE');

    exec DBMS_WM.AlterVersionedTable(table_name=>'PAC.TOMS_TEST_OWM', alter_option=>'DDL', parameter_options=>'ddl=drop index PAC.TOMS_TEST_OWM_UK1, force=true', ignore_last_error=>true);

    exec DBMS_WM.beginddl('PAC.TOMS_TEST_OWM');

    The begin ddl should fail with no data found.
  • 5. Re: BeginDDL is failing with no data found ORA-01403
    955518 Newbie
    Currently Being Moderated
    Actually the command I believe should be:

    exec DBMS_WM.AlterVersionedTable(table_name=>'PAC.TOMS_TEST_OWM', alter_option=>'DDL', parameter_options=>'ddl=create unique index PAC.TOMS_TEST_OWM2_UK1 on PAC.TOMS_TEST_OWM2 (''NAME''), force=true', ignore_last_error=>true);

    The response is:

    Error starting at line 19 in command:
    exec DBMS_WM.AlterVersionedTable(table_name=>'PAC.TOMS_TEST_OWM', alter_option=>'DDL', parameter_options=>'ddl=create unique index TOMS_TEST_OWM_UK1 on table pac.toms_test_owm(name), force=true', ignore_last_error=>true)
    Error report:
    ORA-20171: WM error: This DDL operation is not supported.
    ORA-06512: at "WMSYS.LT", line 14535
    ORA-06512: at line 1
  • 6. Re: BeginDDL is failing with no data found ORA-01403
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Try to create the index as nonunique. That should allow beginDDL to succeed and at that point you could recreate the index as needed.

    Regards,
    Ben
  • 7. Re: BeginDDL is failing with no data found ORA-01403
    955518 Newbie
    Currently Being Moderated
    I tried this but get the response that this operation is not supported. Any other ideas?
  • 8. Re: BeginDDL is failing with no data found ORA-01403
    Ben Speckhard Pro
    Currently Being Moderated
    File an SR for it.

    Regards,
    Ben
  • 9. Re: BeginDDL is failing with no data found ORA-01403
    955518 Newbie
    Currently Being Moderated
    We already have but Oracle's advice is to rollback and that is simply not possible. The only lucky thing is the problem is in our QA environment.
  • 10. Re: BeginDDL is failing with no data found ORA-01403
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Since it's in your QA environment, are you able to drop the schema and then recreate it? Otherwise, just let support know that what they suggested isn't possible and provide the error messages encountered.

    Regards,
    Ben
  • 11. Re: BeginDDL is failing with no data found ORA-01403
    955518 Newbie
    Currently Being Moderated
    Hi Ben,

    Here is the solution that finally worked for our problem that Oracle came up with:

    alter trigger wmsys.NO_VM_DROP_A disable;
    alter trigger wmsys.NO_VM_DDL disable;
    create unique index PAC_PMD.CURRENT_DMS_UK1 on PAC_PMD.CURRENT_DMS_LT(DM_NAME);
    exec dbms_wm.disableversioning('PAC_PMD.CURRENT_DMS',ignore_last_error=>true);
    alter trigger wmsys.NO_VM_DROP_A enable;
    alter trigger wmsys.NO_VM_DDL enable;
    alter table PAC_PMD.current_dms disable constraint CURRENT_DMS_UK1;


    Thanks for your help,
    Tom

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points