oracle常用sql

查询前十条记录

SELECT * FROM A_TEST_TMP WHERE rownum < 10;

找出多于一条的重复记录

SELECT
	NAME,
	count( 1 ) 
FROM
	A_TEST_TMP 
GROUP BY
	NAME 
HAVING
	count( 1 ) >1

去重 按时间排序 查询时间最早的一条记录

SELECT
    FI.* 
FROM
    ( SELECT T.*, ROW_NUMBER ( ) OVER ( PARTITION BY T.NAME ORDER BY T.YEAR asc ) RW FROM A_TEST_TMP T ) FI 
WHERE
    FI.RW =1

更新数据

UPDATE t_account_trans_info AS ii
INNER JOIN t_user AS uu ON ii.tran_user_id = uu.id
SET ii.text2 = uu.link_man
where ii.tran_user_id = uu.id

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)

merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)   
when matched then update set a.字段=b.字段 --目标表别称a和源表别称b都不要省略
when  not matched then insert (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --目标表别称a可省略,源表别称b不可省略

查询序号，去除排序影响

select row_number() over (order by t.YEAR desc) rn,t.*  from A_TEST_TMP t ;

select  rownum,a.*  from (select t.* from A_TEST_TMP t order by t.YEAR desc)  a ;

删除重复数据

DELETE 
FROM
	A_TEST_TMP 
WHERE
	NAME IN ( SELECT NAME FROM A_TEST_TMP GROUP BY NAME HAVING count( * ) > 1 ) 
	AND rowid NOT IN ( SELECT min( rowid ) FROM A_TEST_TMP GROUP BY NAME HAVING count( * ) > 1 );

删除死锁

SELECT SESS.SID,
       SESS.SERIAL#,
       LO.ORACLE_USERNAME,
       LO.OS_USER_NAME,
       AO.OBJECT_NAME,
       LO.LOCKED_MODE
  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
 WHERE AO.OBJECT_ID = LO.OBJECT_ID
   AND LO.SESSION_ID = SESS.SID;

alter system kill session '3694,29415';

导出、导入dmp文件

-- 导入：
imp 实例名/库名@localhost:1521/orcl file= D:\app\data.dmp full=y ignore=y
-- 导出
exp 实例名/库名@localhost:1521/orcl buffer=4096000 file=D:\app\data.dmp tables=(tb_cl_20210820) log=D:\log.log