3 Replies Latest reply: Jul 10, 2012 2:09 AM by 941447 RSS

    Explain plan on versioned tables

    941447
      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-Oracle
          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
            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
              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