Oracle 12c Invisible Columns

With Oracle 12c you can use Invisible Columns to hide table columns for your testing or for other purposes.
Column is invisible for following operations (but you can use normal DML operations for invisible columns):
1. SELECT * FROM <table_name>;
2. DESCRIBE  <table_name> (via sqlplus and OCI)
3. %ROWTYPE attribute declarations in PL/SQL

This way you use invisible columns:
For example:
CREATE TABLE test_table(
  test_id NUMBER,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP );


You can change columns to invisible and to visible:
ALTER TABLE test_table MODIFY (test_name INVISIBLE);
ALTER TABLE test_table MODIFY (test_name VISIBLE);

NOTE! When you set column invisible the column order changes (invisible column is removed from column order). And If you set same column back to visible it is placed last in table column order.


You can also add new columns with invisible on:
ALTER TABLE test_table ADD ( tester_id NUMBER INVISIBLE );


You can also create table with invisible columns. Just add INVISIBLE after column datatype:
CREATE TABLE test_table(
  test_id NUMBER INVISIBLE,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP );


You can make normal SQL DML operations for invisible column like this (for previous test table): INSERT INTO test_table (test_id, test_name, starting_time, ending_time) VALUES (1110, 'Test_1', '12-Oct-13', '15-Oct-13');



NOTE! The following types of tables cannot have invisible columns: External tables, Cluster tables, Temporary tables. Also attributes of user-defined types cannot be invisible.

You can find more info about Invisible Columns here:
Understand Invisible Columns

Oracle 12c Temporal Validity time periods in tables.

In Oracle 12c there is new feature called "Temporal Validity". With it you can create time periods between two columns and use these periods for queries.

Example:
-Create table with Temporal Validity period
(you can also add PERIOD FOR in existing table with "ALTER TABLE" clause):
CREATE TABLE test_table(
  test_id NUMBER,
  test_name VARCHAR2(32),
  starting_time TIMESTAMP,
  ending_time TIMESTAMP,
PERIOD FOR testing_time (starting_time, ending_time));

-Inserts are working just like before (PERIOD FOR is not column)
(There can be also NULL values if table constraints accept those.):
INSERT INTO test_table VALUES (1110, 'Test_1', '12-Oct-13', '15-Oct-13');
INSERT INTO test_table VALUES (1110, 'Test_1', '14-Oct-13', null);

- PERIOD FOR gives more variety for your queries (but you can also query table without it):
-This will return all rows that got given date in their time period (first example row):
SELECT * from test_table AS OF PERIOD FOR testing_time TO_TIMESTAMP('13-Oct-13');

-You can also use Period For in "BETWEEN" clause. This will return both example rows.:
SELECT * from test_table VERSIONS PERIOD FOR testing_time BETWEEN
TO_TIMESTAMP('13-Oct-13') AND TO_TIMESTAMP('16-Oct-13');


NOTE: Flashback Query has been extended to support queries on Temporal Validity dimensions. 

You can find more info about Temporal Validity from here:
Oracle 12c New Features
 and here:
Oracle 12c Desing Basics