Re: Generate a CRUD matrix

I would either

1. do as I originally suggested and create a trace file with all the sql statements in it for the session and then write a parser that pulls out the access type and tables accessed from the raw sql.
2. Use highly intrusive auditing for the system for all schema objects during a period of downtime when you have the only session. And then make triply sure you had turned it all off.

to enable auditing

1.create a nice large tablespace
2. move sys.aud$ to this tablespace. (and rebuild any indexes)
3. issue AUDIT SELECT, INSERT, DELETE, UPDATE
     ON fully_qualified_object_name
     BY ACCESS
     WHENEVER SUCCESSFUL;

for each object.

4. run the session and fill your tablespace
5. turn off auditing
5. extract relevant info from aud$
6. truncate aud$.

having a test box to yourself will help since you really, really don't want to do this in a live environment

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"David FitzGerald" <david_at_fitzg.com> wrote in message
news:7f966bc5.0303030516.38391f6e_at_posting.google.com...


> DA Morgan <damorgan_at_exesolutions.com> wrote in message

news:<3E5F96AA.3B7EC881_at_exesolutions.com>...


> s.com> wrote in message

>

> > I'd correct you if I thought you wrong ... but in this case I think

different


> > people have read the request to be asking different things. It appears

the OP


> > should repost and clarify.

>

> Sorry I wasn't as clear as I ought to have been -- I'm still very much

> new to Oracle, and if I try to get too detailed, sometimes I just get

> it plain wrong and confuse others even more!!

>

> > Or at least let us know who did the best job of guessing the intent.

>

> Niall Litchfield seems to have got pretty close, he said :

>

> "Correct me if I'm wrong but running a sql trace on the session and

> then

> TKPROF'ing it will give all the tables accessed during the session as

> originally requested. This is not the same as auditing, but it doesn't

> appear to be auditing that he is after."

>

> This sounds like what I am after. However, I've played around with

> tkprof and it produces huge reports! Absolutely massive. I would

> simply want something along the lines of listing which tables had

> updates performed on them, and which tables had select's performed on

> them. No details of which columns the selects were performed on, or

> what was returned.

>

> Would I need to set a trigger on each table (Am I right in thinking a

> trigger is something fired when the table is accessed?) and from this

> write out to file that a particular action has been taken on this

> table?

>

> Thanks for the continued help,

> David.