12c New features

1.1 Advanced Index Compression
Advanced Index Compression works well on all supported indexes, including those indexes that are not good candidates for the existing prefix compression feature; including indexes with no, or few, duplicate values in the leading columns of the index.

Advanced Index Compression improves the compression ratios significantly while still providing efficient access to the index.



2 Approximate Count Distinct
The new and optimized SQL function, APPROX_COUNT_DISTINCT(), provides approximate count distinct aggregation. Processing of large volumes of data is significantly faster than the exact aggregation, especially for data sets with a large number of distinct values, with negligible deviation from the exact result.

The need to count distinct values is a common operation in today's data analysis. Optimizing the processing time and resource consumption by orders of magnitude while providing almost exact results speeds up any existing processing and enables new levels of analytical insight.



3 Attribute Clustering
Attribute clustering is a table-level directive that clusters data in close physical proximity based on the content of certain columns. This directive applies to any kind of direct path operation, such as a bulk insert or a move operation.

Storing data that logically belongs together in close physical proximity can greatly reduce the amount of data to be processed and can lead to better compression ratios.



4 Automatic Big Table Caching
In previous releases, in-memory parallel query did not work well when multiple scans contended for cache memory. This feature implements a new cache called big table cache for table scan workload.

This big table cache provides significant performance improvements for full table scans on tables that do not fit entirely into the buffer cache.



5 FDA Support for CDBs
Flashback Data Archive (FDA) is supported for multitenant container databases (CDBs) in this release.

Customers can now use Flashback Data Archive in databases that they are consolidating using Oracle Multitenant, providing the benefits of easy history tracking to applications using pluggable databases (PDB) in a multitenant container database.



6 Full Database Caching
Full database caching can be used to cache the entire database in memory. It should be used when the buffer cache size of the database instance is greater than the whole database size. In Oracle RAC systems, for well-partitioned applications, this feature can be used when the combined buffer caches of all instances, with some extra space to handle duplicate cached blocks between instances, is greater than the database size.

Caching the entire database provides significant performance benefits, especially for workloads that were previously limited by I/O throughput or response time. More specifically, this feature improves the performance of full table scans by forcing all tables to be cached. This is a change from the default behavior in which larger tables are not kept in the buffer cache for full table scans.




7 In-Memory Aggregation
In-Memory Aggregation optimizes queries that join dimension tables to fact tables and aggregate data (for example, star queries) using CPU and memory efficient KEY VECTOR and VECTOR GROUP BY aggregation operations. These operations may be automatically chosen by the SQL optimizer based on cost estimates.

In-Memory Aggregation improves performance of star queries and reduces CPU usage, providing faster and more consistent query performance and supporting a larger number of concurrent users. As compared to alternative SQL execution plans, performance improvements are significant. Greater improvements are seen in queries that include more dimensions and aggregate more rows from the fact table. In-Memory Aggregation eliminates the need for summary tables in most cases, thus simplifying maintenance of the star schema and allowing access to real-time data.



8 In-Memory Column Store
In-Memory Column Store enables objects (tables or partitions) to be stored in memory in a columnar format. The columnar format enables scans, joins and aggregates to perform much faster than the traditional on-disk formats for analytical style queries. The in-memory columnar format does not replace the on-disk or buffer cache format, but is an additional, transaction-consistent copy of the object. Because the column store has been seamlessly integrated into the database, applications can use this feature transparently without any changes. A DBA simply has to allocate memory to In-Memory Column Store. The optimizer is aware of In-Memory Column Store, so whenever a query accesses objects that reside in the column store and would benefit from its columnar format, they are sent there directly. The improved performance also allows more ad-hoc analytic queries to be run directly on the real-time transaction data without impacting the existing workload.

The last few years have witnessed a surge in the concept of in-memory database objects to achieve improved query response times. In-Memory Column Store allows seamless integration of in-memory objects into an existing environment without having to change any application code. By allocating memory to In-Memory Column Store, you can instantly improve the performance of their existing analytic workload and enable interactive ad-hoc data extrapolation.



9 JSON Support
This feature adds support for storing, querying and indexing JavaScript Object Notation (JSON) data to Oracle Database and allows the database to enforce that JSON stored in the Oracle Database conforms to the JSON rules. This feature also allows JSON data to be queried using a PATH based notation and adds new operators that allow JSON PATH based queries to be integrated into SQL operations.

Companies are adopting JSON as a way of storing unstructured and semi-structured data. As the volume of JSON data increases, it becomes necessary to be able to store and query this data in a way that provides similar levels of security, reliability and availability as are provided for relational data. This feature allows information represented as JSON data to be treated inside the Oracle database.



10 New FIPS 140 Parameter for Encryption
The new database parameter, DBFIPS_140, provides the ability to turn on and off the Federal Information Processing Standards (FIPS) 140 cryptographic processing mode inside the Oracle database.

Use of FIPS 140 validated cryptographic modules are increasingly required by government agencies and other industries around the world. Customers who have FIPS 140 requirements can turn on the DBFIPS_140 parameter.



11 PDB CONTAINERS Clause
The CONTAINERS clause is a new way of looking at multitenant container databases (CDBs). With this clause, data can be aggregated from a single identical table or view across many pluggable databases (PDBs) from the root container. The CONTAINERS clause accepts a table or view name as an input parameter that is expected to exist in all PDBs in that container. Data from a single PDB or a set of PDBs can be included with the use of CON_ID in the WHERE clause. For example:

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);
This feature enables an innovative way to aggregate user-created data in a multitenant container database. Reports that require aggregation of data across many regions or other attributes can leverage the CONTAINERS clause and get data from one single place.



12 PDB File Placement in OMF
The new parameter, CREATE_FILE_DEST, allows administrators to set a default location for Oracle Managed Files (OMF) data files in the pluggable database (PDB). When not set, the PDB inherits the value from the root container.

If a file system directory is specified as the default location, then the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files. A file created in this manner is an Oracle-managed file.

The CREATE_FILE_DEST parameter allows administrators to structure the PDB files independently of the multitenant container database (CDB) file destination. This feature helps administrators to plug or to unplug databases from one container to another in a shared storage environment.



13 PDB Logging Clause
The PDB LOGGING or NOLOGGING clause can be specified in a CREATE or ALTER PLUGGABLE DATABASE statement to set or modify the logging attribute of the pluggable database (PDB). This attribute is used to establish the logging attribute of tablespaces created within the PDB if the LOGGING clause was not specified in the CREATE TABLESPACE statement.

If a PDB LOGGING clause is not specified in the CREATE PLUGGABLE DATABASE statement, the logging attribute of the PDB defaults to LOGGING.

This new clause improves the manageability of PDBs in a multitenant container database (CDB).



14 PDB Metadata Clone
An administrator can now create a clone of a pluggable database only with the data model definition. The dictionary data in the source is copied as is but all user-created table and index data from the source is discarded.

This feature enhances cloning functionality and facilitates rapid provisioning of development environments.



15 PDB Remote Clone
The new release of Oracle Multitenant fully supports remote full and snapshot clones over a database link. A non-multitenant container database (CDB) can be adopted as a pluggable database (PDB) simply by cloning it over a database link. Remote snapshot cloning is also supported across two CDBs sharing the same storage.

This feature further improves rapid provisioning of pluggable databases. Administrators can spend less time on provisioning and focus more on other innovative operations.



16 PDB Snapshot Cloning Additional Platform Support
With the initialization parameter CLONEDB set to true, snapshot clones of a pluggable database are supported on any local, Network File Storage (NFS) or clustered file systems with Oracle Direct NFS (dNFS) enabled. The source of the clone must remain read-only while the target needs to be on a file system that supports sparseness.

Snapshot cloning support is now extended to other third party vendor systems.

This feature eases the requirement of specific file systems for snapshot clones of pluggable databases. With file system agnostic snapshot clones, pluggable databases can be provisioned even faster than before.



17 PDB STANDBYS Clause
The STANDBYS clause allows a user to specify whether a pluggable database (PDB) needs to be a part of the existing standby databases. The STANDBYS clause takes two values: ALL and NONE. While ALL is the default value, when a PDB is created with STANDBYS=NONE, the PDB's data files are offlined and marked as UNNAMED on all of the standby databases. Any of the new standby databases instantiated after the PDB has been created needs to explicitly disable the PDB for recovery to exclude it from the standby database. However, if a PDB needs to be enabled on a standby database after it was excluded on that standby database, PDB data files need to be copied to the standby database from the primary database and the control file needs to be updated to reflect their paths after which a user needs to connect to the PDB on that standby database and issue ALTER PLUGGABLE DATABASE ENABLE RECOVERY which automatically onlines all of the data files belonging to the PDB.

This feature increases consolidation density by supporting different service-level agreements (SLAs) in the same multitenant container database (CDB).


18 PDB State Management Across CDB Restart
The SAVE STATE clause and DISCARD STATE clause are now available with the ALTER PLUGGABLE DATABASE SQL statement to preserve the open mode of a pluggable database (PDB) across multitenant container database (CDB) restarts.

If SAVE STATE is specified, open mode of specified PDB is preserved across CDB restart on instances specified in the INSTANCES clause. Similarly, with the DISCARD STATE clause, the open mode of specified PDB is no longer preserved.

These new SQL clauses provide the flexibility to choose the automatic startup of application PDBs when a CDB undergoes a restart. This feature enhances granular control and effectively reduces downtime of an application in planned or unplanned outages.


19 PDB Subset Cloning
The USER_TABLESPACES clause allows a user to specify which tablespaces need to be available in the new pluggable database (PDB). An example of the application of this clause is a case where a customer is migrating from a non-multitenant container database (CDB) where schema-based consolidation was used to separate data belonging to multiple tenants to a CDB where data belonging to each tenant is kept in a separate PDB. The USER_TABLESPACES clause helps to create one PDB for each schema in the non-CDB.

This powerful clause helps convert cumbersome schema-based consolidations to more agile and efficient pluggable databases.


20 Rapid Home Provisioning
Rapid Home Provisioning allows deploying of Oracle homes based on gold images stored in a catalog of pre-created homes.

Provisioning time for Oracle Database is significantly improved through centralized management while the updating of homes is simplified to linkage. Oracle snapshot technology is used internally to further improve the sharing of homes across clusters and to reduce storage space.



21 Zone Maps
For full table access, zone maps allow I/O pruning of data based on the physical location of the data on disk, acting like an anti-index.

Accessing only the relevant data optimizes the I/O necessary to satisfy a query, increasing the performance significantly and reducing the resource consumption.

11g New Features

i.                    Database Replay. -- captures database workload and replays. Packages: DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY.

ii.                  SQL Performance Analyzer. -- capture specific SQL statements and analyzes against various types of changes such as initialization parameter changes, optimizer statistics, refresh, and database upgrades, and then produces a comparison report., DBMS_SQLPA.

iii.                RAT (Real App testing).—both above tools are part of it.

iv.                DBMS_COMPARISON. --. Allows us to compare two tables - or two views - and inform us on the differences in data. Can summarize if there any changes between the data sets. Can detail about the specific rows that are different between the two. Can converge the data sets; get on-demand replication for ordinary tables, within schemas, across schemas or even across databases.

v.                  SQL Test Case Builder. -- gathers of as much information as possible related to a SQL issue and then packages it. The test case script contains all the commands that are needed to be able to re-create the objects, the user, the stats and the environment. dbms_sqldiag.export_sql_testcase.

vi.                OLTP Table Compression. -- The clause "compress for all operations" enables compression on all DML activities like INSERT, UPDATE, etc. The block is compressed, not the row. Compression takes place as a triggered event. Not when DMLs. So no perf impact. (Every block has a symbol table. Symbols replace data. Compression starts when block reaches a threshold.)

vii.              Data Pump. Compress with parameter COMPRESSION in the expdp command line. METDATA_ONLY|DATA_ONLY| ALL| NONE. Also enc and masking.

viii.            Extended Composite Partitioning.—any combinations of partitions.

ix.                Reference Partitioning. -- can be partitioned by a column which is available only in the primary table.

x.                  Interval Partitioning. -- create table sales6 ( sales_dt    date) partition by range (sales_dt) interval (numtoyminterval(1,'MONTH')) (    partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')) ); use “partition for” while selecting.

xi.                System Partitioning. -- partition by system (partition p1 tablespace users); table is physically divided into two segments. Developers decide where to insert.

xii.              Partitioning on Virtual Columns.-- …Cl generated  as (if ...) virtual       partition by list ( a )  ( partition p_low values ('LOW'),  partition p_medium values ('MEDIUM')..);

xiii.            Global Partitioned Indexes: index that is partitioned using a DIFFERENT partitioning-key or partitioning strategy than the table.

xiv.            Incrementally Updated Global Statistics. -- In partitioned tables, each partition can have optimizer statistics. In addition, there is a global statistic on the entire table that is not partition dependent. In 11g, we can collect the global stats incrementally from the changed partition alone, not by doing another full table scan like previous versions. dbms_stats.set_table_prefs.

xv.              Partition Advisor.—part of SQL Access Advisor.

xvi.            DDL Wait Option. -- alter session set ddl_lock_timeout = 10; retries for 10 secs.

xvii.          Adding Columns with a Default Value. -- Oracle ll not update the existing rows unlike earlier versions. New default value is stored in the data dict and queried while selecting.

xviii.        Virtual Columns.—create table a (a char generated always as (case …) virtual); indexes can be created. ( result is func based index)

xix.            Invisible Indexes. – Invisible only to the optimizer.Unlike "ALTER INDEX ... UNUSABLE" which makes DML's fail, with alter index in_res_guest invisible u can work with DMLs.

xx.              Read Only Tables.-- alter table TRANS read only;

xxi.            Fine Grained Dependency Tracking. -- Does not set the dependent objects invalid when an object changes unless it is absolutely necessary. Only changes that directly affect an object will cause invalidation.

xxii.          Oracle 11g uses Adaptive Cursor Sharing to solve bind var peeking by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement.

xxiii.        The IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.

xxiv.        Bind sensitive & aware. -- Once oracle notices that the plan has to be regenerated frequently, it marks Y to Bind-Sensitive. Once oracle changes the plan it marks Y to Bind-Aware in V$SQL. Then best cursor ll be used.

xxv.          Bind var peeking. --while no bind vars are used, oracle smartly choses weather to use the index or not based on the cardinality. Using bind variable freezes the plan of the sql at the first instance. While u query for a new value, the previous value in the bind varable peeks and plan for previous value applies here.

xxvi.        Adaptive Cursors.-- uses the correct plan based on the value of the bind variable, V$SQL_CS_SELECTIVITY

xxvii.      SQL Plan Baselining.—repeatable plans are stored in SQL Management Base (SMB), DBA_SQL_PLAN_BASELINES. DBMS_SPM.

xxviii.    SQL PLAN MANAGEMENT. -- Oracle calls this a "conservative plan selection strategy", as the optimizer preferentially uses a tried and tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

xxix.        Compound Triggers.—All f our different triggers defined as one. create trigger tr for update of col on bookings compound trigger a date before statement is begin ...end; ...after each row is ...

xxx.          Ordered Execution of Trigers. -- …before update on tab for each row follows <another trigger> begin…

xxxi.        Disabled Triggers. -- create or replace trigger tr after insert on t for each row disable begin…

xxxii.      Continue when. -- Instead of using an if and a continue, use continue when mod(ctr,10) != 0; also continue label when cond…

xxxiii.    Straight Sequences. -- trans_id:= myseq.nextval; u need to use a select stmt in prev releases.

xxxiv.    When OTHERS Then Do Something. – warns for when OTHERS then NULL;

xxxv.      Simple Integer. -- Values between negative 2,147,483,648 and 2,147,483,647. It does not allow nulls, but does allow overflow.

xxxvi.    Intra-unit Inlining. -- Intr-unit inlining involves replacing a call to a subroutine with copy of the code of that subroutine. Recompile with the setting alter session set plsql_optimize_level = 3; or PRAGMA INLINE (calc_int, 'YES').

xxxvii.  Name function parameters in sql.-- select myfunc (p_param1=>1,p_param2=>1) from dual;

xxxviii.Dynamic Cursor and REF CURSOR Interchangeability. -- DBMS_SQL.TO_REFCURSOR - Switch from DBMS_SQL to native dynamic. SQL; DBMS_SQL.TO_CURSOR_NUMBER - Switch from native dynamic SQL to DBMS_SQL.

xxxix.    Regexp. -- REGEXP_COUNT to get the no. of matches, SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern. Syntax:  regexp_substr(source, pattern, position, occurrence, options, subexp).

xl.                CLOB. -- EXECUTE IMMEDIATE statement; OPEN-FOR statement and DBMS_SQL.PARSE procedure all accept SQL statements in the form of CLOBs.

xli.              PL/Scope. -- a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of "IDENTIFIERS: NONE". Switch this value to "IDENTIFIERS: ALL" to enable collection.

xlii.            Restriction in FORALL Statements Removed. -- You can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct.

xliii.          Memory Management. --Instead of setting SGA_TARGET and PGA_AGGREGATE_TARGET in 10g, you specify MEMORY_TARGET and MEMORY_MAX_TARGET. alter system set memory_max_target|memory_targe= 1G scope=spfile; V$MEMORY_DYNAMIC_COMPONENTS, V$MEMORY_TARGET_ADVICE.

xliv.          Adaptive Thresholds. -- The thresholds are adaptable to a specific time based on the past behavior and are based on the AWR snapshots calculated earlier.

xlv.            Pending Statistics: Gather but unpublish statistics so that u can test and then publish…dbms_stats.set_table_prefs, dbms_stats.publish_pending_stats('ARUP', 'SALGRADE'); dbms_stats.delete_pending_stats('ARUP','RES'); DBA_TAB_STATS_HISTORY.

xlvi.          Extended Statistics. -- Function-Based Statistics (apply a fn while creating statistics- …method_opt => 'for all columns size skewonly for columns (upper (cust_name))’); Multi-Column Statistics (create associations between different columns (a column group) to help the optimizer make better decisions.) (dbms_stats.gather_table_stats, dbms_stats.create_extended_stats). DBA_STAT_EXTENSIONS.

xlvii.        SQL Result Cache. -- When you execute a query with the HINT RESULT_CACHE, Oracle performs the operation just like any other operation but the results are stored in the SQL Result Cache. Subsequent invocations of the same query do not actually go to the table(s) but get the results from the cache. Change in the underlying table invalidates the cache, and it’s refreshed dynamically.   dbms_result_cache.flush; dbms_result_cache.invalidate; dbms_result_cache.memory_report.

xlviii.      PL/SQL Function Result Cache. -- create or replace function a (…) return a  number RESULT_CACHE relies_on (sales_tax_rate, customers) is … exec dbms_result_cache.bypass(true); DBMS_RESULT_CACHE.

xlix.          Client Query Result Cache. – param CLIENT_RESULT_CACHE_SIZE = 1G. Database client stacks that use the OCI8 drivers can use this cache.

l.                    PIVOT.-- select * from (

    (

        select 'a' value  from dual union all

        select 'e' value  from dual union all

        select 'i'  value  from dual union all

        select 'o' value  from dual union all

        select 'u' value  from dual

    )

   pivot  -- use “pivot xml” to get the o/p in xml

    (

       count( value)

        for value in   ('a','e','i','o','u')

    )

)

li.                  UNPIVOT. -- select value from

(

    (

        select

            'a' v1,

            'e' v2,

            'i' v3,

            'o' v4,

            'u' v5

        from dual

    )

    unpivot [INCLUDE NULLS]

    (

        value

        for value_type in

            (v1, v2,v3,v4,v5) – Also can give ANY here.

    )

)

lii.                SecureFiles: The New LOBs. -- LOBs can now be stored as securefiles or basicfiles. Secure File Features - alter table contracts_sec modify lob (orig_file) (deduplicate| keep_duplicates| compress high| encrypt using 'AES128’| cache), DBA_LOBS,  TBS should be (ASSM) enabled., Parameter db_securefile, …lob (orig_file) store as securefile… in create table stmt.  DBA_LOBS, DBA_LOB_PARTITIONS.

liii.              Migration of LOBs to SecureFiles. -- create a new table, load data from the old one, and rename it. Or use dbms_redefinition.

liv.              Colored SQL. -- The procedure add_colored_sql() in the package dbms_workload_repository marks the SQL as "colored", or important enough to be captured in every AWR snapshot regardless of whether the SQL is in top SQLs. WRM$_COLORED_SQL

lv.                Parameter Files from Memory. -- create spfile|pfile. from memory;

lvi.              Real-time SQL Monitoring. – can be seen in V$SQL_MONITOR, V$SQL_PLAN_MONITOR.

lvii.            SQL Access Advisor. -- includes advice on partitioning schemes that may improve performance. The original workload manipulation has been deprecated and replaced by SQL tuning sets (Inputs for sql access advisor.).

lviii.          SQL Tuning Advisor automatcally find top sqls from AWR, tests Performance, and the profiles are automatically accepted if the param ACCEPT_SQL_PROFILES is set true. DBA_SQL_PROFILES.

lix.              SQL Repair Advisor. -- SRA is run after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.

lx.                AWR baseline. -- contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. Never purged. In 11g Moving Window Baselines, Baseline Templates, and Baseline Metric Thresholds.

lxi.              Adaptive Metric Baselines. -- In 11g, notification thresholds can be associated with a baseline, so the notification thresholds vary throughout the day in line with the baseline.

lxii.            Automatic Diagnostic Repository. -- When a critical error occurs, it is assigned an incident number, and diagnostic data for the error (such as trace files) are immediately captured and tagged with this number in ADR.

lxiii.          Flashback transaction. -- using DBMS_FLASHBACK.TRANSACTION_BACKOUT. View flashback_transaction_query.

lxiv.          Tablespace Transport for a Single Partition thru datapump. It creates a new table with the specified partitions.

lxv.            Data Masking. -- Data Pump new parameter, remap_data, for masking the actual data while exporting for QA, Test servers). Option remap_data=accounts.acc_ssn:pkg_mask.fn_mask_ssn… fn contains logic for masking.

lxvi.          RMAN.-- Data Recovery Advisor - RMAN> list failure; RMAN> advise failure; RMAN> repair failure preview; RMAN> repair failure;

lxvii.        RMAN Proactive Health Checks.-- RMAN> validate database;RMAN> validate tablespace users;RMAN> validate datafile 1;RMAN> validate datafile 4 block 56;

lxviii.      RMAN Flashback Logs to Rescue. -- Oracle looks in the Flashback logs (instead of datafiles) to find a good copy of the past image.

lxix.          RMAN New compression algorithm. -- configure compression algorithm ‘ZLIB’; or BZIP2.

lxx.            RMAN Parallel Backup of the Same Datafile. --RMAN> run { allocate channel c1 type disk format '/backup1/%U';  allocate channel c2 type disk format '/backup2/%U';  backup  section size 500m   datafile 6; } breaks the file into chunks.

lxxi.          RMAN backs up only uncommitted undo….ignores commited undo unlike prev releases…

lxxii.        RMAN Virtual Private Catalog. --  RMAN> create catalog; RMAN> register database; RMAN> create virtual catalog; RMAN> import catalog rman/rman@catdb1;

lxxiii.      Default Passwords. -- select * from dba_users_with_defpwd   - read again.

lxxiv.      Case-Sensitive Passwords. – enables on setting param SEC_CASE_SENSITIVE_LOGON

lxxv.        Password Verify Function. -- improved verification logic with the new function “verify_function_11G” in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

lxxvi.      Auditing. -- Parameter audit_trail is now set to DB by default, not NONE. More statements have been placed under audit by default. Oracle Security Manager Tool in prev versions now merged with EM.

lxxvii.    Transparent Tablespace Encryption. – Everything (including transportable tablespaces, backups, and so on) in the tablespace is encrypted. Encryption of Data Pump Dumpfiles. ENCRYPTION caluse in tablespace creation command.

lxxviii.  Access Control Lists for UTL_TCP/HTTP/SMTP. – control access to particular procedures in a package. dbms_network_acl_admin.create_acl, dbms_network_acl_admin.add_privilege, dbms_network_acl_admin.assign_acl.

lxxix.      ASM. – New role SYSASM, Variable Extent Sizes, select name, allocation_unit_size from v$asm_diskgroup.

lxxx.        ASM does not store data; the database does. The ASM instance, however, maintains metadata such as the diskgroup names, the disks in them, the directories, and so on.

lxxxi.      Preferred Mirror Read. -- In an Oracle RAC database there are multiple nodes all going to the same ASM instance, now you can configure a node to read from a specific failgroup. In prev versions it reads only from primary group.

lxxxii.    ASM New options. -- drop diskgroup dg7 force including contents; alter diskgroup dg7 mount restricted; alter diskgroup dg2 set attribute 'disk_repair_time'='2H';( how long the ASM instance should tolerate a disk with errors before dropping it from the diskgroup.).

lxxxiii.  ASM Metadata Backup and Restore. -- $ asmcmd –p ASMCMD [+] > md_backup.

lxxxiv.  Restricted Mount. -- alter diskgroup dg7 mount restricted; ASM instance is aware of the exclusivity of the operations on the underlying disks and minimizes the locking mechanism.

lxxxv.    Patching. -- Online Patching- without down time, Features-Based Patching.

lxxxvi.  Database Resident Connection Pooling. -- Server-side pool called Database Resident Connection Pool (DRCP). DRCP is available to all database clients that use the OCI driver including C, C++, and PHP. Execute dbms_connection_pool.start_pool; adv- one pool can serve 1000 connections.

lxxxvii.Automatic Health Monitor. –Checks for following integrities, records into Automatic Diagnostic Repository, param diagnostic_dest and view V$DIAG_INFO. AHM calls recovery advisors. If recovery fails, logs in to V$CORRUPT_XID_LIST. DB Structure, Data Block, Redo, Undo Segment, Transaction and Dictionary Integrity Check. DBMS_HM.

lxxxviii.          Active Data Guard. – The Data Guard environment works by sending the redo data to the standby server by connecting to the database instance there. In 11g, you can open the physical standby database in read-only mode and restart the recovery process. This means you can continue to be in sync with primary but can use the standby for reporting. dba_logstdby_parameters, LOGSTDBY$EVENTS.

lxxxix.  Standby DB types. -- A logical standby (physical struct does not match) turns redo into inserts, updates and deletes (SQLs) and applies to SB db, while a physical standby (physical struct match) directly applies redo to its datafiles.

xc.               Snapshot Standby. -- To test apps in prod with Database Replay, the physical standby database can be temporarily converted into an up datatable one called Snapshot Standby Database. alter database convert to snapshot|physical standby.

xci.             Easier Creation of Standby Database. -- One RMAN command that does it all on setting a few params.

xcii.           Real Native Compilation. -- Native-compile without a C compiler in the server or setting the parameter. All you have to do is set a session parameter before creating or recompiling stored code: alter session set plsql_code_type = native; USER_PLSQL_OBJECT_SETTINGS.

xciii.         Dimensions. -- They reference existing tables, and do not contain any data themselves - they merely add additional metadata to existing database objects. To create a product dimension, you'd first create the table that contains the data, and then create the dimension afterwards. On a product table, CREATE DIMENSION products LEVEL category IS (products.prod_category, products.prod_cat_desc)  HIERARCHY prod_hier (product CHILD OF subcategory CHILD OF category) ATTRIBUTE category DETERMINES products.prod_category;

xciv.         OLAP Cubes. -- Whereas MVs store pre-computed results to avoid joins and aggregations, cubes store raw data and compute most summaries on the fly. Stored in special area called an Analytic Workspace (AW) as BLOBs on tables specially named with an AW$ prefix.

xcv.           Analytic Workspace Manager (AWM). – Tool to manage AW OLAP Objects.

xcvi.         Cube Organized Materialized Views. -- 11g Combines the rewrite feature of MVs  with the performance benefits of OLAP cubes via a new feature called Cube Organized Materialized Views.(OLAP cubes are represented as MVs).Prefixed with CB$. Select * from table(cube_table('GLOBAL.PRICE_CUBE')).

xcvii.       Query Rewrite rules are more relaxed. Even if the MV and the query used inline views (or subqueries) or remote tables.

xcviii.     MVs. - Refresh statement combinations (merge and delete).Removal of unnecessary refresh hint. Index creation for UNION ALL MV.

xcix.         DBA_MVIEW_DETAIL_PARTITION. This view shows the partitions that have been updated and will be used in PCT (Partition change tracking) refresh later.

c.                   Truly Online Index Rebuild. -- The online rebuild is truly online: it does not hold an exclusive lock. The DMLs are not affected.

ci.                 GTT can be created with a new tablespace clause.

cii.               SQL*Plus.--  Error Logging- set errorlogging on table sh.my_sperror_log;

ciii.             Shrinking the Temporary Tablespace. -- alter tablespace temp1 shrink space.

civ.             SQL*Plus Shows BFILE path.

cv.               DBA_TEMP_FREE_SPACE. -- displays information about temporary tablespace usage. ALTER TABLESPACE temp SHRINK SPACE|TEMPFILE '..'.

cvi.             ALTER SYSTEM RESET <params> - now sid clause is not needed.

cvii.           DBMS_RESOURCE_MANAGER. -- Now includes a CALIBRATE_IO procedure to assess the I/O performance of the database server’s storage system by performing an I/O intensive read-only workload. Statistics in AWR.

cviii.         Remote External Jobs- allows the database to schedule external jobs which run on a remote server. The remote server doesn't have to have an Oracle client or database installation, but it must have an Oracle Scheduler Agent installated. Detached Jobs, Lightweight Jobs, Support for Data Guard.

cix.             Direct NFS Client. --$ORACLE_HOME/dbs/oranfstab, /etc/oranfstab, /etc/mtab

cx.               Log Miner in EM.

cxi.             Flashback Data Archive.—uses Flashback Recovery Area, unlike undo in prev versions. Create flashback archive near_term  tablespace far_near_term retention 1 month.

cxii.           The Flash Recovery Area is a specific area of disk storage that is set aside exclusively for retention of backup components such as datafile image copies, archived redo logs, and control file autobackup copies. Should be set before any Flash Backup and Recovery activity can take place.

cxiii.         Online table redefinitions.—by default no longer invalidate dependent objects (PL/SQL, views, synonyms etc.), provided the redefinition does not logically affect them. An exception to this behavior is triggers, which are associated directly with a table.

cxiv.         Table Compression Enhancements. --NOCOMPRESS, COMPRESS, COMPRESS FOR DIRECT_LOAD OPERATIONS, COMPRESS FOR ALL OPERATIONS.

cxv.           PL/SQL Hierarchical Profiler. -- gather and analyze hierarchical profiler data for PL/SQL programs. Consists of the DBMS_HPROF package, which feels similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command line utility to converts the profile information into HTML format.

cxvi.         Hangman Utility – The Hang Manager (hangman) utility is to detect database bottlenecks.  An extension of the dba_waiters and dba_blockers views, the hangman tables have a “hang chain” that allow the DBA to find the source of “hangs”, such as the “deadly embrace” where mutually blocking locks or latches hang a process.   In 11g, the hangman utility is installed on all RAC nodes by default, allowing for easier inter-node hang diagnostics.

cxvii.       PLSTIMER. -- Identifies hotspots and performance tuning opportunities in PL/SQL.

cxviii.     Automatic Reporting of Corrupt Blocks.

cxix.         End-to-End Redo Validation.

cxx.           Oracle Audit Vault. -- is a powerful enterprise wide audit solution that efficiently consolidates, detects, monitors, alerts, and reports on audit data for security auditing and compliance.

cxxi.         Oracle Configuration Manager. – creates tar to be sent to Oracle Support.

cxxii.       Oracle text. -- SDATA Sections - Unlike MDATA, they can be used for range searching on numeric or date fields, as well as equality searching.

cxxiii.     Oracle text. --Composite Domain Indexes, Increased Number of Partitions, New parallel Optimize Index “rebuild” mode, Online Index Recreation - CTX_DDL.RECREATE_INDEX_ONLINE, Incremental Indexing -CTX_DDL.POPULATE_PENDING.

cxxiv.     Oracle text. --AUTO_LEXER can perform automatic identification of different languages, INDEX_STEMS feature now available for many more languages.

cxxv.       Native Oracle XML DB Web Services. -- Web services are becoming the preferred way to expose data to client applications. Oracle 11g Database makes the conversion of existing PL/SQL code into web services easier than ever by providing Native XML DB web services. With some simple configuration, this functionality exposes PL/SQL code code as web services.

Release 2:

cxxvi.     Listagg. -- select deptno, listagg( ename, '; ' ) within group (order by ename)  from emp group by deptno order by deptno, for every dept no, gives the name as a ';' seperated list.

cxxvii.   NTH_VALUE (a generalization of existing FIRST_VALUE and LAST_VALUE functions) gives users the functionality of retrieving an arbitrary (or nth) record in a window.

cxxviii. Recursive subquery_factoring_clause in WITH - 2 sqls should be in SFC, 1st (anchor member) shouldn't reference query name. 2nd (recursive member) should reference query name, should be combined with 1st using union all. Eg: with a (x , y) as (select 1,1 from dual union all select 2*x , y+1 from a where y<5) select x from a. o/p will be 1 2 4 8 16 5 rows.

cxxix.     Edition-based Redefinition. --default edition ora$base, CREATE EDITION e2; ALTER SESSION SET EDITION = e2; Editioning views expose a different projection of each changed table into each edition to allow each to see just its own columns. Crossedition triggers propagate data changes made by the old edition into the columns of the new edition.

cxxx.       Automatic Block Repair. --If a corrupt data block is discovered on a physical standby database, the server attempts to automatically repair the corruption by obtaining a copy of the block from the primary. database.Params LOG_ARCHIVE_CONFIG,  LOG_ARCHIVE_DEST_n

cxxxi.     Real-time query. -- A physical standby database can be open for read-only access while redo apply is active only if the Oracle Active Data Guard option is enabled. Queries can be offloaded from the primary database to a physical standby database. Param STANDBY_MAX_DATA_DELAY

cxxxii.   DBMS_PARALLEL_EXECUTE. -- provides subprograms to allow a specified INSERT, UPDATE, DELETE, MERGE, or anonymous block statement to be applied in parallel chunks. Procedures CREATE_TASK, CREATE_CHUNKS_BY_ROWID, RUN_TASK, TASK_STATUS, RESUME_TASK, DROP_TASK

cxxxiii. Parallelism Enhancements. -- In-Memory Parallel Execution, Auto Degree of Parallelism, parallel_degree_policy -- decides whether or not to use direct path depending on the size of the table and the buffer cache. parallel_min_time_threshold -- only when the num_rows suggests that it will take more than nn seconds to scan the table. parallel_degree_limit -- sets a limit on the maximum degree of parallelism. parallel_force_local --prohibits “parallel parallelism”, a case where parallel queries on a RAC node are limited only to the local instance node.

cxxxiv. Deferred Segment Creation.-- Param DEFERRED_SEGMENT_CREATION, clauses SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE in CREATE TABLE..., col SEGMENT_CREATED in create table.

cxxxv.   Data guard.—The redo data being transmitted across the network can be compressed.

cxxxvi. Instance Caging allows the DBA to limit the CPU usage of an Oracle instance by setting the CPU_COUNT initialization parameter and enabling CPU resource management.

cxxxvii.           Standby databases that a primary database can support is increased from 9 to 30.

cxxxviii.         DBMS_SCHEDULER.CREATE_FILE_WATCHER. – enables jobs to be triggered when a file arrives on a given machine.

cxxxix. Oracle Scheduler.--  e-mail notifications on any job activity.

cxl.             Oracle Scheduler.-- Specify multiple destinations for a job.

cxli.           EXECUTE privilege for DIRECTORY objects.

cxlii.         Flashback Data Archive Support for DDLs.

cxliii.       Enhance CREATE or REPLACE TYPE to Allow FORCE. - CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (c varchar(20)).

cxliv.       Materialized view logs on the base table contain a WITH COMMIT SCN clause.

cxlv.         Column level compression -- create table tab compress for archive level=3  as select .... archive level specifies the amount of compression. Available only in Exadata storage devices.

cxlvi.       Fine-Grained Dependencies for Triggers.

cxlvii.     IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement.

cxlviii.   Compressed Table Support in Logical Standby Databases and Oracle LogMiner.

cxlix.       LogMiner.--  Enables the analysis of the contents of archived redo logs. It can be used to provide a historical view of the database without the need for point-in-time recovery. It can also be used to undo operations, allowing repair of logical corruption. DBMS_LOGMNR

cl.                 V$LOGMNR_CONTENTS.-- LogMiner populates a row in this view with each logical operation performed on the database, thus offering familiar, relational-based access for ad-hoc querying or custom application consumption. Each row contains a SQL UNDO statement, which can be used to rollback the change, and SQL REDO statement, which details the original operation.

cli.               Stored Outlines Migration to SQL Plan Management.

clii.             Zero-Size Unusable Indexes and Index Partitions.

cliii.           DB Replay. -- A Replay Compare Period Report performs a high-level comparison of workload replay to its capture or to another replay of the same capture.

cliv.           Compare SQL Tuning Sets.

clv.             Flash Cache--Flash_cache is used for tertiary storage on solid-state disk (SSD) while KEEP pool uses volatile RAM disk. alter|create table|index  objectname  storage  ( buffer_pool { keep | recycle | default }  flash_cache { keep | none | default } );

clvi.           Keep pool --The keep pool's purpose is to take small objects that should always be cached, for example Look Up Tables.

clvii.         Recycle pool --The recycle pool is for larger objects.

clviii.       Default pool -- The default pool is for everything else.

clix.           Oracle Clusterware. -- Create Quorum Disk Groups and Quorum Disk FailGroups which cannot hold any other data than OCR or Voting Disk data.

clx.             Oracle Clusterware.--  A portable cluster software that allows clustering of single servers so that they cooperate as a single system. Oracle

clx. Clusterware also provides the required infrastructure for Oracle Real Application Clusters (RAC).

clxi.           The Oracle Clusterware Compenents.-- is comprised primarily of two components: the voting disk and the OCR (Oracle Cluster Registry). The voting disk is nothing but a file that contains and manages information of all the node memberships. The OCR is a file that holds cluster and database configuration information for RAC and Cluster Ready Services (CRS) such as the cluster node list, and cluster database instance to node mapping and CRS application resource profiles.

clxii.         ASM Cluster File System Snapshots. -- Create up to 63 image copies of file systems as a point in time capture. These snapshots a read only and can be used to view a file system as of the point in time in the past when they were created.

clxiii.       ASM Dynamic Volume Manager. -- Device driver which is integrated into the Oracle Kernel called ASM Dynamic Volume Manager (ADVM) is loaded at ASM startup and functions as a standard I/O interface for normal file systems to utilize ASM functionalities. This driver communicates with the ASM instance about ASM extent maps, rebalancing operations and I/O failure issues.

clxiv.       ASM Optimal Disk Placement. -- Specify on which disk regions the ASM Disks will be placed. This enables us to make use of faster disk zones on the outer regions of disks for better I/O performance. Possible options are hot/mirrorhot and cold/mirrorcold.

clxv.         Renaming Disk Groups – renamedg.

clxvi.       Preprocessing Data for ORACLE_LOADER Access Driver in External Tables.

clxvii.     DBFS. -- The Oracle Database File System (DBFS) is a feature that creates a standard file system interface on top of files and directories that are stored in database tables. DBFS is similar to NFS in that it provides a shared network file system that looks like a local file system. Like NFS, there is a server component and a client component.

clxviii.   Partition pruning. – jus the elimination of partitioning while searching.