Forum Stats

  • 3,728,222 Users
  • 2,245,576 Discussions
  • 7,853,386 Comments

Discussions

Oracle Flashback Query & VPD Protected Tables

Duncs
Duncs Member Posts: 507 Bronze Badge

I have been playing around with Oracle Flashback Query in order to move to a more out the box set of functions for data auditing over our custom table and trigger approach.

All is working really well so far but I have a question about VPD protected tables.

If within my schema, I have a VPD protected table called PERSON, when I connect as SQL*Plus and issue:

SELECT *

FROM person;

Then I get 0 rows returned (as expected). Data is only returned once my application contexts are set up (an APEX Application does this).

If however I run via SQL*Plus:

SELECT *

   FROM person AS OF TIMESTAMP SYSDATE-3;

The I get to see all data.

Is there any way of configuring Oracle Flashback Query to respect, in-play table VPD Policies?

Thanks for any help.

Duncs

DuncsDejan T.

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019

    Interesting.

    Which version of Oracle, and what happens when you try "as of timestamp sysdate" ?

    At first sight this looks like a bug and it's worth remembering that when two features (particularly ones that are not used by many people) collide things often break in the earlier releases.

    Regards

    Jonathan Lewis

    Currently collecting and matching donations to a life-changing cause.

    Duncs
  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited December 2019

    Hi Jonathan.

    We are running 11.2.0.4 (but will be migrating to the Oracle Cloud in the next few months ... so latest and greatest DB version).

    I am away from the office for the next few hours but will put a little test harness together and run the "as of timestamp sysdate" and let you know.

    Cheers for your thoughts,

    Duncs

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019

    Duncs,

    I don't know if livesql.com will allow you to do flashback queries and VPD, but another thing you could try is to run your test case there to see if the error reproduces on 19.x.0.0

    Regards

    Jonathan Lewis

    Currently collecting and matching donations to a life-changing cause.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited December 2019

    Another option to test on newest releases would be to download the "Database Virtual Box Appliance / Virtual Machine" that includes Oracle 19.3

    https://www.oracle.com/database/technologies/databaseappdev-vm.html

    It should be easy to configure VPD there, I doubt Live SQL will allow it...

  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited December 2019

    Yep

    Will run my test harness against

    11.2.0.4

    a 12c instance

    and a 19.3 instance and post the results.

    Cheers

    Duncs

  • JohnWatson2
    JohnWatson2 Member Posts: 4,227 Bronze Crown
    edited December 2019

    I get the function applied with or without a flashback query:

    orcla>

    orcla> conn / as sysdba

    Connected.

    orcla> select * from v$version;

    BANNER

    --------------------------------------------------------------------------------

    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

    PL/SQL Release 11.2.0.4.0 - Production

    CORE    11.2.0.4.0      Production

    TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

    NLSRTL Version 11.2.0.4.0 - Production

    orcla> create or replace function sys.simplevpd

      2  (object_schema varchar2,

      3  object_name varchar2)

      4  return varchar2

      5  as

      6  begin

      7  return 'deptno=10';

      8  end;

      9  /

    Function created.

    orcla> exec dbms_rls.add_policy('SCOTT','EMP','P1','SYS','SIMPLEVPD');

    PL/SQL procedure successfully completed.

    orcla> conn scott/tiger

    Connected.

    orcla> set autot on exp

    orcla> select * from emp;

         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

          7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10

          7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10

          7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3956160932

    --------------------------------------------------------------------------

    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------

    |   0 | SELECT STATEMENT  |      |     5 |   190 |     3   (0)| 00:00:01 |

    |*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     3   (0)| 00:00:01 |

    --------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - filter("DEPTNO"=10)

    orcla> select * from emp as of timestamp sysdate -1;

         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

          7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10

          7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10

          7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3956160932

    --------------------------------------------------------------------------

    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------

    |   0 | SELECT STATEMENT  |      |     5 |   190 |     6   (0)| 00:00:01 |

    |*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     6   (0)| 00:00:01 |

    --------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - filter("DEPTNO"=10)

    orcla>

    Duncs
  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited December 2019

    Interesting

    Let me knock my test harness together and I will post my findings

    Duncs

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019
    I get the function applied with or without a flashback query:

    Three possible reasons that MIGHT explain why different people see different effects:

    a) Policy function not owned by table owner (general principle)

    b) Policy function owned by SYS  (specific threat)

    c) Policy function "too simple" - use of sys_context() is about as simple as I've seen in production

    It will be interesting to see the test case from the OP.

    Regards

    Jonathan Lewis

  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited December 2019

    OK, my DBA is working up the test case but I ran my existing code using SYSDATE instead of sysdate-3 and to my surprise, no data is shown. Adding in sysdate-3 then returns data.

    Cheers

    Duncs

  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited December 2019

    Right, took a while to get my test case together but tried this on 11.2.0.4 and 19.3 and both show the same issue:

    CREATE USER VPD_TEST_DATA_OWNER IDENTIFIED BY "Password_1234" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON USERS;

    GRANT connect, resource, create any context TO VPD_TEST_DATA_OWNER;

    GRANT CREATE ANY CONTEXT TO VPD_TEST_DATA_OWNER;

    GRANT EXECUTE ON DBMS_RLS TO VPD_TEST_DATA_OWNER;

    CREATE USER VPD_TEST_FUNCTION_OWNER IDENTIFIED BY "Password_1234" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON USERS;

    GRANT connect, resource TO VPD_TEST_FUNCTION_OWNER;

    CREATE TABLE VPD_TEST_DATA_OWNER.person (person_id NUMBER, surname VARCHAR2(100), unit_id NUMBER);

    INSERT INTO VPD_TEST_DATA_OWNER.person VALUES (12345, 'MEIN', 10);

    COMMIT;

    create or replace FUNCTION VPD_TEST_FUNCTION_OWNER.TEST_vpd_function
      (  p_schema  IN VARCHAR2 DEFAULT NULL
      , p_object  IN VARCHAR2 DEFAULT NULL
      )
      RETURN VARCHAR2
      AS
      lv_unit_id NUMBER := NVL(SYS_CONTEXT('MY_VPD_CONTEXT','unit_id'), -1);
      BEGIN
     
      RETURN 'unit_id = ' || lv_unit_id;

    END TEST_vpd_function;
    /
    grant execute on VPD_TEST_FUNCTION_OWNER.TEST_vpd_function to public;

    BEGIN
          DBMS_RLS.add_policy (object_schema    => 'VPD_TEST_DATA_OWNER'
                            , object_name      => 'PERSON'
                            , policy_name      => 'TEST_VPD_POLICY'
                            , function_schema  => 'VPD_TEST_FUNCTION_OWNER'
                            , policy_function  => 'TEST_vpd_function'
                            , statement_types  => 'select');
      END;
      /

    CREATE OR REPLACE CONTEXT "VPD_TEST_FUNCTION_OWNER.MY_VPD_CONTEXT" USING VPD_TEST_FUNCTION_OWNER.context_api_pkg;

    CREATE OR REPLACE PACKAGE VPD_TEST_FUNCTION_OWNER.context_api_pkg AS
      PROCEDURE set_parameter(p_name  IN  VARCHAR2,
                              p_value  IN  VARCHAR2);
      END context_api_pkg;
      /

    CREATE OR REPLACE PACKAGE BODY VPD_TEST_FUNCTION_OWNER.context_api_pkg IS

    PROCEDURE set_parameter (p_name  IN  VARCHAR2,
                              p_value  IN  VARCHAR2) IS
      BEGIN
        DBMS_SESSION.set_context('MY_VPD_CONTEXT', p_name, p_value);
      END set_parameter;

    END context_api_pkg;
      /

    -- Now enable Flashback Archive
      CREATE TABLESPACE fda_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

    ALTER USER VPD_TEST_DATA_OWNER QUOTA UNLIMITED ON fda_ts;

    CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
        QUOTA 1G RETENTION 1 YEAR;
     
      GRANT FLASHBACK ARCHIVE ON fda_1year TO VPD_TEST_DATA_OWNER;

    GRANT FLASHBACK ARCHIVE ADMINISTER TO VPD_TEST_DATA_OWNER;

    GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO VPD_TEST_DATA_OWNER;

    ALTER TABLE VPD_TEST_DATA_OWNER.PERSON FLASHBACK ARCHIVE fda_1year;

    -- Issue a few updates on the person table
      update VPD_TEST_DATA_OWNER.person set unit_id = 110;

    commit;

    -- Wait a few minutes at this point...
    Connected as VPD_TEST_DATA_OWNER

    VPD_TEST_DATA_OWNER @ GEN1 > select * from vpd_test_data_owner.person;

    no rows selected

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 1493655343

    ----------------------------------------------------------------------------

    | Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time    |

    ----------------------------------------------------------------------------

    |   0 | SELECT STATEMENT  |    | 1 | 13 | 2   (0)| 00:00:01 |

    |*  1 |  TABLE ACCESS FULL| PERSON | 1 | 13 | 2   (0)| 00:00:01 |

    ----------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      1 - filter("UNIT_ID"=(-1))

    Then issues an as of:

    THEN

    VPD_TEST_DATA_OWNER @ GEN1 > select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-1;

    PERSON_ID

    ----------

    SURNAME

    --------------------------------------------------------------------------------

      UNIT_ID

    ----------

        12345

    MEIN

      10

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3361102314

    --------------------------------------------------------------------------------

    ---------------------------------

    | Id  | Operation   | Name | Rows | Bytes | Cost (

    %CPU)| Time | Pstart| Pstop |

    --------------------------------------------------------------------------------

    ---------------------------------

    |   0 | SELECT STATEMENT   | |     2 |   156 |    20

    (10)| 00:00:01 | | |

    |   1 |  VIEW   | |     2 |   156 |    20

    (10)| 00:00:01 | | |

    |   2 |   UNION-ALL   | | | |

        | | | |

    |*  3 |    FILTER   | | | |

        | | | |

    |   4 |     PARTITION RANGE SINGLE| |     1 |   106 |    10

      (0)| 00:00:01 |   KEY |     1 |

    |*  5 |      TABLE ACCESS FULL   | SYS_FBA_HIST_224766 |     1 |   106 |    10

      (0)| 00:00:01 |   KEY |     1 |

    |*  6 |    FILTER   | | | |

        | | | |

    |   7 |     MERGE JOIN OUTER   | |     1 |  2041 |    10

    (20)| 00:00:01 | | |

    |   8 |      SORT JOIN   | |     1 |    13 |     7

    (15)| 00:00:01 | | |

    |*  9 |       TABLE ACCESS FULL   | PERSON |     1 |    13 |     6

      (0)| 00:00:01 | | |

    |* 10 |      SORT JOIN   | |     1 |  2028 |     3

    (34)| 00:00:01 | | |

    |* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_224766 |     1 |  2028 |     2

      (0)| 00:00:01 | | |

    --------------------------------------------------------------------------------

    ---------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      3 - filter("TIMESTAMP_TO_SCN"([email protected]!-1)<105021047)

      5 - filter("ENDSCN"<=105021047 AND ("OPERATION" IS NULL OR "OPERATION"<>'D')

    AND

            "ENDSCN">"TIMESTAMP_TO_SCN"([email protected]!-1) AND ("STARTSCN" IS NULL O

    R

            "STARTSCN"<="TIMESTAMP_TO_SCN"([email protected]!-1)))

      6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"([email protected]!-1) OR "STARTSCN" IS NULL)

      9 - filter(("VERSIONS_OPERATION" IS NULL OR "VERSIONS_OPERATION"<>'D') AND ("

    VERSIONS_STARTSCN" IS

            NULL OR "VERSIONS_STARTSCN"<="TIMESTAMP_TO_SCN"([email protected]!-1)) AND

    ("VERSIONS_ENDSCN" IS NULL OR

            "VERSIONS_ENDSCN">"TIMESTAMP_TO_SCN"([email protected]!-1)) AND "UNIT_ID"=(

    -1))

      10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))

          filter("RID"(+)=ROWIDTOCHAR("T".ROWID))

      11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>105021047) AND ("STARTSCN"(+)

    IS NULL OR

            "STARTSCN"(+)<105021047))

    Note

    -----

      - dynamic sampling used for this statement (level=2)

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019

    I've had to mess around a little bit with your script to test it, but I think the problem is a defect in 11.2.0.4 that has been fixed by (at least) 12.2.0.1.

    I was a little surprised to see you get ANY results with sysdate - 1 from a script that created a table a few minutes ago, and that's a clue to the problem.  You should be seeing  ORA-01466: unable to read data - table definition has changed when you try that  (after all, the table didn't exist 24 hours ago), but (guesswork here) because that bit of the code path hasn't been called you aren't getting the error and Oracle is carrying on to report data in the right timeframe from the following part of your plan:

    -----------------------------------------------------------------------------------------------------------------| Id  | Operation                 | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------|*  3 |    FILTER                 |                     |       |       |            |          |       |       | 
    |   4 |     PARTITION RANGE SINGLE|                     |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |
    |*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_353051 |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |

    Regards

    Jonathan Lewis

    DuncsDuncsDejan T.
  • rizwanarshad
    rizwanarshad Member Posts: 51 Blue Ribbon
    edited December 2019

    Hi Jonathan,

    Duncan is trying on 11.2.0.4 and the person table has existed for a while before enabling flashback archive.

    I've tried it using 19c (19.3.0.0.0) and I also got the ORA-01466 error you've mentioned using SYSDATE-1.

    However I have done a few updates on the person table and ran the following queries as VPD_TEST_DATA_OWNER:

    select * from person;

    no rows selected

    select * from person as of timestamp to_timestamp('2019-12-20 10:12:00', 'YYYY:MM:DD HH24:MI:SS');

    PERSON_ID SURNAME UNIT_ID

    --------- ------- -------

        12345 MEIN        10

    select * from person as of timestamp to_timestamp('2019-12-20 10:17:00', 'YYYY:MM:DD HH24:MI:SS');

    PERSON_ID SURNAME UNIT_ID

    --------- ------- -------

        12345 MEIN        170

    select * from person as of timestamp to_timestamp('2019-12-20 16:12:00', 'YYYY:MM:DD HH24:MI:SS');

    PERSON_ID SURNAME UNIT_ID

    --------- ------- -------

        12345 MEIN        110

    The system I'm using has limited external access so its difficult to paste the explain plans.

    Thanks,

    Rizwan

    DuncsDuncs
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019

    Latest news - I've reproduced the problem on 12.2.0.1. There's a tiny window of opportunity, possibly dependent on the order of object creation, where the "object definition has changed" error doesn't appear and the old data becomes visible. It may also relate to the way that the timestamp_to_scn() function has a 3 second granularity. Next test, 19.3, then a write up (if I find time) for the blog.

    I'm not sure it's a big problem though, as it may just be literally a 3 second window around the moment that the archive was created rather than being an ongoing issue.  I think if you raise an SR with the execution plan to show the missing security predicate for the 1st half of the union all that might be sufficient to get the attention of a developer.

    Regards

    Jonathan Lewis

    DuncsDuncs
  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited December 2019

    Good stuff

    Thanks so much for taking the time to experiment with this.

    Cheers

    Duncs

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019

    Rizwan,

    Duncan is trying on 11.2.0.4 and the person table has existed for a while before enabling flashback archive.

    Not according to the test script he supplied - unless "a while" equates to a couple of seconds.  Being able to see anything for "sysdate - 1" means the table ought to have existed for at least a day (as you clearly realise) but he seems to be creating it inline with everything else.

    I've repeated my version of the test case on 19.3 as well and the behaviour appears there as well as you've demonstrated - and for the same reason I explained for 11.2.0.4, the nature of the predicate used in the union all view.  My "3 second window" was wrong, though - it was the consequence of my test pattern and the fact that I included a divisor of 1440 (i.e. minutes) while intending to use a divisor for seconds.

    The 19.3 plan looks the same as the 12.2 and 11.2, though you may find that that merge join outer shown in the OP's plan could change to a nested loop or (presumably) a hash join.

    Regards

    Jonathan Lewis

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited December 2019

    If you need this feature to work you need to raise an SR with Oracle support.

    In the meantime I've pinged someone in Oracle with a link to this thread, and I'll be writing up a blog note about the problem some time in the next couple of weeks.

    I've put a couple of execution plans below to make it easier to see the missing predicate.. The plan you posted got a bit messed up in transit.  The first plan below comes from 12.2.0.1, the second from 19.3.0.0. The first happens to use a merge join for the second query block in the union all, the second happens to use a nested loop, but the critical thing is the presence of the first query block, which accesses a table called sys_fba_hist_nnnnnn which (looking at the Predicate Information at lines 3 and 5) never gets the security predicate applied - and that's the table that's returning the data you shouldn't see.

    The 12.2 plan comes from using "autotrace on explain" from an SQL*Plus session

    Plan from 12.2.0.1

    -----------------------------------------------------------------------------------------------------------------

    | Id  | Operation                 | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

    -----------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT          |                     |     2 |    86 |    17  (12)| 00:00:01 |       |       |

    |   1 |  VIEW                     |                     |     2 |    86 |    17  (12)| 00:00:01 |       |       |

    |   2 |   UNION-ALL               |                     |       |       |            |          |       |       |

    |*  3 |    FILTER                 |                     |       |       |            |          |       |       |

    |   4 |     PARTITION RANGE SINGLE|                     |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |

    |*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_353051 |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |

    |*  6 |    FILTER                 |                     |       |       |            |          |       |       |

    |   7 |     MERGE JOIN OUTER      |                     |     1 |  2083 |    10  (20)| 00:00:01 |       |       |

    |   8 |      SORT JOIN            |                     |     1 |    55 |     7  (15)| 00:00:01 |       |       |

    |*  9 |       TABLE ACCESS FULL   | PERSON              |     1 |    55 |     6   (0)| 00:00:01 |       |       |

    |* 10 |      SORT JOIN            |                     |     5 | 10140 |     3  (34)| 00:00:01 |       |       |

    |* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_353051 |     5 | 10140 |     2   (0)| 00:00:01 |       |       |

    -----------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       3 - filter("TIMESTAMP_TO_SCN"([email protected]!-1)<12670390265396)

       5 - filter("ENDSCN"<=12670390265396 AND ("OPERATION" IS NULL OR "OPERATION"<>'D') AND

                  "ENDSCN">"TIMESTAMP_TO_SCN"([email protected]!-1) AND ("STARTSCN" IS NULL OR

                  "STARTSCN"<="TIMESTAMP_TO_SCN"([email protected]!-1)))

       6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"([email protected]!-1) OR "STARTSCN" IS NULL)

       9 - filter("UNIT_ID"=(-1) AND ("VERSIONS_OPERATION" IS NULL OR "VERSIONS_OPERATION"<>'D') AND

                  ("VERSIONS_STARTSCN" IS NULL OR "VERSIONS_STARTSCN"<="TIMESTAMP_TO_SCN"([email protected]!-1)) AND

                  ("VERSIONS_ENDSCN" IS NULL OR "VERSIONS_ENDSCN">"TIMESTAMP_TO_SCN"([email protected]!-1)))

      10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))

           filter("RID"(+)=ROWIDTOCHAR("T".ROWID))

      11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>12670390265396) AND ("STARTSCN"(+) IS NULL OR

                  "STARTSCN"(+)<12670390265396))

    The 19.3 plan comes from memory after settng statistics_level = all, using the "allstats last' option so that you can see very clearly that the A-rows returned by the query all come (for this particular run, which showed me data I shouldn't have seen) from the full tablescan of sys_fba_hist_77373 at operation 4. You can also see that the scan of the person table at operation 7 found two rows that were current and met the predicate "unit_id = -1", these were then eliminated by the filter at opeeration 7

    19.3 plan

    -----------------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                               | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

    -----------------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                        |                         |      1 |        |      5 |00:00:00.01 |      64 |

    |   1 |  VIEW                                   |                         |      1 |      6 |      5 |00:00:00.01 |      64 |

    |   2 |   UNION-ALL                             |                         |      1 |        |      5 |00:00:00.01 |      64 |

    |   3 |    PARTITION RANGE SINGLE               |                         |      1 |      5 |      5 |00:00:00.01 |      37 |

    |*  4 |     TABLE ACCESS FULL                   | SYS_FBA_HIST_77373      |      1 |      5 |      5 |00:00:00.01 |      37 |

    |*  5 |    FILTER                               |                         |      1 |        |      0 |00:00:00.01 |      27 |

    |   6 |     NESTED LOOPS OUTER                  |                         |      1 |      1 |      2 |00:00:00.01 |      27 |

    |*  7 |      TABLE ACCESS FULL                  | PERSON                  |      1 |      1 |      2 |00:00:00.01 |      24 |

    |*  8 |      TABLE ACCESS BY INDEX ROWID BATCHED| SYS_FBA_TCRV_77373      |      2 |      1 |      2 |00:00:00.01 |       3 |

    |*  9 |       INDEX RANGE SCAN                  | SYS_FBA_TCRV_IDX1_77373 |      2 |      1 |      2 |00:00:00.01 |       2 |

    -----------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       4 - filter((("STARTSCN" IS NULL OR "STARTSCN"<=12670391794403) AND "ENDSCN">12670391794403 AND

                  "ENDSCN"<=12670391854831 AND ("OPERATION" IS NULL OR "OPERATION"<>'D')))

       5 - filter(("STARTSCN"<=12670391794403 OR "STARTSCN" IS NULL))

       7 - filter((("VERSIONS_STARTSCN" IS NULL OR "VERSIONS_STARTSCN"<=12670391794403) AND ("VERSIONS_ENDSCN" IS NULL

                  OR "VERSIONS_ENDSCN">12670391794403) AND ("VERSIONS_OPERATION" IS NULL OR "VERSIONS_OPERATION"<>'D') AND

                  "UNIT_ID"=(-1)))

       8 - filter((("ENDSCN" IS NULL OR "ENDSCN">12670391854831) AND ("STARTSCN"<12670391854831 OR "STARTSCN" IS NULL)))

       9 - access("RID"=ROWIDTOCHAR("T".ROWID))

    One of the things I'll be doing when I create a blog note on this issue is to check the 10053 to see if there are indications there of the order of transformations that Oracle uses to apply the two conflicting mechanisms - maybe the code path just (as if saying "just" makes it easy to do) needs to ensure that it applies the VPD before it applies the FDA.

    Regards

    Jonathan Lewis

    Dejan T.
Sign In or Register to comment.