Pseudocolumns are data associated with table data, as though columns, but not columns stored in the database.
Information on the heirarchical pseudocolumns used with CONNECT BY are on the CONNECT BY page. Information on the NEXTVAL and CURRVAL pseudocolumns are on the SEQUENCES page. The XMLDATA pseudocolumn is documented on the XML Tables page.
When referring to an XMLTable construct without the COLUMNS clause, or when
using a TABLE function to refer to a scalar nested table type, the database returns a virtual table with a single column. This name of this pseudocolumn is COLUMN_VALUE.
ORA_ROWSCN returns, for each
row, the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is
useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs
by transaction committed for the block in which the row resides.
Warning: The mapping of SCN to times is kept in bitmaps in sys.smon_scn_time - this table
normally has about 1440 rows (more or less are possible) representing 5 minute
windows of time over 5 days - 5 days of uptime (so you you start your database
for 1 hour every day and shutdown for the other 23 - you'll have 5*24 days of
history in this table).
If you select the minimum scn from this table (mine was 26447476 - representing
5 days ago for me) you'll get an answer from scn-to-timestamp, if you go back
just ONE scn from that - it fails:
scn_to_timestamp( 26447475 ) from DUAL;
select scn_to_timestamp( 26447475 ) from DUAL
ERROR at line 1: ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
sys%ORA10GR2> select scn_to_timestamp( 26447476 ) from DUAL;
that last bit just shows it is about 5 days in the past - my max...
In 11g this behavior changes
when a Flashback Archive has been created
as you can see from the following note from a member of the 11g
The association between a system change number and a timestamp when the number is generated is remembered by the database for a limited period of time. This period
is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode,
and the retention times of all flashback archives in the
database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.
ROWID is what is referred to a
pseudo-column. It is not data in the database or table so much as it is a mapping of the
location, in a specific datafile of the physical location of a row of data. Since rows can
migrate from location-to-location when they are updated ROWID should never be stored an
never be counted on to be the same in any database.
Indexes are segments storing data as are tables. The
data stored consists of the data from the columns defining the index and the ROWIDs that
correspond with the data. The following creates an index and then dumps the index data
ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 10';
Because ROWID points directly to
the physical location of a row of data it is a faster way to affect a record it is faster
to update or delete using ROWID than it is to perform a full table scan or look-up the
ROWID in an index and then perform the same action.
ROWNUM is what is referred to as a pseudo-column. It is not data in the database or table and has absolutely no relationship to anything in the database, data file, tablespace, or table or to the order in which a row is inserted into a table. Rather it is the number of a row selected from a table and will change depending on the order
in which rows are selected.