oracle开发常用,oracle开发常用脚本总结

--查询数据泵job

select * from sys.dba_datapump_jobs where owner_name='G_PSR_NW0824';

--关闭impdp的job

DECLARE

hdl number;

begin

hdl := dbms_datapump.attach('SYS_IMPORT_FULL_01','G_PSR_NW0824');

DBMS_DATAPUMP.STOP_JOB(hdl,1,0);

end;

1.查询数据库中的锁

select * from v$lock;

select * from v$lock where block=1;

2.查询被锁的对象

select * from v$locked_object;

3.查询阻塞

查被阻塞的会话

select * from v$lock where lmode=0 and type in ('TM','TX');

查阻塞别的会话锁

select * from v$lock where lmode>0 and type in ('TM','TX');

4.查询数据库正在等待锁的进程

select * from v$session where lockwait is not null;

5.查询会话之间锁等待的关系

select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b

where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;

6.查询锁等待事件

select * from v$session_wait where event='enqueue';

--锁表查询

SELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.object_id　= o.object_id

AND l.session_id = s.sid;

--杀死锁表进程

ALTER system kill session '11, 253';

查询及删除重复记录的SQL语句

1、查找表中多余的重复记录，重复记录是根据单个字段(Id)来判断

select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段(Id)来判断，只留有rowid最小的记录

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3、查找表中多余的重复记录(多个字段)

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段)，只留有rowid最小的记录

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

--批量删除分区

--select part_id from sys_partition where (part_id in (0,1,5)) or (part_id>=700 and part_id <=749)

delete sys_partition where part_id not in

(select part_id from sys_partition where (part_id in (0,1,5)) or (part_id>=700 and part_id <=749));

----快速删除海量数据(对于有主键或者唯一约束的表特别有效)

declare

TYPE type_rowid IS TABLE OF rowid INDEX BY PLS_INTEGER;

v_rowid type_rowid;

v_count int default 0;

--定义游标，查询需要删除