oracle添加伪列,Oracle伪列 - jifengtang的个人空间 - OSCHINA - 中文开源技术交流社区...

在oracle10g和下，伪列包括如下内容：

lHierarchical Query Pseudocolumns

分级查询是oracle提供的递归查询语法，在这里不做展开。只有在分级查询下，才可以使用以下伪列：

1.CONNECT_BY_ISCYCLE Pseudocolumn

2.CONNECT_BY_ISLEAF Pseudocolumn

3.LEVEL Pseudocolumn

lSequence Pseudocolumns

Sequence有以下2个伪列：

1.CURRVAL：返回当前sequence值

2.NEXTVAL：增加sequence并返回下一个值

一般用法：

sequence.CURRVAL

sequence.NEXTVAL

lVersion Query Pseudocolumns

Version Query伪列只有在Flashback Version Query时才有效，内容如下：

1.VERSIONS_STARTSCN and VERSIONS_STARTTIME

2.VERSIONS_ENDSCN and VERSIONS_ENDTIME

3.VERSIONS_XID

4.VERSIONS_OPERATION

lCOLUMN_VALUE Pseudocolumn

lOBJECT_ID Pseudocolumn

lOBJECT_VALUE Pseudocolumn

lORA_ROWSCN Pseudocolumn

lROWID Pseudocolumn

lROWNUM Pseudocolumn

lXMLDATA Pseudocolumn

在对普通表做查询时，比较常用的伪列有：ORA_ROWSCN、ROWID、ROWNUM。

ORA_ROWSCN

虽然叫ORA_ROWSCN，不过默认情况下，查询出的该值是从数据文件块头获取的，也就是说，查询出的是block的最近事务的scn，而不是精确到row的scn。

在创建表时，可以指定ROWDEPENDENCIES来使ora_rowscn真正记录行一级的scn。

看一下ROWDEPENDENCIES的定义：Specify ROWDEPENDENCIES if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.

从这里也可以看出一个SCN占用的空间(6 bytes)。SCN的最大值是0xffff.ffffffff，共48位。包括2bytes的高位字节(SCN wrap)和4bytes的低位字节(SCN base)。

SQL>select ora_rowscn,username from t;

ORA_ROWSCN USERNAME

---------- ------------------------------

86516279 SYSTEM

86516279 SYS

86516279 OUTLN

86516279 LINCINQ

86516279 TEST

86516279 LINC

86516279 SPLEXUC

86516279 DIP

86516279 TSMSYS

86516279 WMSYS

86516279 DBSNMP

11 rows selected.

需要查询scn对于的具体时间，可以用函数SCN_TO_TIMESTAMP

SQL>alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL>select scn_to_timestamp(ora_rowscn),username from t;

SCN_TO_TIMESTAMP(ORA_ROWSCN)USERNAME

------------------------------ ------------------------------

2011-06-01 13:13:31SYSTEM

2011-06-01 13:13:31SYS

2011-06-01 13:13:31OUTLN

2011-06-01 13:13:31LINCINQ

2011-06-01 13:13:31TEST

2011-06-01 13:13:31LINC

2011-06-01 13:13:31SPLEXUC

2011-06-01 13:13:31DIP

2011-06-01 13:13:31TSMSYS

2011-06-01 13:13:31WMSYS

2011-06-01 13:13:31DBSNMP

11 rows selected.

ROWID

Rowid是基于64位编码的18个字符，格式如下：

data_object_idfile_idblock_numberrow_number

OOOOOOFFFBBBBBBRRR

SQL>select rowid,username from t;

ROWIDUSERNAME

------------------ ------------------------------

AAAE0LAAOAABQqMAAA SYSTEM

AAAE0LAAOAABQqMAAB SYS

AAAE0LAAOAABQqMAAC OUTLN

AAAE0LAAOAABQqMAAD LINCINQ

AAAE0LAAOAABQqMAAE TEST

AAAE0LAAOAABQqMAAF LINC

AAAE0LAAOAABQqMAAG SPLEXUC

AAAE0LAAOAABQqMAAH DIP

AAAE0LAAOAABQqMAAI TSMSYS

AAAE0LAAOAABQqMAAJ WMSYS

AAAE0LAAOAABQqMAAK DBSNMP

11 rows selected.

通过dbms_rowid包，我们可以获得rowid对应的记录详细信息。

SQL>select dbms_rowid.rowid_object('&1') data_object_id#,

2dbms_rowid.rowid_relative_fno('&1') rfile#,

3dbms_rowid.rowid_block_number('&1') block#,

4dbms_rowid.rowid_row_number('&1') row# from dual;

Enter value for 1: AAAE0LAAOAABQqMAAA

old1: select dbms_rowid.rowid_object('&1') data_object_id#,

new1: select dbms_rowid.rowid_object('AAAE0LAAOAABQqMAAA') data_object_id#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old2:dbms_rowid.rowid_relative_fno('&1') rfile#,

new2:dbms_rowid.rowid_relative_fno('AAAE0LAAOAABQqMAAA') rfile#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old3:dbms_rowid.rowid_block_number('&1') block#,

new3:dbms_rowid.rowid_block_number('AAAE0LAAOAABQqMAAA') block#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old4:dbms_rowid.rowid_row_number('&1') row# from dual

new4:dbms_rowid.rowid_row_number('AAAE0LAAOAABQqMAAA') row# from dual

DATA_OBJECT_ID#RFILE#BLOCK#ROW#

--------------- ---------- ---------- ----------

19723143303800

ROWNUM

在对表的查询中，每返回一条记录，rownum伪列就返回一个数字，代表查询返回的行的编号。

SQL>select rownum,username from t;

ROWNUM USERNAME

---------- ------------------------------

1 SYSTEM

2 SYS

3 OUTLN

4 LINCINQ

5 TEST

6 LINC

7 SPLEXUC

8 DIP

9 TSMSYS

10 WMSYS

11 DBSNMP

11 rows selected.

从下面的例子可以看出，rownum返回的是查询过程中返回记录的顺序，并不是查询结果的序列号。

SQL>select rownum,username from t order by username;

ROWNUM USERNAME

---------- ------------------------------

11 DBSNMP

8 DIP

6 LINC

4 LINCINQ

3 OUTLN

7 SPLEXUC

2 SYS

1 SYSTEM

5 TEST

9 TSMSYS

10 WMSYS

11 rows selected.

SQL>select rownum,username from (select username from t order by username);

ROWNUM USERNAME

---------- ------------------------------

1 DBSNMP

2 DIP

3 LINC

4 LINCINQ

5 OUTLN

6 SPLEXUC

7 SYS

8 SYSTEM

9 TEST

10 TSMSYS

11 WMSYS

11 rows selected.