Oracle Pseudocolumns
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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.
Page Index
 
COLUMN_VALUE
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. COLUMN_VALUE
CREATE TYPE phone AS TABLE OF NUMBER;
/

CREATE TYPE phone_list AS TABLE OF phone;
/

SELECT t.COLUMN_VALUE
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;

SELECT t.COLUMN_VALUE
FROM TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t;

CREATE TABLE my_customers (
cust_id       NUMBER,
name          VARCHAR2(25),
phone_numbers phone_list,
credit_limit  NUMBER)
NESTED TABLE phone_numbers STORE AS outer_ntab
(NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);
 
OBJECT_ID
Returns the object identifier of a column of an object table or view. Oracle uses this pseudocolumn as the primary key of an object table. OBJECT_ID

- in earlier releases, this pseudocolumn was called SYS_NC_OID$
CREATE OR REPLACE TYPE person_typ AS OBJECT (
ssn NUMBER(9), name VARCHAR2(30), address VARCHAR2(100))
NOT FINAL;
/

CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table
VALUES (person_typ(20, 'Bob Jones', '111-555-1212'));

set linesize 121
col address format a30

SELECT * FROM person_obj_table;

SELECT p.object_id, p.object_value FROM person_obj_table p;
 
OBJECT_VALUE
Returns system-generated names for the columns of an object table, XMLType table, object view, or XMLType view OBJECT_VALUE

-- in earlier releases, this pseudocolumn was called SYS_NC_ROWINFO$
See OBJECT_ID Demo Above
 
ORA_ROWSCN
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

SQL> SELECT scn_to_timestamp(26447476) FROM dual;

SCN_TO_TIMESTAMP(26447476)
---------------------------------------------------------------------------
13-AUG-07 02.05.23.000000000 PM

SQL> SELECT systimestamp - scn_to_timestamp(26447476) FROM dual;

SYSTIMESTAMP-SCN_TO_TIMESTAMP(26447476)
---------------------------------------------------------------------------
+000000005 01:00:23.881952000

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."
ORA_ROWSCN Precision (approximately 3 sec.) CREATE TABLE scnprec (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables;

SELECT current_scn
FROM v$database;

INSERT INTO scnprec VALUES ('ABC');

COMMIT;

INSERT INTO scnprec VALUES ('ABC');

COMMIT;

INSERT INTO scnprec VALUES ('ABC');

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec;

SELECT current_scn
FROM v$database;

UPDATE scnprec
SET testcol = 'DEF'
WHERE rownum = 1;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec;

UPDATE scnprec
SET testcol = 'XYZ';

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec;

CREATE TABLE scnprec2 AS
SELECT * FROM scnprec;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec2;

INSERT INTO scnprec VALUES ('ABC');
UPDATE scnprec SET testcol = 'DEF' WHERE rownum = 1;
UPDATE scnprec2 SET testcol = 'GHI' WHERE rownum = 1;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec;

SELECT ORA_ROWSCN, rowid, testcol
FROM scnprec2;
ORA_ROWSCN Granularity CREATE TABLE scngran (
testcol NUMBER(10))
ROWDEPENDENCIES;

BEGIN
  FOR i IN 1 .. 1000 LOOP
  INSERT INTO scngran VALUES (i);
  COMMIT;
  user_lock.sleep(0.1);
  END LOOP;
END;
/

SELECT ora_rowscn, testcol
FROM scngran
ORDER BY 2;
 
ROWID
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.
ROWID Demo CREATE TABLE organization (
organization_name VARCHAR2(20));

INSERT INTO organization
(organization_name)
VALUES
('AAAAA');

INSERT INTO organization
(organization_name)
VALUES
('BBBBB');

INSERT INTO organization
(organization_name)
VALUES
('CCCCC');

COMMIT;

SELECT ROWID, organization_name
FROM organization;

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 ...

SELECT

oradebug setmypid
oradebug dump file_hdrs 10

or

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.
CREATE TABLE test_table (
seqno        NUMBER(6),
data_element VARCHAR2(10));

ALTER TABLE test_table
ADD CONSTRAINT pk_test_table
PRIMARY KEY (seqno)
USING INDEX;

CREATE SEQUENCE seq;

BEGIN
  FOR i IN 1..50000 LOOP
    INSERT INTO test_table
    (seqno, data_element)
    VALUES
    (seq.NEXTVAL, sys.dbms_crypto.randombytes(5));
  END LOOP;
END;
/

set timing on

DECLARE
 r test_table.seqno%TYPE;
BEGIN
  FOR i IN 1 .. 50000 LOOP
    SELECT seqno
    INTO r
    FROM test_table
    WHERE seqno = i;

    UPDATE test_table
    SET data_element = 'AAAAA'
    WHERE seqno = i;
  END LOOP;
  COMMIT;
END;
/

DECLARE
 r UROWID;
BEGIN
  FOR i IN 1 .. 50000 LOOP
    SELECT ROWID
    INTO r
    FROM test_table
    WHERE seqno = i;

    UPDATE test_table
    SET data_element = 'AAAAA'
    WHERE ROWID = r;
  END LOOP;
  COMMIT;
END;
/
 
ROWNUM
ROWNUM 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.

ROWNUM RETURN NUMBER;
CREATE TABLE organization (
organization_name VARCHAR2(20));

INSERT INTO organization
(organization_name)
VALUES
('AAAAA');

INSERT INTO organization
(organization_name)
VALUES
('BBBBB');

INSERT INTO organization
(organization_name)
VALUES
('CCCCC');

COMMIT;

SELECT ROWNUM, organization_name
FROM organization;

SELECT ROWNUM, organization_name
FROM organization
WHERE organization_name <> 'AAAAA';
Because the first row selected is ROWNUM 1, the second ROWNUM 2, trying to view row 2 with a simple use of ROWNUM will not work.
SELECT ROWNUM, organization_name
FROM organization;

SELECT organization_name
FROM organization
WHERE ROWNUM = 1;

SELECT organization_name
FROM organization
WHERE ROWNUM = 2;

SELECT organization_name
FROM organization
WHERE ROWNUM <= 2;

SELECT organization_name
FROM organization
WHERE ROWNUM < 3;
With an in-line view you can use ROWNUM to obtain records not beginning with the first record.
SELECT rnum, object_name
FROM (
   SELECT rownum rnum, object_name
   FROM all_objects
   WHERE ROWNUM < 100)
WHERE rnum BETWEEN 25 AND 40;
The inner row number is the order of the object names extracted by the query from ALL_OBJECTS. The outer row number is the numbering of the first ten rows selected from the in-line view.
SELECT inner_r num, object_name, rownum outer_rnum
FROM (
  SELECT ROWNUM inner_r num, object_name
  FROM all_objects
  ORDER BY object_name)
WHERE inner_rnum < 10;
 
Version Query Pseudocolumns (also demonstrated on the Flashback pages)
VERSIONS_ENDSCN The SCN in which this row version was changed
VERSIONS_ENDTIME The TIMESTAMP in which this row version was changed
VERSIONS_OPERATION The action that created this version of the row (one of delete, insert, update)
VERSIONS_STARTSCN The SCN in which this row version first occurred
VERSIONS_STARTTIME The TIMESTAMP in which this row version first occurred
VERSIONS_XID The transaction id that created this version of the row
Versions Demo CREATE TABLE fb_test (
program_id  VARCHAR2(3),
line_number NUMBER(10),
customer_id VARCHAR2(4),
order_date  DATE)
ROWDEPENDENCIES;

desc fb_test

BEGIN
  INSERT INTO fb_test VALUES ('747',1,'UAL',SYSDATE-10);
  COMMIT;
  INSERT INTO fb_test VALUES ('757',1,'UAL',SYSDATE-10);
  COMMIT;
  INSERT INTO fb_test VALUES ('767',1,'UAL',SYSDATE-10);
  COMMIT;
  INSERT INTO fb_test VALUES ('777',1,'UAL',SYSDATE-10);
  COMMIT;
  INSERT INTO fb_test VALUES ('787',1,'UAL',SYSDATE-10);
  COMMIT;
END;
/

SELECT current_scn
FROM v$database;

SELECT *
FROM fb_test;

SELECT rownum, rowid, ora_rowscn, program_id, line_number
FROM fb_test;

SELECT versions_xid, versions_startscn, versions_endscn, program_id
FROM fb_test
VERSIONS BETWEEN SCN 7742944 AND 7742945;

SELECT versions_xid, versions_startscn, versions_endscn, program_id
FROM fb_test
VERSIONS BETWEEN SCN 7742944 AND MAXVALUE;

SELECT versions_xid, versions_startscn, versions_endscn, program_id
FROM fb_test
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

SELECT current_scn
FROM v$database;

UPDATE fb_test
SET order_date = order_date+20
WHERE program_id = 787;
COMMIT;

UPDATE fb_test
SET order_date = order_date+1
WHERE program_id = 787;
COMMIT;

SELECT *
FROM fb_test
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
ORDER BY 1;

SELECT program_id, order_date
FROM fb_test
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE program_id = '787';

set linesize 121

SELECTversions_xid, versions_startscn, versions_endscn,
versions_operation, program_id, line_number, customer_id,
order_date
FROM fb_test
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE program_id = '787';

DROP TABLE fb_test PURGE;
CREATE TABLE fb_test (
program_id  VARCHAR2(3),
line_number NUMBER(10),
customer_id VARCHAR2(4),
order_date  DATE)
ROWDEPENDENCIES;

desc fb_test

BEGIN
  INSERT INTO fb_test VALUES ('747',1,'UAL',SYSDATE-10);
  COMMIT;
  dbms_lock.sleep(5);
  INSERT INTO fb_test VALUES ('757',1,'UAL',SYSDATE-10);
  COMMIT;
  dbms_lock.sleep(5);
  INSERT INTO fb_test VALUES ('767',1,'UAL',SYSDATE-10);
  COMMIT;
  dbms_lock.sleep(5);
  INSERT INTO fb_test VALUES ('777',1,'UAL',SYSDATE-10);
  COMMIT;
  dbms_lock.sleep(5);
  INSERT INTO fb_test VALUES ('787',1,'UAL',SYSDATE-10);
  COMMIT;
  dbms_lock.sleep(5);
  UPDATE fb_test
  SET order_date = order_date+20
  WHERE program_id = 787;
  COMMIT;
  dbms_lock.sleep(5);
  UPDATE fb_test
  SET order_date = order_date+1
  WHERE program_id = 787;
  COMMIT;
END;
/

SELECT *
FROM fb_test
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY 1;

col versions_starttime format a22
col versions_endtime format a22

SELECT versions_xid, versions_starttime, versions_endtime,
versions_operation, program_id, line_number, customer_id,
order_date
FROM fb_test
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE program_id = '787';

SELECT *
FROM fb_test
VERSIONS BETWEEN TIMESTAMP MINVALUE
AND TO_TIMESTAMP('14-JUN-05 08.35.55 PM')
WHERE program_id = '787';
 
XMLDATA
The XMLDATA pseudocolumn lets you access the underlying LOB or object relational column to specify additional storage clause parameters, constraints, indexes, and so forth. CREATE TABLE xml_lob_tab of XMLTYPE;

desc xml_lob_tab

SELECT table_name, cache
FROM user_lobs;

ALTER TABLE xml_lob_tab MODIFY LOB (XMLDATA)
(STORAGE (BUFFER_POOL DEFAULT MAXSIZE 2G) CACHE);

SELECT table_name, cache
FROM user_lobs;
 
XMLEXTRA
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 $ORACLE_HOME/rdbms/admin/xdbs111.sql

According to Oracle this is officially undocumented and reserved for internal use.
CREATE OR REPLACE PROCEDURE createCustomAuthTrustType(refs IN REF SYS.XMLTYPE) AS
 trustnmnum        NUMBER(38);
 reftrustnm        REF SYS.XMLTYPE;
 reqparsenum       NUMBER(38);
 refreqparse       REF SYS.XMLTYPE;
 allowregnum       NUMBER(38);
 refallowreg       REF SYS.XMLTYPE;
 trustdescnum      NUMBER(38);
 reftrustdesc      REF SYS.XMLTYPE;
 trustusernum      NUMBER(38);
 reftrustuser      REF SYS.XMLTYPE;
 trustparsenum     NUMBER(38);
 reftrustparse     REF SYS.XMLTYPE;
 grnum             NUMBER(38);
 refgr             REF SYS.XMLTYPE;
 skidtrustelems    XDB.XDB$XMLTYPE_REF_LIST_T;
 refskidtrustelems REF SYS.XMLTYPE;
 anypart           VARCHAR2(4000);
 i                 NUMBER(38);
 reftrustschtyp    REF SYS.XMLTYPE;
 trustschnum       NUMBER(38);
 reftrustsch       REF SYS.XMLTYPE;
 skidtrustschs     XDB.XDB$XMLTYPE_REF_LIST_T;
 refskidtrustschs  REF SYS.XMLTYPE;
BEGIN
  -- create trust-scheme-name element
  trustnmnum := xdb.xdb$propnum_seq.NEXTVAL;
  INSERT INTO xdb.xdb$element e (e.xmlextra, e.xmldata)
  VALUES
  (XMLTYPEEXTRA(XMLTYPEPI('4E00206874 .. cut .. 6368656D61',
   '5000047864 .. cut .. 672E787364',
   '5000037864 .. cut .. 6D2F786462'),
   XMLTYPEPI('523030')),
   XDB.XDB$ELEMENT_T(XDB.XDB$PROPERTY_T(XDB.XDB$RAW_LIST_T(
   '83B8102000 .. cut .. 0C07292728'),
   refs, trustnmnum, 'trust-scheme-name',
   XDB.XDB$QNAME('00','string'), NULL, '01', '00', '00',
   NULL, NULL, 'string', NULL, NULL, NULL, NULL,  NULL, NULL,
   NULL, NULL, NULL, '00', NULL, NULL, NULL, '00', NULL, NULL, '00'),
   NULL, NULL, '00', NULL, NULL, '00', '01', '01', '01', '01',
   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, '00',
   '01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL))
   RETURNING ref(e) INTO reftrustnm;
  .. cut ..
  .. cut ..
  .. cut..
   --returning ref(c) into refcauthtrusttyp;
   COMMIT;
END;
/

Related Topics
 
 
 
 
 
 

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved