Home » RDBMS Server » Security » How to trace activities of all users on the database (HPUX,oracle 9i)
| How to trace activities of all users on the database [message #360960] |
Mon, 24 November 2008 08:08  |
sanei05
Messages: 104
Registered: September 2008
|
Senior Member |
|
|
Hi,
I need to trace all the users activities on the database level and need to log those data in a file..
as far as all documents, I have found
SQL> alter system set SQL_TRACE=TRUE scope=spfile;
SQL>shutdown immediate
SQL> startup
When I run above queries,I find the logs but I am not able find which user has performed which action. Can I know the method to trace the username...
|
|
|
|
|
|
|
|
|
|
| Re: How to trace activities of all users on the database [message #361096 is a reply to message #361090] |
Tue, 25 November 2008 00:49  |
sanei05
Messages: 104
Registered: September 2008
|
Senior Member |
|
|
Herre is my trace file., can you please let me know where is the userid. Please
--------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /oracle20/app/oracle/product/9.2
System name: HP-UX
Node name: NETMAN3
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: dbtest1
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 14562, image: oracle@server1 (TNS V1-V3)
*** SESSION ID:(7.499) 2008-11-25 06:37:11.184
=====================
PARSING IN CURSOR #1 len=475 dep=1 uid=0 oct=42 lid=0 tim=3586380332394 hv=71214
0972 ad='33853b60'
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY
= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GR
EGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= '
BINARY' TIME_ZONE= '+05:30' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT = 'HH.MI.SSX
FF AM' NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT = 'H
H.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM TZR'
END OF STMT
PARSE #1:c=0,e=420,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3586380332340
EXEC #1:c=0,e=1189,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3586380395440
=====================
PARSING IN CURSOR #1 len=6 dep=0 uid=0 oct=44 lid=0 tim=3586381929648 hv=2425872
81 ad='33b3e330'
commit
END OF STMT
PARSE #1:c=0,e=350,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3586381929587
XCTEND rlbk=0, rd_only=1
EXEC #1:c=0,e=441,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3586382245198
*** 2008-11-25 06:41:07.413
XCTEND rlbk=0, rd_only=1
---------------------
|
|
|
|
|
|
| Re: How to trace activities of all users on the database [message #361106 is a reply to message #361100] |
Tue, 25 November 2008 01:23 |
sanei05
Messages: 104
Registered: September 2008
|
Senior Member |
|
|
=====================
PARSING IN CURSOR #1 len=6 dep=0 uid=21 oct=44 lid=21 tim=3588934980014 hv=24258
7281 ad='57826160'
commit
END OF STMT
PARSE #1:c=0,e=712,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3588934979972
XCTEND rlbk=0, rd_only=0
EXEC #1:c=0,e=654,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=3588934981427
*** 2008-11-25 07:21:14.990
XCTEND rlbk=0, rd_only=1
Now I have the uid=21. where can I found what is the username for this userid ?
Any views...
Please..
|
|
|
|
|
|
|
|
| Re: How to trace activities of all users on the database [message #361141 is a reply to message #361131] |
Tue, 25 November 2008 03:01 |
sanei05
Messages: 104
Registered: September 2008
|
Senior Member |
|
|
Mike,
I need a help from you again...
As of now, the trace files are going to below destination
/oracle20/app/oracle/admin/testdb1/udump
and this path is default path where oracle traces the user and generates the traces..
Once i give sql_trace=true, I need to differentiate the enabled traces and default traces of the oracle..
Is it possible to redirect the trace files that are generated by sql_trace to a new location..
|
|
|
|
|
|
|
|
|
|
| Re: How to trace activities of all users on the database [message #361191 is a reply to message #361180] |
Tue, 25 November 2008 05:42 |
sanei05
Messages: 104
Registered: September 2008
|
Senior Member |
|
|
yes.. i am trying on that...
also I need to try on auditing too..
Please share your ideas if you have any....
I am just trying auditing now and let me see how this work... The only thing with auditing is that I cannot make the output to be viewed by the non-DBA personnel...
But in sql_trace,, i can make non-dba to view the files...
so i am going for sql_trace instead auditing..
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 21:15:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
