oracle使用过的各种指令.docx

1、oracle 锁表查询set timing on;Select p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a,v$locked_object b,all_objects c where p.addr=a.paddr and cess=cess and c.object_id=b.object_idcmd cd\ 根目录 cd..返回上一目录2、查询相同炉次号的行select heatno from il2_wss03 group by heatno having count(*)1select * from il2_wss03 where heatnoin (select heatno from il2_wss03 group by heatno having count(*)1)3、将计划更新成当前时间update il2_int_msg set ATTRIBUTE5=sysdate where MSG_TABLE_NAME = IL2_MGR.IL2_WSS03 AND ATTRIBUTE5 = TO_DATE(01/01/2000 00:00:00, MM/DD/YYYY HH24:MI:SS)select * from il2_int_msg where MSG_TABLE_NAME = IL2_MGR.IL2_WSS03 AND ATTRIBUTE5 = TO_DATE(01/01/2000 00:00:00, MM/DD/YYYY HH24:MI:SS)4、数据库中复制新表先将原表HMI_LOG改成HMI_LOG1 create table HMI_LOG as select * from HMI_LOG1 create table 新表 as select * from 旧表数据一起复制create table tmp_zsh_info_cdma tablespace IBAS_TS_DATA1 asselect a.device_number,a.customer_no,a.user_no from _cdma a where rownum1001createtable IL2_WSS03_NEW tablespace TS_ERP asselect * fromIL2_WSS03where1=2 create table 新表 as select * from 旧表 where 1=2 空表 create table B.新表 as select * from A.旧表 copy from a/a to b/b create table aaa using select * from aaa;5、删除大于3个月的信息 select count(*) from il2_wss03 delete from IL2_MGR.IL2_WSS03 WHERE (sysdate-MSG_TIME)90; select count(*) from IL2_MGR.IL2_WSS03 WHERE (sysdate-MSG_TIME)90;6、ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源解决方法今天做数据清洗，在做数据操作的时候突然发现了一个小问题需要立即停止在运行的存储过程。于是狂点取消，结果是停下来了，但是发现表被锁起来了，而且是TM锁（表级）。郁闷！！一KILL就给我来个ORA-00031: 标记要删去的会话。郁闷！到网上狂搜解决办法，呵~在这写下解决办法：第一：确认有那些锁，并的出相应的SID和SERIAL#。以便KILLSELECT /*+ rule */s.username,decode(l.type, TM, TABLE LOCK, TX, ROW LOCK, NULL) LOCK_LEVEL,o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,gram,s.osuserFROM v$session s, v$lock l, dba_objects oWHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL第二：使用kill语句alter system