Forum Stats

  • 3,853,687 Users
  • 2,264,255 Discussions
  • 7,905,432 Comments

Discussions

Unique index WWV_FLOW_WS_PIVOT_IDX3 violated APEX 5.1

Kim Mertens
Kim Mertens Member Posts: 45 Blue Ribbon
edited Nov 3, 2017 3:32AM in APEX Discussions

Hi all,

One of our users gets this Unique index violation everytime he opens a page containing an Interactive Report.

The user has saved several containing pivot and non-pivot private reports.

He never gets to see the report, the error immediately shows on the screen.

Already found that this bug should've been fixed in patchset 5.0.3 with bug number 22160678

Unfortunately we are still experiencing this issue in version 5.1.0.00.45.

The index itself is on table WWV_FLOW_WORKSHEET_PIVOT and on the following columns:

WORKSHEET_ID

WEBSHEET_ID

REPORT_ID

A temporary fix that seems to work, no idea why tho, is the following:

  1. Change owner of the private reports from user X to user Y with statement
    update wwv_flow_worksheet_rpts
    set application_user = <USER_Y>
    where id = <report_id>
  2. Let user X start the page, which works now.
  3. Set the private reports back to user X with the same statement statement as mentioned in step 1.

Doing this makes the page works for a few times and after that it's messed up again

Anyone have an idea what the reason could be or how to solve this ?

Above mentioned bug number doesn't give any more information..

Thanks in advance,

Kim

Tagged:
Kim Mertens

Best Answer

  • Christina Cho-Oracle
    Christina Cho-Oracle Posts: 217 Employee
    edited Oct 12, 2017 2:15PM Answer ✓

    Hi Kim,

    Thank you for the additional information.  Since there are some rows returned with query #3, there is definitely an issue with orphan pivot.  However, I am puzzled how orphan pivot record got created since you stated the application IDs are same.  The bug 22160678 is about orphan pivot getting created if application ID changes between the instance which results in wwv_flow_ws_pivot_idx3 error when user session report gets initiated.

    I need one more information from you.  Can you please run the following queries?

    select flow_id, page_id, worksheet_id, report_id

    from wwv_flow_worksheet_pivot

    where worksheet_id in (select id from wwv_flow_worksheets)

    and report_id in (select report_id

    from wwv_flow_worksheet_pivot

    group by report_id

    having count(*) > 1);

    select flow_id, page_id, session_id, base_report_id

    from wwv_flow_worksheet_rpts

    where id in (select report_id

    from wwv_flow_worksheet_pivot

    where worksheet_id in (select id from wwv_flow_worksheets)

    and report_id in (select report_id

    from wwv_flow_worksheet_pivot

    group by report_id

    having count(*) > 1));

    Regards,

    Christina

Answers

  • Christina Cho-Oracle
    Christina Cho-Oracle Posts: 217 Employee
    edited Oct 11, 2017 12:40PM

    Hi Kim,

    I have some questions to help analyze your issue further:

    1. Is your application ID different between development and production app where user experiences wwv_flow_ws_pivot_idx3 error?

    2. Did you upgrade APEX to 5.1.0.00.45 from the previous version?

    3. What are the results when you run the following 2 queries?

    select *

    from wwv_flow_worksheet_pivot

    where worksheet_id not in (select id from wwv_flow_worksheets)

    and report_id in (select report_id

                      from wwv_flow_worksheet_pivot

                      group by report_id

                      having count(*) > 1);

    select *

    from wwv_flow_worksheet_pivot

    where report_id in (select report_id

                      from wwv_flow_worksheet_pivot

                      group by report_id

                      having count(*) > 1);

    Regards,

    Christina

    Kim Mertens
  • Kim Mertens
    Kim Mertens Member Posts: 45 Blue Ribbon
    edited Oct 12, 2017 4:02AM

    Hi Christina,

    1. Is your application ID different between development and production app where user experiences wwv_flow_ws_pivot_idx3 error?

    I have tried to simulate the issue with my user account ( also in the production environment ) but couldn't reproduce it.

    In this case the application ID is identical since it's the same application I tried to simulate it in.

    The application ID in the test environment is different from production.

    2. Did you upgrade APEX to 5.1.0.00.45 from the previous version?

    The previous version that was installed was 5.0.1.00.06.

    3. What are the results when you run the following 2 queries?select *from wwv_flow_worksheet_pivotwhere worksheet_id not in (select id from wwv_flow_worksheets)and report_id in (select report_id from wwv_flow_worksheet_pivot group by report_id having count(*) > 1);

    This statement returns 2 records. Both for the page that is failing and the user that gets the error.

    select *from wwv_flow_worksheet_pivotwhere report_id in (select report_id from wwv_flow_worksheet_pivot group by report_id having count(*) > 1);

    This statement returns 4 records. This is for the page and user that gets the error.

    Kind regards,

    Kim

  • Christina Cho-Oracle
    Christina Cho-Oracle Posts: 217 Employee
    edited Oct 12, 2017 2:15PM Answer ✓

    Hi Kim,

    Thank you for the additional information.  Since there are some rows returned with query #3, there is definitely an issue with orphan pivot.  However, I am puzzled how orphan pivot record got created since you stated the application IDs are same.  The bug 22160678 is about orphan pivot getting created if application ID changes between the instance which results in wwv_flow_ws_pivot_idx3 error when user session report gets initiated.

    I need one more information from you.  Can you please run the following queries?

    select flow_id, page_id, worksheet_id, report_id

    from wwv_flow_worksheet_pivot

    where worksheet_id in (select id from wwv_flow_worksheets)

    and report_id in (select report_id

    from wwv_flow_worksheet_pivot

    group by report_id

    having count(*) > 1);

    select flow_id, page_id, session_id, base_report_id

    from wwv_flow_worksheet_rpts

    where id in (select report_id

    from wwv_flow_worksheet_pivot

    where worksheet_id in (select id from wwv_flow_worksheets)

    and report_id in (select report_id

    from wwv_flow_worksheet_pivot

    group by report_id

    having count(*) > 1));

    Regards,

    Christina

This discussion has been closed.