Oracle 11gR2 - New Featuers for DBA

1) Enhanced Online Index Creation and Rebuild :Before Oracle 11g, rebuilding an index online on a table with extensive DML leads to unpredictable results because table exclusive lock (X) is required by rebuilding process. In Oracle 11g, rebuilding an index acquire shared exclusive lock (SX) on the table allowing DML to go on uninterrupted. This new enhancement applies in the following statements:
1. Create index online
2. Rebuild index online
3. Create materialized view log


2) DDL_LOCK_TIMEOUT - Avoid ORA-00054: resource busy error message
Modify a table:
Step 1) Alter table jtable add ( last_name varchar2(20));
*
ORA-0054:resource busy and aquire with nowait specified or timeout expired
Step 2) Alter session set ddl_lock_timeout=30; (seconds)
Step 3) Alter table jtable add ( last_name varchar2(20));
Table altered.

3) Invisible Indexes:  To avoid drop and re-create the index :   Invisible indexes are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE at the instance or session level.

Index is invisible to optimizer:
create index index_name on table_name (col_name) INVISIBLE;
alter index index_name INVISIBLE;
OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE

Index is visible to optimizer:
alter index index_name VISIBLE;
OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE

select VISIBILITY from DBA|all|user_INDEXES views.

4) Adding columns with a default value:
alter table table_name add col_name varchar2(6)  'ABC' not null;
If table has 900 million rows, it will generate larger amount of undo and redo hence performance overhead.
In Oracle 11g, the default value will be stored in the data dictionary instead of the table itself. This will save space and significantly reduce time to execute the modification statement. Update the data dictionary with default value - hence no redo and undo. Oracle 11g will not issue an update all the records of the table.

5) Read-only Tables: Make table read only,modify DDL (create index/partition) and then make it read write : If you make table read only, insert, delete and update are disallowd otherwise ORA-12081.

alter table table_name read only;
alter table table_name read write;

6) Segment-less Objects: Segments are not created by default when table is created but rather when the 1st data is inserted. Exmple:
create table jtest (jcol number); Table created.
select bytes from user_segments where segment_name = 'jtest'; no row selected.
insert into jtest values (101);  1 row inserted.
select bytes from user_segments where segment_name ='jtest'; bytes : 65536
To create the segment when table is created, set parameter deffrred_segment_creation=false;

7) SQL * PLUS error logging:
sql> SET ERRORLOGGIN ON
sql> select * from from dual;
select * from from dual
              *
ERROR at line 1:
ORA-00903: invalid table name

sql> SHOW ERRORLOGGING
errorlogging is ON TABLE SYS.SPERRORLOG

sql> SELECT USERNAME, STATEMENT, MESSAGE FROM SPERRORLOG;
USERNAME    STATEMENT                MESSAGE
SYS         select * from from dual  ORA-00903: invalid table name

sql> SET ERRORLOGGIN OFF


Ref. Oracle Docs/Metalink