With every new release of the database, Oracle introduces hundreds of new features. These features help the DBA in maintaining the database. But sometimes there are a few improvements which are too small to be called a full fledged feature, yet they are so handy that every DBA must know them. In this article we will look at the those small Table related enhancements new in 11g.
DDL Wait Option
Have you ever encountered this while executing a DDL command on a table?
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
This error occurs when table is being used by other user sessions and there are pending transactions in the queue against it. Until the users commit or rollback the transaction or the long transaction finishes, you will not be able to execute any DDL command on the table. So to get the job done, you had to approach the hit and trial method and continue to retry until you got a lock on the table. But it is quite possible that one user session may have released the row the lock on the table but before you noticed another user session acquired the lock. So depending on your luck you can get yourself in some sort of difficulty or annoyance. In 11g Oracle introduced the DDL wait time option which when turned on can automate the hit and trial work for you. You just have to issue the command once. For example.
alter session set ddl_lock_timeout=10;
Now when you issue any DDL command on a table and if the table is busy, Oracle will continue to attempt to obtain the lock again and again for the next 10 seconds. You can increase this time and further this can also be set at the system level.
alter system set ddl_lock_timeout=20;
Read Only Tables
As the name suggests, in 11g you can make tables read only. This is particularly useful in data warehouse environments where tables are only opened for DML during specific time periods. For example you can make a table read only using the following command.
SQL> alter table emp read only;
SQL> select table_name,read_only
2 from user_tables;
Now if you try to execute a DML you will get the error message.
SQL> update empYou can change the table back to read write mode using the following command.
2 set empno=2010;
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP"
SQL> alter table emp read write;
Adding Columns with a Default Value
You can now add columns to a table which has a default value. Previously it was not possible as you could not add columns into an existing table with a default value. This created a bigger problem when you wanted that column to be Not Null. You first have to create column and then execute an update statement to update the column with a suitable value and then apply the Not Null constraint.
This is time consuming and hectic. However now you can simply do this simply by altering the table.
SQL> alter table emp add bonus number default 2000 not null;
You may think that executing this command on a large table may cause problems but it is not the case. That’s because the above statement does not trigger the column update. Instead Oracle notices that the column has a default value and populates that value at runtime every time a SELECT statement is issued.
Thus this little statement can save you massive amount of time and effort.
In above example we added a column bonus to the emp table with a default value of 2000. Suppose we have a slightly different requirement and the bonus field is actual 5 times the value of SAL column. You don’t want to populate this every time you insert a new record and want to automate this so that every time a record is inserted, the bonus column of that record gets populated by multiplying 5 with the value of SAL column. What would you do?
In most cases you will create a trigger to do that. But triggers should be avoided when possible because of the performance overhead, especially true with large tables. Starting from 11g you can now use a virtual column which does the exact same thing. Virtual column are “formula” columns and the value is not stored inside the database segment rather it is calculated at run time.
The example below adds the bonus column as described above.
SQL> alter table emp add bonus numberQuerying the EMP table.
2 generated always as
3 (sal*5) virtual;
SQL> select empno, sal, bonus<
2 from emp;
EMPNO SAL BONUS
---------- ---------- ----------
7369 800 4000
7499 1600 8000
7521 1250 6250
7566 2975 14875
7654 1250 6250
14 rows selected.
We never populated or updated the values of bonus column but yet they are still there. Now, when you add new rows without specifying the value of Virtual column, Oracle will calculate the value accordingly when you query the column.
SQL> insert into emp (empno,sal)
2 values (7777,200);
1 row created.
SQL> select empno,sal,bonus
2 from emp
3 where empno=7777;
EMPNO SAL BONUS
---------- ---------- ---------
7777 200 1000
On the other side if you try to provide the value for bonus column, you will get an error.
SQL> insert into emp (empno,sal,bonus)You can use the virtual columns as any other column. You can even create indexes on them.
2 values (7778,300,1500);
insert into emp (empno,sal,bonus)
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
SQL> create index idx_emp_bonus on emp(bonus);
The index created will be a function-based index.
You may also like read Temporary Tablespace Management Features in Oracle 11g.