advertisements
_____________________________________________________________________________________________________________________
ROWID:
- It is a unique address identifier or pointer for each row to locate the row resides in the disk
- The details in the ROWID gives Oracle everything it needs to find your row, disk number, block and offset to the block
- ROWID is an 18 digit unique number and that internally represented as a base-64 number.
- Oracle internally manages the ROWID details
- You can see the ROWID values by selecting the ROWID pseudo column in select statement
- Describe table will not display the ROWID
Information contained in ROWID
- Object number of the object
- Data block in the datafile in which the row resides
- The position of the row in the data block(First row is 0)
- The datafile in which the row resides(fist file is 1) and this number is relative to the tablespaces
- Values of the ROWID pseudocolumn have the datatype ROWID or UROWID
Important use of ROWID
- ROWID is the fastest way to access a row in a table
- It shows how the rows in a table stored
- They are the unique identifiers for the rows
Some Facts of ROWID
- ROWID cannot be used as primary key
- If you delete and insert the same in a table, the ROWID may change. If you delete a row, then oracle may reassign its ROWID to a new row inserted later.
- ROWID (any pseudo column) values are not stored anywhere in the database
- You cannot insert, delete, update the ROWID column values
Structure of ROWID
First 6 characters refers to the data object number
Next 3 characters represent relative file number
Next 6 characters represent the block number
Last 3 characters refers to the row number
SQL> select rowid, DEPTNO, DNAME, LOC from dept;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABfsIAAEAAAC70AAA 10 ACCOUNTING NEW YORK
AABfsIAAEAAAC70AAB 20 RESEARCH DALLAS
AABfsIAAEAAAC70AAC 30 SALES CHICAGO
AABfsIAAEAAAC70AAD 40 OPERATIONS BOSTON
AABfsI : Object Number
AAE : Relative file number
AAAC70 : Block number
AAD : row number for the department id 40
DBMS_ROWID Package
DEPT is our example table and the rows in the table are shown below with rowid
SQL> select rowid, DEPTNO, DNAME, LOC from dept;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABfsIAAEAAAC70AAA 10 ACCOUNTING NEW YORK
AABfsIAAEAAAC70AAB 20 RESEARCH DALLAS
AABfsIAAEAAAC70AAC 30 SALES CHICAGO
AABfsIAAEAAAC70AAD 40 OPERATIONS BOSTON
Query to find out the file number & file name using DBMS_ROWID
SQL> SELECT dbms_rowid.rowid_to_absolute_fno('AABfsIAAEAAAC70AAD', 'SCOTT', 'DEPT') FILEID FROM DUAL;
FILEID
----------
4
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=4;
FILE_NAME
-------------------------------------------------------------/data/oracle/oradata/prod9/users01.dbf
OR
SQL> select file_name
2 from dba_data_files
3 WHERE FILE_ID=dbms_rowid.rowid_to_absolute_fno('AABfsIAAEAAAC70AAD', 'SCOTT', 'DEPT');
FILE_NAME
-----------------------------------------
/data/oracle/oradata/prod9/users01.dbf
Query to find out the block number
SQL> select dbms_rowid.rowid_block_number('AABfsIAAEAAAC70AAD') from dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AABFSIAAEAAAC70AAD')
---------------------------------------------------
12020
Query to find out the row number
SQL> select dbms_rowid.rowid_row_number('AABfsIAAEAAAC70AAD') ROW_NUMBER from dual;
ROW_NUMBER
----------
3
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment