Dirk-Jan’s blog

If you need to get rid of some table rows, in my case a Sun IDM logging table, you can use the following steps.

1. Create a backup in case you have done some thing wrong u can always revert back.

create table waveset.log_backup as select * from waveset.log;

2. Check if these are the records you want to keep and rest all delete.

select * from waveset.log_backup where repomod between'26-MAR-09' and '01-MAY-09';

3. Delete logging from timestamp type collumn ‘REPOMOD’ between dates:

delete from waveset.log_backup where repomod between'26-MAR-09' and '01-MAY-09';

4. if this works do the same on the real table instead of the backup table.

Optionally you can repeat the same trick on the waveset.syslog table, which also can grow to huge proportions.

For a Tomcat installation I needed to connect to a Oracle RAC database backend. I figured out the correct connection string, shown below:

If you want to know what SQL statements are actually being executed by an application because something goes wrong and there is no clear error message explaining what’s happening you can use SQL Trace. SQL Trace can be enabled on the current session, the session of another user or the complete database.

To enable SQL Trace on the current session

alter session set sql_trace = true;

You can find the output of the trace in the udump directory of the database (see the value of parameter user_dump_dest). You can chose to give the tracefile another name, see the next example:

alter session set sql_trace = true;
alter session set tracefile_identifier = customname;

When done tracing you’ll need to stop the trace:

alter session set sql_trace = false;

To enable SQL trace on another session
You’ll first need to know which session to trace, by looking up the sid and serial#:

select s.sid,
s.serial#,
s.osuser,
s.program
from v$session s;

Start the trace on sid number 8 and serial 13607:

alter system set timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(8, 13607, true);

Carry out the to trace actions and stop the trace:

execute dbms_system.set_sql_trace_in_session(8,13607, false);

You can find the output again in the udump destination.

Trace the complete database

Start SQL Trace on the complete database:

alter system set sql_trace = true scope=memory;

Stop SQL Trace on the complete database:

alter system set sql_trace = false scope=memory;