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.
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 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.
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.
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.
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.
The UID pseudo column is the user identification number for a particular session.
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