dbms_rowid包的使用

dbms_rowid包可以通过表中各条记录的唯一rowid号，来查看这条记录的信息，例如所属object、block等等。这个包很简单，但是对于确定当前行的信息值还是比较有用的。而dbms_rowid包的function可以在SQL中直接使用，下面先找一个rowid出来。

[sql] view plaincopyprint?

1. SQL> select rowid,object_name,object_idfrom t1 where rownum = 1; 
2.  
3. ROWID              OBJECT_NAME           OBJECT_ID 
4. ------------------ -------------------- ---------- 
5. AAAR7jAAEAAAAC7AAA ICOL$                        20 

SQL> select rowid,object_name,object_id from t1 where rownum = 1;ROWID              OBJECT_NAME           OBJECT_ID------------------ -------------------- ----------AAAR7jAAEAAAAC7AAA ICOL$                        20

1、dbms_rowid.rowid_create             --输入相应信息后自己创建一个ROWID并返回，主要用于测试比对

2、dbms_rowid.rowid_info               --返回ROWID确定的各种信息

3、dbms_rowid.rowid_type               --返回ROWID类型(restricted or extended)

  rowid_type_restricted constant integer := 0;
  rowid_type_extended   constant integer := 1;

[sql] view plaincopyprint?

1. SQL> select dbms_rowid.rowid_type('AAAR7jAAEAAAAC7AAA')from dual; 
2.  
3. DBMS_ROWID.ROWID_TYPE('AAAR7JAAEAAAAC7AAA') 
4. ------------------------------------------- 
5.                                           1 

SQL> select dbms_rowid.rowid_type('AAAR7jAAEAAAAC7AAA') from dual;DBMS_ROWID.ROWID_TYPE('AAAR7JAAEAAAAC7AAA')-------------------------------------------                                          1

4、dbms_rowid.rowid_object             --返回该ROWID对应的OBJECT的OBJ#

[sql] view plaincopyprint?

1. SQL> select dbms_rowid.rowid_object('AAAR7jAAEAAAAC7AAA')from dual; 
2.  
3. DBMS_ROWID.ROWID_OBJECT('AAAR7JAAEAAAAC7AAA') 
4. --------------------------------------------- 
5.                                         73443 
6.  
7. SQL> select owner,object_namefrom all_objects where data_object_id = 73443; 
8.  
9. OWNER                          OBJECT_NAME 
10. ------------------------------ -------------------- 
11. U1                             T1 

SQL> select dbms_rowid.rowid_object('AAAR7jAAEAAAAC7AAA') from dual;DBMS_ROWID.ROWID_OBJECT('AAAR7JAAEAAAAC7AAA')---------------------------------------------                                        73443SQL> select owner,object_name from all_objects where data_object_id = 73443;OWNER                          OBJECT_NAME------------------------------ --------------------U1                             T1

5、dbms_rowid.rowid_relative_fno       --返回该ROWID对应的对应文件号

[sql] view plaincopyprint?

1. SQL> select dbms_rowid.rowid_relative_fno('AAAR7jAAEAAAAC7AAA')from dual; 
2.  
3. DBMS_ROWID.ROWID_RELATIVE_FNO('AAAR7JAAEAAAAC7AAA') 
4. --------------------------------------------------- 
5.                                                   4 
6.  
7. SQL> select file_name,file_id,tablespace_namefrom dba_data_files where file_id = 4; 
8.  
9. FILE_NAME                                   FILE_ID TABLESPACE_NAME 
10. ---------------------------------------- ---------- ------------------------------ 
11. +DG/orcl/datafile/users.259.773991911             4 USERS 
12.  
13. SQL> select owner,table_name,tablespace_namefrom dba_tables where table_name ='T1'; 
14.  
15. OWNER                          TABLE_NAME                     TABLESPACE_NAME 
16. ------------------------------ ------------------------------ ------------------------------ 
17. U1                             T1                             USERS 

SQL> select dbms_rowid.rowid_relative_fno('AAAR7jAAEAAAAC7AAA') from dual;DBMS_ROWID.ROWID_RELATIVE_FNO('AAAR7JAAEAAAAC7AAA')---------------------------------------------------                                                  4SQL> select file_name,file_id,tablespace_name from dba_data_files where file_id = 4;FILE_NAME                                   FILE_ID TABLESPACE_NAME---------------------------------------- ---------- ------------------------------+DG/orcl/datafile/users.259.773991911             4 USERSSQL> select owner,table_name,tablespace_name from dba_tables where table_name = 'T1';OWNER                          TABLE_NAME                     TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------U1                             T1                             USERS

6、dbms_rowid.rowid_block_number       --返回该ROWID所在的BLOCK号

[sql] view plaincopyprint?

1. SQL> select dbms_rowid.rowid_block_number('AAAR7jAAEAAAAC7AAA')from dual; 
2.  
3. DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAR7JAAEAAAAC7AAA') 
4. --------------------------------------------------- 
5.                                                 187 

SQL> select dbms_rowid.rowid_block_number('AAAR7jAAEAAAAC7AAA') from dual;DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAR7JAAEAAAAC7AAA')---------------------------------------------------                                                187

7、dbms_rowid.rowid_row_number         --返回该行数据在BLOCK中的相对位置

[sql] view plaincopyprint?

1. SQL> select dbms_rowid.rowid_row_number('AAAR7jAAEAAAAC7AAA')from dual; 
2.  
3. DBMS_ROWID.ROWID_ROW_NUMBER('AAAR7JAAEAAAAC7AAA') 
4. ------------------------------------------------- 
5.                                                 0 

SQL> select dbms_rowid.rowid_row_number('AAAR7jAAEAAAAC7AAA') from dual;DBMS_ROWID.ROWID_ROW_NUMBER('AAAR7JAAEAAAAC7AAA')-------------------------------------------------                                                0

8、dbms_rowid.rowid_to_absolute_fno    --返回相关的完全数据文件号

[sql] view plaincopyprint?

1. SQL> select dbms_rowid.rowid_to_absolute_fno('AAAR7jAAEAAAAC7AAA','U1','T1')from dual; 
2.  
3. DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAR7JAAEAAAAC7AAA','U1','T1') 
4. ---------------------------------------------------------------- 
5.                                                                4 

SQL> select dbms_rowid.rowid_to_absolute_fno('AAAR7jAAEAAAAC7AAA','U1','T1') from dual;DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAR7JAAEAAAAC7AAA','U1','T1')----------------------------------------------------------------                                                               4

9、dbms_rowid.rowid_to_extended        --将restricted类型的ROWID修改为extended

10、dbms_rowid.rowid_to_restricted      --将extended类型的ROWID修改为restricted

11、dbms_rowid.rowid_verify             --查看是否可以对ROWID的类型进行修改

下面是包体定义：

[sql] view plaincopyprint?

1. create orreplace package dbms_rowid is 
2.   ------------ 
3.   --  OVERVIEW 
4.   -- 
5.   --  This package provides procedures to create ROWIDs and to interpret 
6.   --  their contents 
7.  
8.   --  SECURITY 
9.   -- 
10.   --  The execution privilege is granted to PUBLIC. Procedures in this 
11.   --  package run under the caller security. 
12.  
13.  
14.   ---------------------------- 
15.  
16.   ---------------------------- 
17.  
18.   --  ROWID TYPES: 
19.   -- 
20.   --   RESTRICTED - Restricted ROWID 
21.   -- 
22.   --   EXTENDED   - Extended ROWID 
23.   -- 
24.   rowid_type_restricted constant integer := 0; 
25.   rowid_type_extended   constant integer := 1; 
26.  
27.   --  ROWID VERIFICATION RESULTS: 
28.   -- 
29.   --   VALID   - Valid ROWID 
30.   -- 
31.   --   INVALID - Invalid ROWID 
32.   -- 
33.   rowid_is_valid   constant integer := 0; 
34.   rowid_is_invalid constant integer := 1; 
35.  
36.   --  OBJECT TYPES: 
37.   -- 
38.   --   UNDEFINED - Object Number not defined (for restricted ROWIDs) 
39.   -- 
40.   rowid_object_undefined constant integer := 0; 
41.  
42.   --  ROWID CONVERSION TYPES: 
43.   -- 
44.   --   INTERNAL - convert to/from column of ROWID type 
45.   -- 
46.   --   EXTERNAL - convert to/from string format 
47.   -- 
48.   rowid_convert_internal constant integer := 0; 
49.   rowid_convert_external constant integer := 1; 
50.  
51.   --  EXCEPTIONS: 
52.   -- 
53.   -- ROWID_INVALID  - invalid rowid format 
54.   -- 
55.   -- ROWID_BAD_BLOCK - block is beyond end of file 
56.   -- 
57.   ROWID_INVALID exception; 
58.      pragma exception_init(ROWID_INVALID, -1410); 
59.   ROWID_BAD_BLOCK exception; 
60.      pragma exception_init(ROWID_BAD_BLOCK, -28516); 
61.  
62.   --  PROCEDURES AND FUNCTIONS: 
63.   -- 
64.  
65.   -- 
66.   -- ROWID_CREATE constructs a ROWID from its constituents: 
67.   -- 
68.   -- rowid_type - type (restricted/extended) 
69.   -- object_number - data object number (rowid_object_undefined for restricted) 
70.   -- relative_fno - relative file number 
71.   -- block_number - block number in this file 
72.   -- file_number - file number in this block 
73.   -- 
74.   function rowid_create(rowid_type IN number, 
75.                         object_number IN number, 
76.                         relative_fno IN number, 
77.                         block_number IN number, 
78.                         row_number IN number) 
79.                         return rowid; 
80.   pragma RESTRICT_REFERENCES(rowid_create,WNDS,RNDS,WNPS,RNPS); 
81.  
82.   -- 
83.   -- ROWID_INFO breaks ROWID into its components and returns them: 
84.   -- 
85.   -- rowid_in - ROWID to be interpreted 
86.   -- rowid_type - type (restricted/extended) 
87.   -- object_number - data object number (rowid_object_undefined for restricted) 
88.   -- relative_fno - relative file number 
89.   -- block_number - block number in this file 
90.   -- file_number - file number in this block 
91.   -- ts_type_in - type of tablespace which this row belongs to 
92.   --              'BIGFILE' indicates Bigfile Tablespace 
93.   --              'SMALLFILE' indicates Smallfile (traditional pre-10i) TS. 
94.   --              NOTE: These two are the only allowed values for this param 
95.   -- 
96.   procedure rowid_info( rowid_in IN rowid, 
97.                         rowid_type OUT number, 
98.                         object_number OUT number, 
99.                         relative_fno OUT number, 
100.                         block_number OUT number, 
101.                         row_number OUT number, 
102.                         ts_type_in IN varchar2default 'SMALLFILE'); 
103.   pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS); 
104.  
105.   -- 
106.   -- ROWID_TYPE returns the type of a ROWID (restricted/extended_nopart,..) 
107.   -- 
108.   -- row_id - ROWID to be interpreted 
109.   -- 
110.   function rowid_type(row_id IN rowid) 
111.                         return number; 
112.   pragma RESTRICT_REFERENCES(rowid_type,WNDS,RNDS,WNPS,RNPS); 
113.  
114.   -- 
115.   -- ROWID_OBJECT extracts the data object number from a ROWID. 
116.   -- ROWID_OBJECT_UNDEFINED is returned for restricted rowids. 
117.   -- 
118.   -- row_id - ROWID to be interpreted 
119.   -- 
120.   function rowid_object(row_id IN rowid) 
121.                         return number; 
122.   pragma RESTRICT_REFERENCES(rowid_object,WNDS,RNDS,WNPS,RNPS); 
123.  
124.   -- 
125.   -- ROWID_RELATIVE_FNO extracts the relative file number from a ROWID. 
126.   -- 
127.   -- row_id - ROWID to be interpreted 
128.   -- ts_type_in - type of tablespace which this row belongs to 
129.   -- 
130.   function rowid_relative_fno(row_idIN rowid, 
131.                               ts_type_in IN varchar2default 'SMALLFILE') 
132.                         return number; 
133.   pragma RESTRICT_REFERENCES(rowid_relative_fno,WNDS,RNDS,WNPS,RNPS); 
134.  
135.   -- 
136.   -- ROWID_BLOCK_NUMBER extracts the block number from a ROWID. 
137.   -- 
138.   -- row_id - ROWID to be interpreted 
139.   -- ts_type_in - type of tablespace which this row belongs to 
140.   -- 
141.   -- 
142.   function rowid_block_number(row_idIN rowid, 
143.                               ts_type_in IN varchar2default 'SMALLFILE') 
144.                         return number; 
145.   pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS); 
146.  
147.   -- 
148.   -- ROWID_ROW_NUMBER extracts the row number from a ROWID. 
149.   -- 
150.   -- row_id - ROWID to be interpreted 
151.   -- 
152.   function rowid_row_number(row_id IN rowid) 
153.                         return number; 
154.   pragma RESTRICT_REFERENCES(rowid_row_number,WNDS,RNDS,WNPS,RNPS); 
155.  
156.   -- 
157.   -- ROWID_TO_ABSOLUTE_FNO extracts the relative file number from a ROWID, 
158.   -- which addresses a row in a given table 
159.   -- 
160.   -- row_id - ROWID to be interpreted 
161.   -- 
162.   -- schema_name - name of the schema which contains the table 
163.   -- 
164.   -- object_name - table name 
165.   -- 
166.   function rowid_to_absolute_fno(row_idIN rowid, 
167.                                  schema_name IN varchar2, 
168.                                  object_name IN varchar2) 
169.                         return number; 
170.   pragma RESTRICT_REFERENCES(rowid_to_absolute_fno,WNDS,WNPS,RNPS); 
171.  
172.   -- 
173.   -- ROWID_TO_EXTENDED translates the restricted ROWID which addresses 
174.   -- a row in a given table to the extended format. Later, it may be removed 
175.   -- from this package into a different place 
176.   -- 
177.   -- old_rowid - ROWID to be converted 
178.   -- 
179.   -- schema_name - name of the schema which contains the table (OPTIONAL) 
180.   -- 
181.   -- object_name - table name (OPTIONAL) 
182.   -- 
183.   -- conversion_type - rowid_convert_internal/external_convert_external 
184.   --                   (whether old_rowid was stored in a column of ROWID 
185.   --                    type, or the character string) 
186.   -- 
187.   function rowid_to_extended(old_rowidIN rowid, 
188.                              schema_name IN varchar2, 
189.                              object_name IN varchar2, 
190.                              conversion_type INinteger) 
191.                         return rowid; 
192.   pragma RESTRICT_REFERENCES(rowid_to_extended,WNDS,WNPS,RNPS); 
193.  
194.   -- 
195.   -- ROWID_TO_RESTRICTED translates the extnded ROWID into a restricted format 
196.   -- 
197.   -- old_rowid - ROWID to be converted 
198.   -- 
199.   -- conversion_type - internal/external (IN) 
200.   -- 
201.   -- conversion_type - rowid_convert_internal/external_convert_external 
202.   --                   (whether returned rowid will be stored in a column of 
203.   --                    ROWID type, or the character string) 
204.   -- 
205.   function rowid_to_restricted(old_rowidIN rowid, 
206.                                conversion_type INinteger) 
207.                         return rowid; 
208.   pragma RESTRICT_REFERENCES(rowid_to_restricted,WNDS,RNDS,WNPS,RNPS); 
209.  
210.   -- 
211.   -- ROWID_VERIFY verifies the ROWID. It returns rowid_valid or rowid_invalid 
212.   -- value depending on whether a given ROWID is valid or not. 
213.   -- 
214.   -- rowid_in - ROWID to be verified 
215.   -- 
216.   -- schema_name - name of the schema which contains the table 
217.   -- 
218.   -- object_name - table name 
219.   -- 
220.   -- conversion_type - rowid_convert_internal/external_convert_external 
221.   --                   (whether old_rowid was stored in a column of ROWID 
222.   --                    type, or the character string) 
223.   -- 
224.   function rowid_verify(rowid_in IN rowid, 
225.                         schema_name IN varchar2, 
226.                         object_name IN varchar2, 
227.                         conversion_type INinteger) 
228.                         return number; 
229.   pragma RESTRICT_REFERENCES(rowid_verify,WNDS,WNPS,RNPS); 
230.  
231. end;