Oracle 11g has some pretty cool new features. I am covering some of the Read Only tables and Server result cache features in this post

Read only Tables

In 10g or prior oracle releases,  table can be made read only by granting the  SELECT object privilege to them. Oracle 11g allows tables to be marked as read-only by ALTER TABLE command.
Example:

Create table
CREATE TABLE orders (
id  NUMBER
);

Mark table as read-only
ALTER TABLE orders READ ONLY;

DML operations
INSERT INTO orders VALUES (2) ;
UPDATE orders SET id = 2;
DELETE FROM orders;
DDL Operations
ALTER TABLE orders ADD (description VARCHAR2(50));
TRUNCATE TABLE orders;

All the DML & DDL statements result in following error:
ORA-12081: update operation not allowed on table “SAMPLE”.“ORDERS”

Change the table status to read-write
ALTER TABLE orders READ WRITE;

Dictionary Views:

The read-only status of tables is displayed in the READ_ONLY column of the [DBA|ALL|USER]_TABLES views.

Server Result Cache

• Enable query result to be cached in memory which can be used during future execution of similar query by bypassing the regular processing and return the results faster.

• Decreases the wait time for both physical and logical IO by directly fetching the results from the memory.

• Cached result set is completely shareable between the sessions and various statements as long as they share common execution plan.

• Server result cache is the new component of SGA that caches results of queries and is managed by automatic memory management.

• New parameter RESULT_CACHE_MAX_SIZE is used to enable result cache by setting up the maximum size of the cache.

• A new optimizer hint allows use of result cache at the query level.

Query execution without result cache hint.

Query execution without result cache hint - Oracle 11g

Query execution with result cache hint.

Query execution with result cache hint - Oracle 11g

Parameters  related to Result Cache

RESULT_CACHE_MAX_RESULT  :  specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use
RESULT_CACHE_MAX_SIZE    :  specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache.
RESULT_CACHE_REMOTE_EXPIRATION   : specifies the number of minutes that a result using a remote object is allowed to remain valid
RESULT_CACHE_MODE    : specifies when a ResultCache operator is spliced into a query’s execution plan.

How to find result cache information:
V$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_DEPENDENCY

DBMS_RESULT_CACHE – PL/SQL API for result cache management:
Functions : Status – displays the current status of the result cache.

SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
—————————————————–
ENABLED

Flush: remove all objects from the result cache and release memory.

I will cover some more features in next post . Keep reading

And if you are looking for interview questions , see it right here Oracle11g Interview questions

If preparing for 1Z0- 050 certification , you can find OCP 11g certification practice test