Some of the Oracle Database 11g New Features

Oracle Database 11g Release 1 (11.1) New Features
=================================================

Case Sensitive Passwords

Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.

=======================================
Readonly Tables

To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY

TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.

The following example places the EMP table in read-only mode:

ALTER TABLE EMP READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE EMP READ WRITE;

=========================================

Invisible Indexes

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Altering Indexes

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:

CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX ind_emp_dept VISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX ind_emp_dept INVISIBLE;

To check whether an index is visible or invisible, query the dictionary views

USER[ALL][DBA]_INDEXES

For example, to determine if above index is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';

INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE

===========================================

Automatic Stats Collection on Tables

As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary.

This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these
views to identify tables with stale statistics.

=================================================

Virtual columns

Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.

==============================================

Simplified and improved automatic memory management

You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.

===============================================

Database resident connection pooling

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of
database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.

==================================================

Tablespace-level encryption

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted.

All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.

==========================================================

Result cache in the system global area

Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The result cache occupies memory in the shared pool.

=============================================================

Enhanced online index creation and rebuild

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.

===============================================================

Ability to online redefine tables that have materialized view logs

Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

=====================================================================

Optimized ALTER TABLE...ADD COLUMN

For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:

Add a NOT NULL column with a default value

Add a nullable column without a default value

Add a virtual column
==========================================================