oracle常用sql

最新推荐文章于 2024-09-13 21:40:38 发布

a97549824

最新推荐文章于 2024-09-13 21:40:38 发布

阅读量63

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/csk-1/p/7691189.html

版权

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，
而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

分页：
--分页查询一
select * from (select a1.*,rownum rn from (select * from student) a1 where rownum <=5) where rn>=2;

--分页查询二
select a1.* from (select student.*,rownum rn from student) a1 where rn between 3 and 5;

oracle中文乱码问题：

NLS_LANG：select userenv('language') from dual; 设置为查询到的结果

查询所有序列和对应者
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences

查询序列当前值
select 序列名.currval from dual
------------------------

--锁表查询语句
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;

--查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

--释放SESSION SQL:
--alter system kill session 'sid, serial#';
ALTER system kill session '67, 48324';

转载于:https://www.cnblogs.com/csk-1/p/7691189.html