Consult Queries in the Data Dictionary

[weslleis]

Dear friends,

is my first post, and already face to know if anyone can help me with the question below:

I need to see the Queries that determined Owner / user is performing in the database, but I have not yet been able to find in the dice dictionary.

grateful.



* Oracle version: 10g
* Operating system: Linux / Red Hat

[victorhugomuniz]

Welcome brother

See if it helps you ..

To see the instructions executed:

Select all

 
select sql_text, object_status, first_load_time, username, sid, lockwait 
  from v$session ses, v$sql sql 
 where ses.sql_id = sql.sql_id 

Filtering by User:

Select all

 
   and ses.USERNAME = 'XXXX' 

Filtering by the application:

Select all

 
   and ses.program = 'XXXXXX' 

[weslleis]

Thank you Victor Hugo , that's exactly what I was looking for.

[bernardoramos]

Hello ...

I just registered in the forum. I found some things interesting and better then participate.

I took courses of Oracle 8 and 9i but I did not act. Looonga story ...
There is a problem here in the office and would like to return to active, reheat the engines.

As every good problem, it is not trivial to mortals.

We have a more documentation application, running with Oracle. There are more than 2000 tables!

To win a client, I need to integrate this new product with the bank of this previous application. Will act as an old bank user.

Basically, after entering a human action, I need to find the records involved in the operation. Every x seconds our application will consult to see changes and take attitudes in another bank.

How do I find out which tables have changed from a certain time, or in the last 5 to 20 seconds?

would appreciate your help.

[victorhugomuniz]

Select all

 
select sql_text, object_status, first_load_time, username, sid, lockwait, sql.LAST_ACTIVE_TIME 
  from v$session ses, v$sql sql 
 where ses.sql_id = sql.sql_id  
   and sql_text like '%insert%table_name%' 
   and sql.LAST_ACTIVE_TIME between (SYSDATE - 20/86400) 
   and sysdate; 

To get all altered you can include the update too .. or then the name of the table

Select all

 
   and sql_text like '%insert%table_name%' 

20 was the seconds that you asked for .. 20 seconds behind until now

Select all

 
   and sql.LAST_ACTIVE_TIME between (SYSDATE - 20/86400) 
   and sysdate;