Oracle Database 12c New Features for Developers

Oracle Database 12c New Features for Developers

Session level sequences
A new SESSION level database sequence can be created now in 12c to support the session level sequence values. These types of sequences are most useful and suitable on global temporary tables that have session level existence.

Session level sequences produce a unique range of values that are limited within the session, not across the sessions. Once the session ends, the state of the session sequences also goes away. The following example explains creating a session level sequence:
Examples:
SQL> CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION;

SQL> ALTER SEQUENCE my_seq GLOBAL|SESSION;

While Creating Table we can use Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.

Example:
create sequence product_seqno start with 1 increment by 1 nocycle;

create table product_dim
(
   product_id number default product_seqno.nextval,
  product_name     varchar2(30),
  qty                      number,
  status                  varchar2(1),
  cre_date             date,
);


Table Level Invisible column:
Oracle Database 12c provides you the Invisible column feature. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column need to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the database into invisible columns.

Example:
SQL> create table emp
   (
   emp id number,
    ename varchar2(100),
    email varchar2(100),
    sal number,    
    comm number INVISIBLE,
    deptno number
    );
  
SQL> ALTER TABLE my_table MODIFY (password visible);  


Multiple indexes on the same column
Before Oracle Database 12c, we could not have multiple indexes on a single column. In Oracle Database 12c a column may have multiple indexes but all should be of different types. Like a column may have B-Tree and BitMap Index both. But, only one index will be used at a given time.


VARCHAR2 length up to 32767  (Not Verified in 12.1.0 version)
Form Oracle Database 12c, a varchar2 column can be sized upto 32767, which was earlier 4000. The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes.


Analytical  Functions same as MYSQL  Top-N feature
A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set
Example:
SQL> SELECT sal
    FROM   emp
    ORDER BY sal DESC
    FETCH FIRST 10 ROWS ONLY;


Sequence Values generating using  IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is a American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).
Example:
SQL> create table EMP
    (
    empid number generated as identity,
    ename varchar2(100),
    email varchar2(100),
    sal  number,
    comm number,
   deptno number  
    );


PLSQL Function with  WITH Clause improvement
In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement and use it as an ordinary function. Using this construct results in better performance as compared with schema-level functions
Example:
SQL> WITH
     FUNCTION emp_f(n IN NUMBER) RETURN NUMBER IS
     BEGIN
        RETURN n+1;
      END;
    SELECT  EMP_F(1)
    FROM   dual ;


Cascade for TRUNCATE and EXCHANGE partition.

With Oracle Database 12c, The TRUNCATE can be executed with CASCADE option which will also delete the child records.
Example:
SQL>  TRUNCATE TABLE EMP  CASCADE;

Move table partition to different Tablespace online

From Oracle 12c, it become very easy to move Table Partition to different tablespace and does not require complex steps
Example:
  SQL> ALTER TABLE MY_LARGE_TABLE MOVE PARTITION MY_LARGE_TABLE_PART1 TO TABLESPACE USERS_NEW;



Temporary Undo
Before Oracle Database 12c, undo records of temporary tables used to be stored in undo tablespace. With the temporary undo feature in Oracle Database 12c, the undo records of temporary tables can now be stored in a temporary table instead of stored in undo tablespace. The main benefits of temporary undo are 1) Low undo tablespace usages 2) less redo data generation. For using this feature Compatibility parameter must be set to 12.0.0 or higher and TEMP_UNDO_ENABLED initialization parameter must be Enabled.


DDL logging
By using the ENABLE_DDL_LOGGING initiation parameter in Oracle Database 12c, we can now log the DDL action into xml and log files to capture when the drop or create command was executed and by whom under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. The parameter can be set at the database or session levels.
Example:
  SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;