How to remove duplicate rows from a table

Sometimes, the application is inserting "duplicate" rows in a table, let's assume the uniqueness is supposed to be ensured using col1, col2 and col3.
In the case the number of duplicate rows is reasonably small and that we don't really care which row we leave/delete out f the duplicate ones, we could use the delete below to eliminate these rows.
In the case where the number of duplicate is very big, we need to avoid the delete and use "create table as select", to leave only the desired rows.

DELETE FROM dup_table

WHERE rowid not in

(SELECT MIN(rowid)

FROM dup_table

GROUP BY column1, column2, column3);

How to trace remote oracle connections, on the client side?

In order to trace oracle remote connections on the client side, first we need to add a few entries to the sqlnet.ora file; for example, to set trace at the highest level, for oracle support, we need level 16:

 box1:/u01/app/oracle/client_trace> cat /etc/sqlnet.ora
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=/u01/app/oracle/client_trace
LOG_DIRECTORY_CLIENT=/u01/app/oracle/client_trace
TRACE_TIMESTAMP_CLIENT=ON
DIAG_ADR_ENABLED=OFF

The last line, disabling diag is required starting with 11G.

In addition, there are two more steps to follow, to make sure that the trace/log file will be generated as expected:

1) Oracle will look for sqlnet.ora in $ORACLE_HOME/networking/admin, by default, so either update the file in this location or define TNS_ADMIN, in the environment where the connection to the DB is taking place.
2) Make sure the directory destination for trace/log files it has read/write permission for the owner of the process you want to trace; oracle will silently ignore your request and just it won't generate the trace file otherwise.

DBA_HIST_ACTIVE_SESS_HISTORY and locking history information

The AWR historical DBA_HIST* views are a real treasure of information. This time, we are interested in historical information related to locking. the question is, who was blocking my session last night, within a given timeframe?

dba_hist_active_sess_history to the rescue! 

First step, we create our table as follows, to include only the timeframe we are interested in:

 SQL> create table my_hist_active_sess_history nologging parallel(degree 4)

            as select * from

           dba_hist_active_sess_history

           where SNAP_ID in ( select snap_id from dba_hist_snapshot where BEGIN_INTERVAL_TIME >            to_timestamp('06-jun-13 10:00:00','dd-mon-yy hh24:mi:ss')

           and BEGIN_INTERVAL_TIME <= to_timestamp('06-jun-13 11:00:00','dd-mon-yy hh24:mi:ss'));

The second step will be to query this table for the information we need; in my case, I'm looking for session which were blocked during this time:

SQL>

select event,sum(time_waited)/1000000 s,session_id,module,BLOCKING_SESSION

from my_hist_active_sess_history

where EVENT like 'enq%'

group by event,session_id,module,BLOCKING_SESSION

order by 2;

This is supposed to give us the blocking_session, together with the event and time waited.