Oracle小技巧

 

1)查询死锁sid，serial：

SELECT ,t2. SID,t2.SERIAL# FROM V$LOCKED_OBJECT t1,V$SESSION t2 WHERE t1.SESSION_ID=t2.SID;

2）kill掉这个死锁的进程：

　　alter system kill session ‘sid,serial#’; 

 

3)select count(*) from v$session； 
  select count(*) from v$process； 
  查看当前总会话数和进程数，这两个视图就是跟会话及进程有关的重要视图啦，信息都是从这里面取的。 

4)查询那些应用的连接数此时是多少 
select  b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and  b.USERNAME is not null   group by  b.MACHINE  , b.PROGRAM order by count(*) desc; 

 

5)创建用户

 

create user batch identified by batch;

grant create table to batch;

grant select on v_$statname to batch;

grant select on v_$sesstat to batch;

grant select on v_$session to batch;

grant select on v_$mystat to batch;

grant create session to batch;

 

grant resource,connect,RECOVERY_CATALOG_OWNER to batch;

 

alert user batch default tablespace batch;（表空间）

 

purge recyclebin;清空oracle10g的回收站

 

6)months_between(date1,date2)  相差的月