Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Using FGAC for implementing history

Re: Using FGAC for implementing history

From: Jonathan Lewis <>
Date: Fri, 14 May 2004 12:05:04 +0000 (UTC)
Message-ID: <c82clg$hhp$>

Note in-line


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Optimising Oracle Seminar - schedule updated May 1st

"Vikas Agnihotri" <> wrote in message

> The intended use of FGAC seems to be for securing data and providing
> different view of the data (at a row-level) depending on who is looking
> at it, etc.
> Would it be a appropriate use of FGAC to use it for history-enabling
It seems unlikely, but you have to figure out exactly what you are trying to achieve, and then see what features best match the requirements. History is almost always a bigger pain than it seems at first sight.
> For example, I have a CUST table with primary key of cust_no. There is a
> requirement to capture month-end history snapshots of the table. I was
> thinking of adding as_of_date to the primary key of the table. The
> "current" data would have a as_of_date of, say, 12/12/3000 or something
> (cant be null because it is part of the PK). Of course, depending on
> data volume, I would create this as a partitioned table with as_of_date
> as a partition key.
I can think of a few side-effects that you might have to worry about if you did this. For example, can you guarantee that every piece of code would set the date for new data correctly ? You could protect it with a pre-row insert trigger (defining a default can leave you with a risk), but then any batch loading would turn into single row processing.
> Then, I would attach a FGAC policy to the table with a default predicate
> of "as_of_date=12/12/3000", so current users of the table wouldnt be
> affected. Then I would provide a function/procedure to change the
> as_of_date in the app. context. For example
> select * from cust; -- would return current data
> exec set_ctx('4/30/2004') -- would add a "as_of_date=4/30/2004" predicate
> select * from cust; -- would return 4/30/2004 data
If the users have to call a function to change the snapshot data, why not simply clone the data into a separate schema (perhaps with snapshot partition arrangement) and get them to change schema ?
> Is this a good use of FGAC?
> If not, why not?
Invisible predicates (even very simple ones) can result in changes in access paths. And some access paths will become untunable - for example, a simple outer join INTO a table with a security predicate cannot be optimised properly in 8.1 unless you set one of the hidden parameters relating to predicate pushing.
> Tom Kyte refered me to Workspace Manager for this, but I found WM to be
> a very immature offering with many bugs and restrictions. See my thread
> on this at
My view of Workspace Manager is that it is a marketing offering, that exists to allow a very limited set of functions look easy to manage. At best, it might be appropriate for running 'what if' scenarios that make small changes to an otherwise fixed data set. You've probably seen how much extra work goes on behind the scenes to support WM. You probably won't want that to happen all the time for an OLTP system.
> Oracle Support refuses to discuss this question, saying that it is a
> case for Oracle Consulting Services.
> If anyone here has any comments, experiences, I would greatly appreciate
> Thanks
Received on Fri May 14 2004 - 07:05:04 CDT

Original text of this message