| sql trace on users how to [message #53903] |
Wed, 16 October 2002 15:40  |
Teresa
Messages: 36
Registered: May 2002
|
Member |
|
|
I would like to set up sql tracing on some users, can someone please
tell me how can I do it. I needed in order to view how many times some
users are connecting to the database then I need to set up user
profiles for that specific users that are taking to many connections.
Any ideias would be great
Thanks
Teresa
|
|
|
|
| Re: sql trace on users how to [message #53904 is a reply to message #53903] |
Wed, 16 October 2002 16:18  |
Trifon Anguelov
Messages: 514
Registered: June 2002
|
Senior Member |
|
|
Since Oracle 8i, you can create LOGON triggers. When the trigger is fired, you can do whatever actions you have to do. Create LOGON trigger as:
CREATE OR REPLACE TRIGGER users_logon
AFTER LOGON ON DATABASE
BEGIN
place_your_code_here;
END;
/
Hope that helps,
clio_usa
OCP - DBA
Visit our Web site
|
|
|
|
| Re: sql trace on users how to [message #53906 is a reply to message #53903] |
Wed, 16 October 2002 17:40 |
Teresa
Messages: 36
Registered: May 2002
|
Member |
|
|
Thanks Trifon
What I'm looking for is the following, I have permament users logged into the database, through a aplication I want to see how many are doing queries, running jobs etc. since they all use the same userid, I would like to enable sql tracing then I could use TKPROF to view the information. This is oracle 8174
Thanks
Teresa
|
|
|
|
| Re: sql trace on users how to [message #53919 is a reply to message #53906] |
Thu, 17 October 2002 10:48 |
sai sreenivas jeedigunta
Messages: 370
Registered: November 2001
|
Senior Member |
|
|
heyy,
your Question can be solved by implementing more than one solution..
for knowing when the users are logging in and logging off u can write after logon / before logoff triggers and u can dump user data from V$Session to a temp table.. as far as Viewing all the transactions being done by user.. use Logminer.. (provided u need to have your database in Archivelog mode).
|
|
|
|
| Re: sql trace on users how to [message #53931 is a reply to message #53906] |
Fri, 18 October 2002 02:40 |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
use v$sqlarea to find what user do ( join with v$session )
to put a session in trace use built-in package
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( SID,SERIAL,TRUE )
Hope it's helpful !
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
