Table Specialities
Virtual Columns
They are there, but not physically. Virtual Reality with tables? Something alike: They are created "on runtime", so to say.
CREATE TABLE sales (
sales_id NUMBER,
…
sale_category VARCHAR2(6)
GENERATED ALWAYS AS (
CASE
WHEN sales_id < 1000 THEN 'VIP'
WHEN sales_id > 999 AND sales_id < 10000 THEN 'NORMAL'
...
END
) VIRTUAL
);
In this example, the virtual column is sale_category. Its values are never stored to the database, but computed at runtime. Nevertheless, they can even be used as partition criteria.
This feature was introduced with Oracle 11g.
Read-Only Tables
(Temporarily) write-protect a table? With Oracle 11g+ this is as easy as …
ALTER TABLE mytable READ ONLY;
No DML is allowed on a read-only table – that's the idea behind it. Still,
SELECT statements are served – just no UPDATE/INSERT, as it is a
write-protect, not a hide-away. So no changes? Ahem … almost. DDL is still
accepted. And if the table should be made "writeable" again?
ALTER TABLE mytable READ WRITE;
Tables without segments
If someone tells you databases are created: do not believe him. Databases are grown. Or at least, if they have been created once, they have evolved since. And not always for the best.
The nightmare of many a DBA: oodles of unused objects. Empty tables. Nobody knows whether they are still used, as none of the original developers is left with the company. Or the other way around: tables which are populated on the production site, but not used on the development server. Once created, they request their "initial extend". What for?
Starting with Oracle 11gR2, this is no longer the case: something called "deferred segment creation" was invented. Which means, space is no longer requested on table creation, but when the first data is tried into (even if that transaction then rolls back). Child elements (such as indexes) inherit this from their parent table; but while the initial segment of an index now can be dropped by rendering the index UNUSABLE, there's no such thing for the table.
If one is (partially) not happy with this new behaviour: it is changeable on system as well as session level:
ALTER <SESSION | SYSTEM>
SET deferred_segment_creation = <TRUE | FALSE>;