Tuesday, June 25, 2013

Oracle Database 12c Release 1 (12.1) New Features

Oracle 12c Release 1 is now available.


Software can be downloaded here :- Oracle Database Software Download
Documentation is available here     :- 12c Release 1 (12.1)
New Features documentation can be found here :- Oracle® Database New Features Guide 12c Release 1 (12.1)  and Changes in This Release for Oracle Database Reference


Lots of changes and new features to play with :- 


  • CSSCAN and CSALTER replaced with DMU.
  • IDENTITY columns in tables.  Finally !  
CREATE TABLE t2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10));


  • VARCHAR2, NVARCHAR2, RAW size increased from 4,000 to 32,767 bytes.
  • DBMS_UTILITY.EXPAND_SQL_TEXT converts sql against views to underlying tables
  • ALTER TABLE ... MOVE PARTITION becomes non-blocking online DDL while DML operations continue to run uninterrupted on the partition that is being moved. Global indexes are maintained during the move partition, so a manual index rebuild is no longer required.
  • Local and global indexes can be created on a subset of the partitions of a table.  Potential to greatly speed up new index creation on large partitioned tables.
  • Adaptive Sql Plan Management ...
  • Concurrent Statistics Gathering ....  If you call the DBMS_STATS.GATHER_TABLE_STATS procedure on a partitioned table when CONCURRENT is set to true, then Oracle creates a separate statistics gathering job for each partition (or subpartition) in the table.
  • Dynamic Statistics are automatically used when the optimizer deems it necessary and the resulting statistics will be persistent in the statistics repository making them available to other queries.
  • Incremental statistics have been enhanced to support partition exchange loading. Data loaded into a non-partitioned table can be exchanged with a partition from the table and Oracle automatically and accurate computes the global statistics for the partition table using the statistics from the non-partitioned table and the existing partition-level statistics.
  • Online Statistics Gathering during bulk inserts.  Statistics are automatically created as part of a bulk load operation such as a CREATE TABLE AS SELECT operation or INSERT INTO ... SELECT operation on an empty table
  • Session Private Statistics for global temporary tables
  • Multiple indexes on the same set of columns, but not identical (e.g global and partitioned)
  • In-Database Archiving allows users and applications to set the archive state for individual rows. Rows that have been marked as archived will not be visible unless the session is enabled to see archived data.
  • Multi Tenant Architecture ...  Pluggable Database ...
  • Invisible columns !
CREATE TABLE mytable (a INT, b INT INVISIBLE, c INT);
  • Move a datafile ONLINE !
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' 
  TO '+dgroup_01/data/orcl/datafile/user1.dbf';
  • Dataguard Broker now supports cascaded standby
  • RMAN can "RECOVER TABLE" ... using an auxiliary database and datapump
  • New parameter PGA_AGGREGATE_LIMIT
  • PLSQL interface to inventory / patch level using DBMS_QOPATCH
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
  • Oracle Flex ASM.  ASM instance can now run on a separate server from DB instance
  • Oracle Flex Cluster.  Concept of Hub nodes and Leaf nodes in clusterware.
  • SQL*Net data compression.  New parameters SQLNET_COMPRESSION and SQLNET.COMPRESSION_SCHEME_LIST
  • Oracle Advanced Security now includes Oracle Data Redaction.  Realtime data masking for low privileged users with policies configured using DBMS_REDACT
BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'HR', 
   object_name            => 'EMPLOYEES', 
   column_name            => 'SALARY',
   column_description     => 'emp_sal_comm shows employee salary and commission',
   policy_name            => 'redact_emp_sal_comm', 
   policy_description     => 'Partially redacts the emp_sal_comm column',
   function_type          => DBMS_REDACT.PARTIAL,
   function_parameters    => '9,1,4',
   expression             => '1=1');
END;
/

  • Code Based Security.  Grant privileges to a PL/SQL package instead of a user.
  • Unified Auditing and AUDIT_ADMIN, AUDIT_VIEWER roles
  • SELECT ANY DICTIONARY no longer provides access to tables with password hashes (USER$ etc)
  • RESOURCE role does not include UNLIMITED TABLESPACE anymore
  • Materialized View out-of-place refresh.
DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);
  • Real Application Security ... lots to read up on here.

New Parameters in 12C :-

CONNECTION_BROKERS
DNFS_BATCH_SIZE
ENABLE_PLUGGABLE_DATABASE
HEAT_MAP
MAX_STRING_SIZE
OPTIMIZER_ADAPTIVE_FEATURES
OPTIMIZER_ADAPTIVE_REPORTING_ONLY
PDB_FILE_NAME_CONVERT
PGA_AGGREGATE_LIMIT
PROCESSOR_GROUP_NAME
NONCDB_COMPATIBLE
SPATIAL_VECTOR_ACCELERATION
TEMP_UNDO_ENABLED
THREADED_EXECUTION
UNIFIED_AUDIT_SGA_QUEUE_SIZE
USE_DEDICATED_BROKER