| Query Regarding AUDIT_TRAIL [message #496291] |
Fri, 25 February 2011 22:59  |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Hi Friends,
I would like get select,insert,update commands in particular text file for my one of the schema. if i am enabling audit_trail=os in init.ora and issue
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY SCOTT;
Should i get above statements in text file.
Actually I would like to get all the select,insert,update statements for my particular schema. is there any way to get it from instance or log. is it possible without audit_trail? if you know another way please let me know.
Thanks in Advance,
Chintan
[Updated on: Sat, 26 February 2011 00:54] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query Regarding AUDIT_TRAIL [message #496300 is a reply to message #496299] |
Sat, 26 February 2011 01:17  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It works for me:
MICHEL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY SCOTT;
Audit succeeded.
MICHEL> connect scott/tiger
Connected.
SCOTT> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
SCOTT> connect michel/michel
Connected.
MICHEL> select extended_timestamp from dba_audit_trail where username='SCOTT' and obj_name='EMP';
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
26/02/2011 08:14:05.946 +01:00
Regards
Michel
[Updated on: Sat, 26 February 2011 01:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Regarding AUDIT_TRAIL [message #496311 is a reply to message #496306] |
Sat, 26 February 2011 04:47 |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Thanks Michel
As you say i am enable sql_trace=true in my init.ora, now the problem is that its generates so many unnecessary queries by system. its also creates so many .trc files. it is possible to creates only on trace file and bypass all unnecessary queries, herewith i paste the command.
tkprof ora_16600.trc temp.txt SYS=NO INSERT=temp.sql AGGREGATE=NO
Chintan
|
|
|
|
|
|
| Re: Query Regarding AUDIT_TRAIL [message #496313 is a reply to message #496312] |
Sat, 26 February 2011 05:34 |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Thanks
Actually my plan is to to enable sql_trace=true run web based application, collect all select,insert,update,delete statements, and run all that statements on another server. so i have to get only those statements which are run by application only.
Chintan
|
|
|
|
|
|
| Re: Query Regarding AUDIT_TRAIL [message #496431 is a reply to message #496314] |
Mon, 28 February 2011 04:14 |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Thanks michel
i am finding the way from sql_trace. by the use of tkprof i am collecting all the statements into to one table and then i differentiate all the statements with use of user_id. now i would like to know another thing that, which criteria used by oracle to create .trc files because its generate so many .trc and i have to tkprof unnecessary files which is not containing related schema statements.
Regards
Chintan
|
|
|
|
|
|
|
|
| Re: Query Regarding AUDIT_TRAIL [message #496436 is a reply to message #496431] |
Mon, 28 February 2011 04:25 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:2/ You have a trace file per session
Process id is in the file trace name.
You can set "tracefile_identifier" session parameter if you want something more explicit for you but this must be done BEFORE setting the trace on, so this means you have to do it in a logon trigger not at instance level.
Regards
Michel
[Updated on: Mon, 28 February 2011 04:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Regarding AUDIT_TRAIL [message #496466 is a reply to message #496461] |
Mon, 28 February 2011 05:59 |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Dear michel
As you say i am sending snap of my .trc file listing with the command of "ll -ltrh".
[oracle@Test udump]$ ll -ltrh
-rw-r----- 1 oracle1 oinstall 4.4k Feb 28 12:46 test_ora_3800.trc
[b]
-rw-r----- 1 oracle1 oinstall 4.2k Feb 28 12:46 test_ora_3806.trc
-rw-r----- 1 oracle1 oinstall 9.7k Feb 28 12:46 test_ora_3804.trc
-rw-r----- 1 oracle1 oinstall 285k Feb 28 12:46 test_ora_3802.trc
[/b]
-rw-r----- 1 oracle1 oinstall 117k Feb 28 12:46 test_ora_3779.trc
-rw-r----- 1 oracle1 oinstall 100k Feb 28 12:47 test_ora_3813.trc
-rw-r----- 1 oracle1 oinstall 712 Feb 28 12:48 test_ora_3892.trc
[oracle1@Test udump]$
As i observe some times older process id with newest timestamp. in this case you are right, i have to depend on creation time stamp instead of process id sequence.
Regards
Chintan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
