11G – DDL Locks can Wait for DML Locks

In previous releases, by default, any DDL locks wouldn’t wait for a DML lock but would fail right away if they couldn’t obtain the DDL lock. In Oracle Database 11g, you can use the new initialization parameter ddl_lock_timeout to specify the duration for which a DDL statement will wait for a DML lock.

The default value of the ddl_lock_timeout parameter is zero, meaning that DDL statements won’t wait for a DML lock. The maximum value of 1,000,000 seconds means you can potentially set the wait duration for as long as 11.5 days. You can use the alter session statement to specify the duration of wait for a DML lock, as shown here:

SQL> alter session set ddl_lock_timeout = 60;
Session altered.

11G – Virtual Column

Sometimes you might want to store data in a column based on the evaluation of an expression. Oracle Database 11g provides a new type of column you can include in a table, called a virtual column. Virtual columns are similar to normal table columns, with two major differences:

1. You can’t write to a virtual column.
2. A virtual column is populated by the evaluation of an expression.

You can do the following things with a virtual column:

1. Use them in both DDL and DML statements.
2. Collect statistics on them.
3. Define indexes on them. The indexes you define are similar to function-based indexes

you create on normal columns. Behind the scenes, Oracle creates a function-based index on the virtual column when you create an index on that column.

Creating a Table with a Virtual Column

To create a virtual column, you must use the clause generated always as after the virtual column name when you create a table. Here’s an example showing how to incorporate a virtual column in a table:

SQL> create table emp (
2 no NUMBER(5) PRIMARY KEY,
3 name VARCHAR2(10) NOT NULL,
4 ni NUMBER(5),
5 sal NUMBER(8,3),
6 rate NUMBER(8,3) generated always as (sal/2000));
Table created.
SQL>

Above example creates the virtual column rate. If you want, you can also use the keyword virtual after this line to make it syntactically complete, but the keyword is purely optional.

The following example shows how to use the optional keyword virtual as part of a table creation statement that also creates a check constraint on the virtual column.

SQL> create table emp
2 (sal number (8,3),
3 rate number (8,3) generated always as (sal/2000)
4 virtual
5 constraint HourlyRate CHECK (rate > 8.00));
Table created.
SQL>

Virtual Columns Limitations

The following are restrictions on virtual columns:

• You can create virtual columns only on ordinary (heap-organized) tables.
• You can’t create virtual columns on an index-organized table, an external table, a temporary table, an object, or a cluster.
• You can’t create a virtual column as a user-defined type, LOB, or RAW.
• All columns in the column expression must belong to the same table.
• The column expression must result in a scalar value.
• The column expression in the generated always as clause can’t refer to another virtual column.
• You can’t update a virtual column by using it in the set clause of an update statement.
• You can’t perform a delete or an insert operation on a virtual column.