ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located in North America that send an email to
firstname.lastname@example.org. Request a Workshop for
your organization today.
Pseudocolumns are data associated with table data, as though columns, but not columns stored in a segment.
The heirarchical pseudocolumns used with CONNECT BY are on a separate, CONNECT BY page, linked at page bottom. NEXTVAL and CURRVAL pseudocolumns are on the SEQUENCES page.
Remember that pseudocolumn names are keywords and should never be used to name an object, column, constant, or variable.
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.
CREATE TYPE phone AS TABLE OF NUMBER;
CREATE TYPE phone_list AS TABLE OF phone;
FROM TABLE(phone(1,2,3)) t;
SELECT t.COLUMN_VALUE FROM
TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t;
FROM TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t;
CREATE TABLE my_customers (
NESTED TABLE phone_numbers STORE AS outer_ntab
(NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);
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:
SQL> SELECT 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
that last bit just shows it is about 5 days in the past - my max...
~ Tom Kyte
In 11g and above this behavior changes when a Flashback Archive
has been created as you can see from the following note from a member of the 11g development team.
"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 not data stored 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 data row.
Since rows can migrate from location-to-location when they are updated ROWID should never be stored and never be counted upon to be the same between accesses.
INSERT INTO organization
INSERT INTO organization
INSERT INTO organization
SELECT ROWID, organization_name
TRUNCATE TABLE organization;
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.
CREATE TABLE ...
CREATE INDEX ...
oradebug dump file_hdrs 10
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 depending on the order in which rows are selected will change.
The XMLEXTRA pseudocolumn is essentially undocumented. An XMLEXTRA object column is created, to store top-level instance data such as namespace declarations.
This demo code comes from /rdbms/admin/catxdbdt.sql
This pseudocolumn is officially undocumented and reserved for internal use.
execute immediate 'insert into
(sys_nc_oid$, XMLEXTRA, xmldata) values (:1, :2, :3)
returning ref(s) into :4'
using '8758D485E6004793E034080020B242C6', extras_i, schema_i
returning into schref;