OCP Oracle Database 11g : New Features for Administrators Exam Guide

Topics related to above exam follows.....

For now... here are some of the cool new features of 11g

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.


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.

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