rownum in ORACLE-简单演示 Oracle 数据库针对表...-ORACLE 回收站整理

当前位置: 数据库>oracle

本页文章导读:

▪rownum in ORACLE 转自：http://blog.csdn.net/mitedu/archive/2008/12/22/3584399.aspx1 引言 Oracle中的rownum一般是在实现分页查询时用到，虽然在我做的项目中只实现了分页显示而没有实现真正的分页，但是出于学习的.........

▪简单演示 Oracle 数据库针对表主键并发导致行级锁本文内容软件环境简单演示 Oracle 数据库并发导致行级锁本文简单演示针对表主键并发导致的行级锁。并发是两个以上的用户对同样的数据进行修改（包括插入、删除和修改）。.........

▪ORACLE 回收站整理回收站(Recycle Bin）从原理上来说就是一个数据字典表，放置用户删除（drop）掉的数据库对象信息。用户进行删除操作的对象并没有被数据库删除，仍然会占用空间。除非是由于用户手工进行Pu.........

[1]rownum in ORACLE

来源: 发布时间: 2013-10-29

转自：http://blog.csdn.net/mitedu/archive/2008/12/22/3584399.aspx

1 引言

Oracle中的rownum一般是在实现分页查询时用到，

虽然在我做的项目中只实现了分页显示而没有实现真正的分页，但是出于学习的目的研究了一下oracle的rownum。

在使用查询语句时，我们经常要求返回表中的前n条记录或者是中间的几条记录，

比如在一个大表（假设有10W条数据）要求查询从第1000到1005条的记录。

面对这种查询，我们怎么办呢？mysql和oracle都有自己的解决办法。

2 MySql中的实现

在mysql中，我们可以使用limit语句来实现：

1）查询从第1000到1005条的记录（注意mysql中的记录是从0开始编号的，所以第1000条记录编号为999）

select * from table_name limit 999,5;

2）查询前10条记录

select * from table_name limit 10;

等价于：

select * from table_name limit 0,10;

2）查询从第100条记录开始到表的最后一条记录

select * from table_name limit 99 -l;

mysql提供-L的参数，表示到表的最后一条记录

3 Oracle中的实现

Oracle使用rownum的关键字来实现这种查询：

首先我们假设有一个地域信息表area，其表结构如下图所示：

表中的数据如下图所示（select * from area语句得到的结果）：

1）查询表中的前8条记录
select * from area where rownum <= 8
查询结果如下：

2）查询第2到第8条记录
对于这种形式的查询，oracle不像mysql那么方便，它必须使用子查询或者是集合操作来实现。我们可以使用以下3种方式可以实现：

select id,province,city,district from (select id,province,city,district,rownum as num from area) where num between 2 and 8;

首先根据select id,province,city,district,rownum as num from area得到一个临时表，这个临时表中有一个rownum列（一个伪列，类似与rowid，但又不同于rowid，因为rowid是物理存在的一个列，也就是说Oracle数据库中任何一个表都有一个rowid列，而rownum不是物理存在的），然后在临时表中来查询。

B: select * from area where rownum <= 8 minus select * from area where rownum < 2;

使用集合减运算符minus，该操作返回在第一个select中出现而不在第二个select中出现的记录。

select id,province,city,district from (select id,province,city,district,rownum as num from area) where num >=2

intersect

select * from area where rownum <= 8;

使用集合交运算符intersect，这里绕了一个弯（不过这个弯实现了rownum大于某个数的查询），它是首先利用A的方式查询得到所有rownum大于2的记录，然后再与rownum小于等于8的记录集合做交运算。三种操作得到的结果一样，如下图所示：

3）rownum需要注意的问题

[1] rownum不支持以下方式的查询

a: select * from area where rownum > 2;

b: select * from area where rownum = n; --where n is a integer number lager than 1

注：rownum只支持select * from area where rownum =1的查询。Oracle的官方文档说明如下：

Conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM employees

WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The

second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and

makes the condition false. All rows subsequently fail to satisfy the condition, so no

rows are returned.

因为rownum是根据查询的结果集来对记录进行编号，所以当你查询rownum大于2的记录时会得到一个空的结果集。

因为当oracle查询得到第1条记录时，发现rownum为1不满足条件，然后就继续查询第2条记录，

但此时第2条记录又被编号为1（也即rownum变为1），所以查询得到的始终是rownum=1，因此无法满足约束，

最终查询的结果集为空。

[2] rownum的排序查询问题

Rownum的排序查询是根据表中数据的初始顺序来进行的。Oracle官方文档中说明如下：

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be

reordered by the ORDER BY clause. The results can vary depending on the way the

rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index

to access the data, then Oracle may retrieve the rows in a different order than without

[2]简单演示 Oracle 数据库针对表主键并发导致行级锁

来源: 发布时间: 2013-10-29

本文内容

软件环境
简单演示 Oracle 数据库并发导致行级锁

本文简单演示针对表主键并发导致的行级锁。并发是两个以上的用户对同样的数据进行修改（包括插入、删除和修改）。锁的产生是因为并发。没有并发，就没有锁。并发的产生是因为系统需要，系统需要是因为用户需要。

软件环境

Windows 2003 Server
Oracle 11g Release 1 (11.1)

简单演示 Oracle 数据库并发导致行级锁

首先，打开一个会话 session 1，执行如下操作：

     SQL> select distinct sid from V$mystat;                SID    ----------           118         SQL> create table t (x int primary key);         表已创建。         SQL> insert into t values(1);         已创建 1 行。         SQL> update t set x=10 where x=1;         已更新 1 行。         SQL>

先查看本次会话的 SID，然后创建一个表 t，只有一个名为 x 的字段，且该字段为主键，插入一条数据，并更新该数据。

接下来，打开另一个会话 session 2：

    SQL> select distinct sid from V$mystat;                SID    ----------           137         SQL> update t set x=10 where x=1;

此时，该会话被“卡”在这里不动。只是光标一直在闪……因为，session 2 被 session 1 阻塞。

现在，查看锁定视图 V$LOCK。

    SQL> select sid,type,id1,id2,lmode,request,block      2  from v$lock where sid in (118,137)      3  order by sid;                SID TYPE         ID1        ID2      LMODE    REQUEST      BLOCK    ---------- ----- ---------- ---------- ---------- ---------- ----------           118 TM        128105          0          3          0          0           118 AE            99          0          4          0          0           118 TX        262174      34335          6          0          1           137 TM        128105          0          3          0          0           137 AE            99          0          4          0          0           137 TX        262174      34335          0          6          0         已选择6行。         SQL>

说明：“TYPE”列表示锁的类型；“LMODE”列表示锁的模式；“ID1”和“ID2”列是锁的相关信息。“REQUEST”列是正在什么锁。

SID=118 是第一个会话，SID=137 是第二个会话。第三行，第一个会话 BLOCK=1 表示这个会话正在阻塞其他会话，LMODE=6 表示锁的模式，即行级排他锁。第六行，第二个会话 REQUEST=6 表示当前会话正在等待一个 LMODE=6 的锁。注意，第三行和第六行的 ID1 和 ID2 列完全相同。因为它们指向统一资源，只不过一个是资源的拥有者（SID=118），一个是资源的等待者（SID=137）。

通过这个视图，很容易发现所在——故障定位（trouble shooting）。会话 2 之所以被“卡”住，是因为会话 1 还没提交，而在这张表上，又恰好有要求列值唯一性约束。

通过 SID 号，查看视图 V$SESSION 就可以确定用户信息。

    SQL> select machine from v$session where sid in (118,137);         MACHINE    ----------------------------------------------------------------    NUODE\LN    NUODE\LN         SQL>

因为，两个会话是同一台机器，所以名字一样。

本文链接

[3]ORACLE 回收站整理

来源: 发布时间: 2013-10-29

回收站(Recycle Bin）从原理上来说就是一个数据字典表，放置用户删除（drop）掉的数据库对象信息。用户进行删除操作的对象并没有被数据库删除，仍然会占用空间。除非是由于用户手工进行Purge或者因为存储空间不够而被数据库清掉。

在Oracle 10g数据库中，引入了一个回收站(Recycle Bin)的数据库对象。

回收站，顾名思义，它就是存储被删掉的东西。从原理上来说就是一个数据字典表，放置用户删除（drop）掉的数据库对象信息。用户进行删除操作的对象并没有被数据库删除，仍然会占用空间。除非是由于用户手工进行Purge或者因为存储空间不够而被数据库清掉。数据库有了这样的功能，能够减少很多不必要的麻烦。

如果一个表被删除，那么与该表有关联的对象，例如索引、约束和其他依赖对象都会在前面加bin$$这个前缀。

启动和关闭

你可以使用下面的查询语句来查看回收站的当前状态：

SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

如果返回值为“on”表明回收站是启动的，“off”表明是关闭的。

当然，你可以启动或者关闭回收站里的每个会话（session）和系统（system），代码如下：

ALTER SYSTEM SET recyclebin = ON;

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = OFF;

获取回收站里的内容

你可以使用下面的任意一个语句来获得回收站中的对象：

SELECT * FROM RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;

SELECT * FROM DBA_RECYCLEBIN;

还原

可以使用如下语法来还原被删除的对象：

view sourceprint?1 FLASHBACK TABLE <<Dropped_Table_Name>> TO BEFORE DROP RENAME TO <<New_Table_Name>>;

备注：这里的RENAME是给删除的对象进行重新命名，是一个可选命令。

清空回收站

这里的清空包含两种情况，第一你可以有条件的清空；第二是全部清空。我们先来看看有条件的清空该如何做：

a.清空一个特定的表：

PURGE TABLE <<Table_NAME>>;

b.清空一个特定的索引：

PURGE INDEX <<Index_NAME>>;

c.清空与该表空间有关联的对象：

PURGE TABLESPACE<<Table_NAME>>;

d.清空一个特定用户的表空间对象：

PURGE TABLESPACE<<Table_NAME>> USER <<User_Name>>;

e.清空回收站：

PURGE RECYCLEBIN;

f.当一个表被删除（drop）时就直接从回收站中清空

DROP TABLE <<Table_Name>> PURGE;

Demo

1.启动回收站功能

ALTER SYSTEM SET recyclebin = ON;

2.创建一个DEMO_RECYCLEBIN表

CREATE TABLE DEMO_RECYCLEBIN (COL1 NUMBER);

3.向DEMO_RECYCLEBIN表中插入一条数据

INSERT INTO DEMO_RECYCLEBIN (COL1) VALUES (1); COMMIT;

4.删除（Drop）DEMO_RECYCLEBIN表

DROP TABLE DEMO_RECYCLEBIN;

5.查询

SELECT * FROM USER_RECYCLEBIN;

结果：

6.从回收站中还原DEMO_RECYCLEBIN表

FLASHBACK TABLE DEMO_RECYCLEBIN TO BEFORE DROP;

7.还原后查询

SELECT * FROM DEMO_RECYCLEBIN;

查询结果和删除前是一致的。

8.删除表，并且将其从回收站中清空

DROP TABLE DEMO_RECYCLEBIN PURGE;

本文链接