Pseudocolumns are data associated with table data, as though columns, but not columns stored in the database.
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. 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.
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;
COLUMN_VALUE
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);
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:
sys%ORA10GR2> 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
sys%ORA10GR2> select scn_to_timestamp( 26447476 ) from dual;
that last bit just shows it is about 5 days in the past - my max...
Tom Kyte
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
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 is approximately 3 sec.
CREATE TABLE test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;
SELECT table_name, dependencies
FROM user_tables;
SELECT current_scn
FROM v;
INSERT INTO test VALUES ('ABC');
COMMIT;
INSERT INTO test VALUES ('ABC');
COMMIT;
INSERT INTO test VALUES ('ABC');
COMMIT;
SELECT ORA_ROWSCN, rowid, testcol FROM test;
SELECT current_scn
FROM v;
UPDATE test
SET testcol = 'DEF'
WHERE rownum = 1;
SELECT ORA_ROWSCN, rowid, testcol FROM test;
COMMIT;
SELECT ORA_ROWSCN, rowid, testcol FROM test;
UPDATE test
SET testcol = 'XYZ';
SELECT ORA_ROWSCN, rowid, testcol FROM test;
COMMIT;
SELECT ORA_ROWSCN, rowid, testcol FROM test;
CREATE TABLE test2 AS
SELECT * FROM test;
COMMIT;
SELECT ORA_ROWSCN, rowid, testcol FROM test2;
INSERT INTO test VALUES ('ABC');
UPDATE test SET testcol = 'DEF' WHERE rownum = 1;
UPDATE test2 SET testcol = 'GHI' WHERE rownum = 1;
COMMIT;
SELECT ORA_ROWSCN, rowid, testcol FROM test;
SELECT ORA_ROWSCN, rowid, testcol FROM test2;
ORA_ROWSCN Granularity
CREATE TABLE t (
testcol NUMBER(10))
ROWDEPENDENCIES;
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO t VALUES (i);
COMMIT;
user_lock.sleep(0.1);
END LOOP;
END;
/
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.
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.
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.
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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
ORDER BY object_name)
WHERE inner_rnum < 10;
Version Query Pseudocolumns
versions_xid
The transaction id that created this version of the row
versions_startscn
The SCN in which this row version first occured
versions_endscn
The SCN in which this row version was changed
versions_starttime
The TIMESTAMP in which this row version first occured
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)
Free