Spying on queries from application?

#1

Hi all

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

WBR
Sonnich

#2

On Wed, 03 Mar 2010 06:51:32 -0800, jodleren wrote:

Quote:

Hi all

We have a management programme, which uses Oracle DB. We (I) also make
some intranet stuff etc for this system, which uses the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not made
by us, we just have our own additions. Therefore I need to copy some of
that action.

Is there a spy that can give me that? The programme uses ODBC. My driver
is 10.2. I dont know about the DB version.

WBR
Sonnich

The name is 10046. James 10046. Set up the trace using either
DBMS_MONITOR, DBMS_SYSTEM.SET_EV or DBMS_SUPPORT.START_TRACE_IN_SESSION
and then analyze it using tkprof, trca, orasrp or Hotsos profiler. That
should tell you everything you should now, and more.

--
http://mgogala.byethost5.com

#3

On Mar 3, 9:51*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

snip

Quote:

Hi all

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

WBR
Sonnich

You can set an after login database trigger and activate low level
tracing if needed.

The trace files can get very big and you don't want to do this for
many sessions probably.

Google around for things like "oracle database login trigger" and/or
"10046 trace" ... proceed cautiously and test out everything in a test
environment before even thinking about possibly doing this in a live
prod environment.

Have you talked to your DBA's about the information that you need?

#4

On Mar 3, 6:25*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

Quote:

On Mar 3, 9:51*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

snip

Hi all

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

WBR
Sonnich

You can set an after login database trigger and activate low level
tracing if needed.

The trace files can get very big and you don't want to do this for
many sessions probably.

Google around for things like "oracle database login trigger" and/or
"10046 trace" ... proceed cautiously and test out everything in a test
environment before even thinking about possibly doing this in a live
prod environment.

Have you talked to your DBA's about the information that you need?

The DBA is long time dead

And I do not know that much about Oracle
yet.
We have a test DB where I can play, I will check it out.

Sonnich

#5

On Mar 3, 6:40*pm, jodleren <sonn... (AT) hot (DOT) ee> wrote:

Quote:

On Mar 3, 6:25*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

On Mar 3, 9:51*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

snip

Hi all

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

WBR
Sonnich

You can set an after login database trigger and activate low level
tracing if needed.

The trace files can get very big and you don't want to do this for
many sessions probably.

Google around for things like "oracle database login trigger" and/or
"10046 trace" ... proceed cautiously and test out everything in a test
environment before even thinking about possibly doing this in a live
prod environment.

Have you talked to your DBA's about the information that you need?

The DBA is long time dead

And I do not know that much about Oracle
yet.
We have a test DB where I can play, I will check it out.

Sonnich

Can Toad (Free) do it?

#6

On Mar 3, 8:40*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

Quote:

On Mar 3, 6:25*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

On Mar 3, 9:51*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

snip

Hi all

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

WBR
Sonnich

You can set an after login database trigger and activate low level
tracing if needed.

The trace files can get very big and you don't want to do this for
many sessions probably.

Google around for things like "oracle database login trigger" and/or
"10046 trace" ... proceed cautiously and test out everything in a test
environment before even thinking about possibly doing this in a live
prod environment.

Have you talked to your DBA's about the information that you need?

The DBA is long time dead

And I do not know that much about Oracle
yet.
We have a test DB where I can play, I will check it out.

Sonnich

dbconsole or it's big brother EM can do it, you can look at all
sessions, pick out cursors and specific SQL to your hearts content.
There is probably a port 550x or 180x access through your browser.
Try https://yourhostname:5500/em You may need to know the system
login or equivalent. We could give more advice if we knew what kind
of host you have, like where to look for the information about ports,
or how to see if it is running.

jg
--
@home.com is bogus.
http://www.fastcompany.com/magazine/53/peters.html

#7

On Mar 3, 3:51*pm, jodleren <sonn... (AT) hot (DOT) ee> wrote:

Quote:

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

If your ODBC version is recent enough, then you can also use the ODBC
trace facility that writes the ODBC calls including the SQLs I think
to a log file. You can enable this in the ODBC administrator.

Of course this is something that you would need to do on each client
computer that you want to trace and the trace file will be written by
the client - the Oracle database tracing facility is more flexible and
allows you even to enable the tracing for an already connected
session. The trace files will however reside on the database server
rather than on the client.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

#8

Quote:

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.

You can set an after login database trigger and activate low level
tracing if needed.

The trace files can get very big and you don't want to do this for
many sessions probably.

Google around for things like "oracle database login trigger" and/or
"10046 trace" ... proceed cautiously and test out everything in a test
environment before even thinking about possibly doing this in a live
prod environment.

Have you talked to your DBA's about the information that you need?

I have access to the database, but as a newbie....
I found this
http://www.dba-oracle.com/t_10046_enable_trace.htm

I use Borland Database Explorer, and using
alter system set timed_statistics=true
then

ALTER SYSTEM SET
EVENT='10046 trace name context forever, level 12'
SCOPE=spfile;

nut should I change the name?

I get data where I can see they are from my dbexplore.exe, and I can
see my queries there - but only after I changed "name" to the user
name I used to login.
The real application does not leave anything - it logs in using
another username, and I have tried to change the alter system
accodingly, but that fails.

What do I do wrong?

Otherwise I am well ahead, defenately something I will use in the
future...

I have used this some times and I get some ~4 trc files... is that
normal?

WBR
Sonnich

oh - can I change the name of the spfile? from sql?

#9

On Mar 4, 10:31*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:

Quote:

We have a management programme, which uses Oracle DB.
We (I) also make some intranet stuff etc for this system, which uses
the same DB.

Now I need to track what this programme does, meaning I want to know
which tables it uses etc for certain actions. The programme is not
made by us, we just have our own additions.
Therefore I need to copy some of that action.

Is there a spy that can give me that?
The programme uses ODBC. My driver is 10.2. I dont know about the DB
version.
You can set an after login database trigger and activate low level
tracing if needed.

The trace files can get very big and you don't want to do this for
many sessions probably.

Google around for things like "oracle database login trigger" and/or
"10046 trace" ... proceed cautiously and test out everything in a test
environment before even thinking about possibly doing this in a live
prod environment.

Have you talked to your DBA's about the information that you need?

I have access to the database, but as a newbie....
I found thishttp://www.dba-oracle.com/t_10046_enable_trace.htm

I use Borland Database Explorer, and using
alter system set timed_statistics=true
then

* ALTER SYSTEM SET
EVENT='10046 trace name context forever, level 12'
SCOPE=spfile;

nut should I change the name?

I get data where I can see they are from my dbexplore.exe, and I can
see my queries there - but only after I changed "name" to the user
name I used to login.
The real application does not leave anything - it logs in using
another username, and I have tried to change the alter system
accodingly, but that fails.

What do I do wrong?

You need to figure out the scope of what you are doing. Since you
want to trace the session of the particular username, you should trace
that particular session, not the entire system. As a newbie, you
probably don't want to messing around with the spfile until you
understand exactly what you are doing.

Quote:

Otherwise I am well ahead, defenately something I will use in the
future...

I have used this some times and I get some ~4 trc files... is that
normal?

WBR
Sonnich

oh - can I change the name of the spfile? from sql?

What exactly do you intend to accomplish by doing this?

You should get into the concepts manual in the docs to understand a
few more basic things. What is a database in ODBC-speak is a
different concept than in Oracle. You are not in Borland anymore.

jg
--
@home.com is bogus.
http://www.royrogers.com/announcement.html

#10

On Mar 4, 1:31*pm, jodleren <sonn... (AT) hot (DOT) ee> wrote:

snip

Quote:

I use Borland Database Explorer, and using
alter system set timed_statistics=true
then

* ALTER SYSTEM SET
EVENT='10046 trace name context forever, level 12'
SCOPE=spfile;

Yeah that's kind of the starting point. The idea is one approach is
to have a trigger look at all the new sessions ( new connections )
and set the tracing for just the database session ( the odbc work )
that you want to get traced.

Do you have permission from IT management for what you are trying to
do?

If the DBA is dead are there other people in your organization that
might be useful in helping you get the information that you need?

At your experience level you really want to figure out how to get this
type of stuff done on a test system first ... please.