Quick Tips : A ‘How to’ Section

 How to find what is locking a table.

       1. Query from DBA_OBJECTS to find the object_name of the table getting locked.
       2. Query from V$LOCK where id1 = 'table_name', get sid.
       3. Query from v$PROCESS where pid = sid.

Now the V$PROCESS has information on what is locking the table.

How To Find the Server Name and IP Address

declare 
  v_host_name v$instance.host_name%type; 
  v_ip_address varchar2(50); 
begin 
  select host_name into v_host_name from v$instance; 
  dbms_output.put_line('the database server name is ' || v_host_name); 
  SELECT UTL_INADDR.GET_HOST_ADDRESS(v_host_name) into v_ip_address FROM DUAL; 
  dbms_output.put_line('the database server ip address is ' || v_ip_address); 
end; 
/ 

SQL> SELECT host_name, UTL_INADDR.GET_HOST_ADDRESS(host_name) ip FROM v$instance;

What Code is executed by a Session?

Use the following code to see the ACTIVE Sessions:
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box, substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user, substr(b.program,1,30) program
from v$session b, v$process a
 where b.paddr = a.addr
   and type='USER'
   and status = 'ACTIVE'
order by spid;


Use the following script to retrieve the SQL statement for a particular user:
select SQL_TEXT from V$SQLAREA
  where (address, hash_value) 
     IN (select SQL_ADDRESS, SQL_HASH_VALUE
              from V$SESSION
              where SID = &SID);

How to Delete Duplicate Records

DELETE FROM &&table_name
   WHERE rowid NOT IN (SELECT max(rowid)
                         FROM &&table_name
                         GROUP BY &colums_with_duplicates);

Another way

SELECT a.rowid
   FROM &&table_name a
   WHERE a.rowid > (SELECT min(b.rowid)
                       FROM &&table_name b
                       WHERE a.&&column_name = b.&&column_name);

Then:
DELETE from &&table_name
    WHERE a.rowid > (SELECT min(b.rowid)
                       FROM &&table_name b
                       WHERE a.&&column_name = b.&&column_name);

****Most efficient way to remove duplicate rows
 

This script uses a hash join -- the most efficient way of joining huge tables -- to find duplicate rows.

--Firstly,  Set hash join enabled

DELETE FROM <table>
  WHERE rowid IN (SELECT t1.rowid
                    FROM <table> t1, <same-table> t2
                    -- primary key is (a1, a2)
                    WHERE t1.a1 = t2.a1
                      AND t1.a2 = t2.a2
                      AND t1.rowid < t2.rowid);

****Another METHOD (by METALINK) to find duplicates for one field****
To find duplicate keys from a table tx:
select key, count(key) no_of_duplicates
   from tx
   group by key
   having count(key) > 1;

*** This script will remove duplicate rows. Suppose a table contains 3 columns.

 To remove the duplicate rows write the following command, where a and b are aliases of the same table.
Delete from table_A a
   where a.rowid > any (select rowid
                           from Table_B b
                           where a. = b.
                             and a. = b.
                             and a. = b.);

To Identify which user is using which Temporary Tablespace

select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid,
       a.serial#, a.username, a.osuser, a.status
from v$session    a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;