【ORACLE】oracle的一些操作记录

最近一个月外出支援

做数据迁移

之前不怎么使用oracle，也是现学现用的  ，这过程中发现oracle真心强大，比mysql强好多

 

期间写了一些sql，现在保存一下，权当模板了

 

 

 

查看操作进程的剩余时间（第一句）：

SELECT c.STATUS,time_remaining/60 剩余时间分钟,sql_text 执行SQL,machine,message
FROM V$SESSION_LONGOPS a inner join v$sql b on
a.sql_hash_value = b.hash_value
inner join v$session c on a.sid = c.sid
where sofar<totalwork
order by time_remaining desc

select sid,message,time_remaining, start_time,last_update_time,elapsed_seconds from V$SESSION_LONGOPS where time_remaining>0;

select sid,message,time_remaining, start_time,last_update_time,elapsed_seconds from V$SESSION_LONGOPS Order By start_time Desc

select sid,message,time_remaining, start_time,last_update_time,elapsed_seconds from V$SESSION_LONGOPS Order By last_update_time Desc;

 

 

查看被锁的表：

select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

 

 

解锁

alter system kill session '139,213';

 

 

select * from gv$process
where addr in (
select paddr from gv$session where sid =72);

 

Select * from  v$session_wait where sid=139

SELECT sid, total_waits, time_waited
FROM v$session_event
WHERE event='db file sequential read' And sid=72  and total_waits>0;

select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

 

 

 

创建序列：

-- Create sequence
create sequence xulie
minvalue 1
maxvalue 9999999999999999999999999999
start with 11602214
increment by 1
cache 20
cycle;

 

xulie.Nextval

 

创建索引：

Create Index 名字On tablename(字段);

 

删除索引：

drop index 名字

 

 

 

一些基本的语句：

Update tableA a Set aaa=(Select bbbFrom tableB b Where a.id=b.id)；

 

Update tableA a Set aaa=(a.aaa||'0') ;

 

 

Insert Into t_nota_apply_user(   ) Select   From aaa  ;

 

 

 

Update aaaaa Set OLDSTATUS=-1 Where oid In(Select Max(oid) From aaa Group By a, b Having Count(1)>1)
Update aaaaa Set OLDSTATUS=-1 Where oid In(Select Max(oid) From aaa Group By a, b Having Count(1)=1)

 

 

Select * From aaaa a Inner Join  bbbbb b On a.id=b.id

 

Select Sum(amount) As totalmount,id,code From aaaaa  Group By id,code