Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Understanding select...as of timestamp query

Ajay VijayanNov 18 2015 — edited Nov 25 2015

Hi ,

I am stuck with this scenario where we provide production data to developer for some testing purpose by altering the business critical columns by some arbitrary value suing update statement.

Now the problem is when we fire select ... as of timestamp query we are able to view the old altered data.I have created a sample test case scenario:

CREATE TABLE test1 (id number);

inserted some values :

SQL> select * from test1;

        ID

----------

         1

         2

         3

         4

         5

         5

         5

         5

         5

         5

10 rows selected.

SQL> update test1 set id=3 where id=5;

6 rows updated.

SQL> commit;

Commit complete.

Now the data in the table is :

SQL>  select * from test1;

        ID

----------

         1

         2

         3

         4

         3

         3

         3

         3

         3

         3

10 rows selected.

Now when i fire as of timestamp query i am able to see the old data:

SQL> select * from test1 as of timestamp sysdate - 5/1440;

        ID

----------

         1

         2

         3

         4

         5

         5

         5

         5

         5

         5

10 rows selected.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE

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

recyclebin                           string      OFF

Now I would like to know from where does oracle fetch the old data.As per my knowledge it will be fetching this data from undo tablespace if yes then i would like to know is there any way to stop this as this may expose the old data to the developer.

Note:- This is not a proper way of data masking but this is a management call for not to opt data masking tool due to license

This post has been answered by JohnWatson2 on Nov 24 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 23 2015
Added on Nov 18 2015
17 comments
22,002 views