Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 02, 2016 - 3:06 pm UTC

Last updated: November 03, 2016 - 12:31 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi


We have need to audit who has seen personal data in database.

Fine Grained Auditing seemed to be good candidate for the job.
It is however generating a lot of data, which should be kept for two years.
About 10 gb of uncompressed data is currently being generated each week for this system.


First idea was to dump fga_log$ rows into file and import them into partitioned database
with interval partitioning and where old partitions could be compressed. This database could be also be
used when other systems would implement fga.

However we don't have any good ETL tools to handle lob and long fields and dumping/writing them into files.

Other possibilities were are considering
- make database link and read data from that source database and insert rows into partitioned database
and then delete inserted rows from source database However there is long column (FGA_LOG$.PLHOL in table).
- use datapump export to extract data from fga_log$, after successfull extract delete rows from source database
and import data into partitioned table.


We are not very keen on using DBA_FGA_AUDIT_TRAIL, because we would like to store whole sql-statement.
Application is using bind variables, so lsqltext (lob field) would also benefit from compressing.
Is basic compression used for lob columns ? Other compression options ?

What is table FGA_LOG$FOR_EXPORT_TBL for ?

I didn't succeed to find description of columns of fga_log$. Is it available somewhere ?


How would You implement this ?


All ideas and suggestions are much appreciated.




and Connor said...

To delete records you can manually delete from it (that is support), or you can use DBMS_AUDIT_MGMT for a more controlled mechanism.

For copying the data, just convert the problem column to a clob, eg

SQL> create table tt as select scn, plhol from sys.fga_log$;
create table tt as select scn, plhol from sys.fga_log$
                               *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> create table tt as select scn, to_lob(plhol) clobcol from sys.fga_log$;

Table created.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.