How to get SQL commands executed on Oracle database in certain period?

10-05-2006, 03:47 AM

qazmlp

Junior Member

qazmlp's Info

Join Date: Aug 2004

Posts: 10

How to get SQL commands executed on Oracle database in certain period?

Is it possible to get the SQL commands executed on a Oracle database
from a certain time?

We have a system connected with Server-1 and Server-2. Server-1 uses
Oracle RDBMS and Server-2 uses some other database for Data storage.
In regular intervals, the application running on the System should
fetch all the SQL commands executed on Server-1 from the last interval,
convert if required and then apply these changes on the Server-2. I
cannot use redo logs, as I have to apply the changes on a non-Oracle
database. For this reason, I would like to know how exactly I can get
the SQL commands executed on a Oracle database from a certain time(or
in certain time period).

How to get SQL commands executed on Oracle database in certainperiod?

10-05-2006, 08:06 AM

DA Morgan

Junior Member

DA Morgan's Info

Join Date: May 2005

Posts: 474

How to get SQL commands executed on Oracle database in certainperiod?

qazmlp1209@rediffmail.com wrote:

Quote:

Is it possible to get the SQL commands executed on a Oracle database from a certain time? We have a system connected with Server-1 and Server-2. Server-1 uses Oracle RDBMS and Server-2 uses some other database for Data storage. In regular intervals, the application running on the System should fetch all the SQL commands executed on Server-1 from the last interval, convert if required and then apply these changes on the Server-2. I cannot use redo logs, as I have to apply the changes on a non-Oracle database. For this reason, I would like to know how exactly I can get the SQL commands executed on a Oracle database from a certain time(or in certain time period).

If you look at gv$sql you will see a column named LAST_ACTIVE_TIME.
That will give you the last time executed. The command, itself, can
be found in the SQL_FULLTEXT column.

As you didn't supply version or other related information this may,
or may not, work for you.
--
Daniel Morgan
Puget Sound Oracle Users Group

How to get SQL commands executed on Oracle database in certain period?

10-06-2006, 02:59 AM

qazmlp

Junior Member

qazmlp's Info

Join Date: Aug 2004

Posts: 10

How to get SQL commands executed on Oracle database in certain period?

DA Morgan wrote:

Quote:

If you look at gv$sql you will see a column named LAST_ACTIVE_TIME. That will give you the last time executed. The command, itself, can be found in the SQL_FULLTEXT column. As you didn't supply version or other related information this may, or may not, work for you.

Thanks, it helped!

I use the following Oracle version:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi

I used the following command to check the SQL commands executed in the
Oracle Database:
SQL> select SQL_FULLTEXT, LAST_LOAD_TIME from gv$sql where
LAST_LOAD_TIME is not null order by LAST_LOAD_TIME ASC;