Pseudo Columns in Oracle


Information such as row numbers and row descriptions are automatically stored by Oracle and is directly accessible, ie. not through tables. This information is contained within pseudo columns. These pseudo columns can be retrieved in queries. These pseudo columns can be included in queries which select data from tables.

Available Pseudo Columns

ROWNUM

If a table is retrieved in an order over-ridden by the ORDER BY clause then the ROWNUM values would not neccessarily appear in ROWNUM order.

select table_name,rownum from all_tables order by table_name;

TABLE_NAME                        ROWNUM
------------------------------ ---------
AUDIT_ACTIONS                          5
BONUS                                 10
CUSTOMER                              19
DEF$_TEMP$LOB                          7
DEPARTMENT                            13
DEPT                                   8
DUAL                                   1
EMP                                    9
EMPLOYEE                              15
ITEM                                  21
JOB                                   14
LOCATION                              12
PRICE                                 18
PRODUCT                               17
PSTUBTBL                               6
SALARY_GRADE                          16
SALES_ORDER                           20
SALGRADE                              11
STMT_AUDIT_OPTION_MAP                  4
SYSTEM_PRIVILEGE_MAP                   2
TABLE_PRIVILEGE_MAP                    3

ROWID

ROWID is the physical database row location of each row of customers in the database as shown in the example below. ROWID values will remain the same regardless of which columns are included in the rest of the query of any specific table. Every table row in every table in a database has a unique ROWID and these ROWIDs can not be duplicated. Assuming that ROWIDs are not altered when for instance moving a block to a new extent then the ROWID could be used as a unique object identifier global to the database. ROWID values will change whenever a table is dropped and recreated. Note that use of ROWID values in DML statements is extremely fast since minimal searching is required since the ROWID represents a physical disk location.

select name,rowid from demo.customer;

NAME                                          ROWID
--------------------------------------------- ------------------
JOCKSPORTS                                    AAAAg+AACAAAAJHAAA
TKB SPORT SHOP                                AAAAg+AACAAAAJHAAB
VOLLYRITE                                     AAAAg+AACAAAAJHAAC
JUST TENNIS                                   AAAAg+AACAAAAJHAAD
EVERY MOUNTAIN                                AAAAg+AACAAAAJHAAE
K + T SPORTS                                  AAAAg+AACAAAAJHAAF
SHAPE UP                                      AAAAg+AACAAAAJHAAG
WOMENS SPORTS                                 AAAAg+AACAAAAJHAAH

ROWID is a unique address for each row in a database. The ROWID pseudo-column represents the physical address of a row in the database either as the initial section of the row in the first block of a chain of blocks containing other chained sections of the row. The only exception is clustered tables where rows in different tables but in the same block can have a single ROWID. Note that ROWIDs represent addresses and since they are pseudo-columns they are not physically stored in the database, they are generated on request. Columns of ROWID type created in tables are not reliable. The only cases where ROWIDs are physically stored in the database are in the case of indexes and for linking of chained rows between blocks. In the case of indexes and index is built containing the columns and column values in each row of the index plus a ROWID which allows direct physical access from the index row found back into the ROWID of the table record which the index is mapped to.

ROWID Format

The Oracle8 ROWID has been altered extensively from that of the Oracle7 ROWID. Oracle7 ROWIDs are invalid in Oracle8. The Oracle8 ROWID is called the extended ROWID. The ROWID is divided into four sections as listed below.

  1. Object Number (000000) - the data object number which identifies the database segment and is a tablespace relative number.
  2. Relative Datafile Number (FFF) - represents the relative datafile to which the row is assigned.
  3. Data Block Number (BBBBBB) - represents the data block in which the row is stored.
  4. Row Number (RRR) - the row in the block in which the row is stored.

Note that in Oracle8 that ROWIDs are tablespace relative and are thus not unique globally to the database but only unique to the tablespace in which the rows represented by those ROWIDs are stored.

SYSDATE

The system stores the current date as SYSDATE. Selecting SYSDATE will return the current date. Days can be added and subtracted from SYSDATE to calculate other dates. For instance, (SYSDATE + 1) will give tommorrow's date and (SYSDATE - 1) will give yesterday's date.

UID

The UID pseudo column is the user identification number for a particular session.

USER

The USER pseudo column contains the name under which the current user is logged into the Oracle database.

select user,name from demo.customer;

USER                           NAME
------------------------------ ---------------------------------------------
SCOTT                          JOCKSPORTS
SCOTT                          TKB SPORT SHOP
SCOTT                          VOLLYRITE
SCOTT                          JUST TENNIS
SCOTT                          EVERY MOUNTAIN
SCOTT                          K + T SPORTS
SCOTT                          SHAPE UP
SCOTT                          WOMENS SPORTS
SCOTT                          NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER