This discussion is archived
3 Replies Latest reply: Jul 10, 2012 12:09 AM by 941447 RSS

Explain plan on versioned tables

941447 Newbie
Currently Being Moderated
Hello community,

I have a question on explain plans:

I have a table MY_TABLE on which versioning is enabled:
exec dbms_wm.enableversioning('MY_TABLE');
If I want to get an explain plan for a query on this versioned table like "select * from MY_TABLE",
I get an ORACLE error: "ORA-0109: insufficient privileges on underlying objects of the view"

If I am trying to obtain the explain plan for "SELECT * FROM MY_TABLE_LT", there is no problem at all.

What are the privileges I need to get explain plans for queries on versioned tables?

Regards,

Edited by: 938444 on 04-jun-2012 4:37
  • 1. Re: Explain plan on versioned tables
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    What version of Workspace Manager are you using? Also, it may depend on the table definition so anything you can share regarding that would be helpful.

    Regards,
    Ben
  • 2. Re: Explain plan on versioned tables
    941447 Newbie
    Currently Being Moderated
    Hi Ben,

    I use Oracle Database 10g Enterprise Edition Release 10.2.0.5.0.

    The table definition is as follows:

    create table MY_TABLE
    (
    X_ID NUMBER(28) not null,
    V_TEXT VARCHAR2(200),
    V_COMMENT VARCHAR2(4000)
    )
    tablespace DATOS_BDI_DATOS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    );
    -- Create/Recreate primary, unique and foreign key constraints
    alter table MY_TABLE
    add constraint PK_MY_TABLE primary key (X_ID)
    using index
    tablespace DATOS_BDI_DATOS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    );

    Regards,

    Marc
  • 3. Re: Explain plan on versioned tables
    941447 Newbie
    Currently Being Moderated
    Hi,

    I just found the solution myself.
    The view MY_TABLE (when the table is versioned) needs SELECT privileges on the following tables:
    + wmsys.wm$modified_tables
    + wmsys.wm$nextver_table
    + wmsys.wm$version_table
    + wmsys.wm$workspaces_table

    In my case, the grant on wmsys.wm$modified_tables was missing:

    grant select on wmsys.wm$modified_tables to <schema>

    Thanks to everyone,

    Marc

Legend

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