RE: Is it just me

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 11 Aug 2004 11:45:45 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKOEAEFDAA.mwf@rsiz.com>

Good lord.

I may have to hide for defending this notion for certain audit required databases.

But this is exactly the type of "you never hope to have to read it" data that screams to be segmented and rotated by date.

(Notice I didn't beg the question by dictating partitioning, which might be complication overkill.)

CREATE OR REPLACE SYNONYM functionality facilitates rotating or aging actual underlying tables quite nicely if you can live with a slightly fuzzy date boundary.

This, combined with read only and offline tablespaces can be used to create an audit system that is actually feasible to query for specific time ranges, and practical to maintain for a long time (7 years has some meaning in the US, 25 (groan!) for EPA stuff) until forensics might be required.

PS: I note that you didn't build it Connor, you just saw it and probably recoiled in horror at the waste (or possibly smiled curiously at the irony) of it being created such that it was a useless waste.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Connor McDonald Sent: Wednesday, August 11, 2004 9:31 AM To: oracle-l_at_freelists.org
Subject: Re: Is it just me

Its like the "audit" facility on a system I've recently seen. ins/upd/del on every single table -
every single row change logged to a single audit table which (in order to handle all tables) has
columns (renamed to preserve anonymity)

id    number         (surrogate key)
dt    date           (timestamp)
typ   varchar        (ins/upd/del)
ta    varchar2       (tablename)
c1    varchar2(2000)  first column of table

c2 varchar2(2000) next column of table c3 varchar2(2000) next column of table ...
...
c200 varchar2(2000) next column of table

So now there's several hundred million records in there...Also a bit of bummer that pretty much
any kind of analysis query simply never comes back within reasonable time frames (if it all...).

Thus its data stored that will never be read :-)

• Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
  > On Wed, 11 Aug 2004 06:52:21 -0500, Cary Millsap
  > <cary.millsap_at_hotsos.com> wrote:
  > > If you ever saw a system that was write-mostly over the long term, then
  > > there's data going in that's never coming out. You'd have to wonder why
  > > someone would pay money to store things that are never retrieved.
  >

  ---

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

---

___________________________________________________________ALL-NEW Yahoo!

Messenger - all new features - even more fun! http://uk.messenger.yahoo.com

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 11 2004 - 10:43:01 CDT