1、取得一个表的所有字段名用逗号分割
select max(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','),2)) col from (
select COLUMN_NAME,column_id from user_tab_columns where table_name='&表名')
start with column_id=1
connect by column_id=rownum;
2、根据某字段去重 (字段1)
1)查找去重
SELECT DISTINCT 字段1,字段2 FROM 表
注:字段中存在clob类型字段的话,会报错
2)查找去重(去重筛选ID最大的)
SELECT * from 表 Where ID In (Select Max(ID) From 表 Group By 重复字段)
3、删除重复记录
1)删除全部重复记录(慎用)
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
2)删除全部重复记录(保留一条)*
Delete 表 Where ID Not In (Select Max(ID) From 表 Group By 重复字段)
注:此处保留ID最大一条记录
4、查锁表
select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode,
'ALTER SYSTEM KILL SESSION '''||s.sid||', '||s.serial#||''';' command
from V$LOCKED_OBJECT L,V$SESSION S,ALL_OBJECTS O
where L.SESSION_ID=S.sid and L.OBJECT_ID=O.OBJECT_ID;