SQL那点事

1.如何查找重复数据。

select * from cim_terminal t
 where (t.df_raw_id, t.df_yearmonth) in
       (select df_raw_id, df_yearmonth
          from cim_terminal p
         group by p.df_raw_id, p.df_yearmonth
        having count(*) > 1)
   and rowid not in (select min(rowid)
                       from cim_terminal
                      group by df_raw_id
                     having count(*) > 1);

2.如何删除重复数据，且重复数据只保留一条。

delete from cim_terminal t
 where (t.df_raw_id, t.df_yearmonth) in
       (select df_raw_id, df_yearmonth
          from cim_terminal p
         group by p.df_raw_id, p.df_yearmonth
        having count(*) > 1)
   and rowid not in (select min(rowid)
                       from cim_terminal
                      group by df_raw_id
                     having count(*) > 1); 

当数据量比较大的时候，这种删除方法效率会比较低。比较好的解决办法是有：
         1）采用低效率的存储过程。
         2）借助临时表，重新生成数据。
这两种方法适用于大批量的调整数据。 
3.如何判断表是否被锁，即释放锁

    1)：用以下SQL查看进程列表，判断出被锁定的表

SELECT dob.OBJECT_NAME Table_Name,
       lo.SESSION_ID || ', ' || vss.SERIAL# 删除号,
       lo.locked_mode,
       lo.SESSION_ID,
       vss.SERIAL#,
       vss.action Action,
       vss.osuser OSUSER,
       vss.LOGON_TIME,
       vss.process AP_PID,
       VPS.SPID DB_PID,
       vss.*
  From v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
 Where lo.OBJECT_ID = dob.OBJECT_ID
   and lo.SESSION_ID = vss.SID
   AND VSS.paddr = VPS.addr
 order by 2, 3, DOB.object_name

    2): 删除进程，如之前的“删除号”查找出的结果为“286, 2184”，则运行以下SQL

    ALTER system kill session '286, 2184'