Oracle Database 12c New Features for Developers

Oracle Database 12c introduces a new multitenant architecture that makes it easy to deploy and manage database clouds. Oracle 12c is a pluggable database environment, where we can plug multiple databases into single database container. All these databases then share same background processes and memory. This helps in reducing the overhead of managing multiple databases.

I have tried to compile some of the important new features of Oracle Database 12c. Below are the top 15 new features of Oracle Database 12c for Oracle Developer & professional.

1. 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 test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);


2. 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 my_table
  2  (
  3  id number,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );
  
SQL> ALTER TABLE my_table MODIFY (password visible);  


3. 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.


4. VARCHAR2 length up to 32767
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. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). To use extended data types functionality we need to set MAX_STRING_SIZE initialization parameter to EXTENDED. The default value of MAX_STRING_SIZE is STANDARD, which restricts the maximum sizes to the traditional lengths.


5. 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 value
  2  FROM   mytable
  3  ORDER BY value DESC
  4  FETCH FIRST 10 ROWS ONLY;


6. 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 my_table
  2  (
  3  id number generated as identity,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );


7. 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
  2    FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
  3    BEGIN
  4      RETURN n+1;
  5    END;
  6  SELECT f_test(1)
  7  FROM   dual
  8  ;


8. 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.


9. Online RENAME/MOVE of Datafiles
Oracle Database 12c has provided a simple way to online renamed or moved data files by simply "ALTER DATABASE MOVE DATAFILE" command. Data files can also be migrated online from ASM to NON-ASM and NON-ASM to ASM easily now.

Examples:
Rename datafile:  
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/indx_01.dbf';
Move Datafile:    
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/orcl/indx.dbf';
NON-ASM to ASM:   
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '+DISKGROUP_DATA01';


10. 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;



11. 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.


12. 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;



13. PGA_AGGREGATE_LIMIT parameter
Oracle Database 12c has provided us a way to limit PGA by PGA_AGGREGATE_LIMIT parameter. Before Oracle Database 12c there was no option to limit and control the PGA size. Oracle will automatically abort the session that holds the most untenable PGA memory when PGA limits exceeds the defined value.


14. SQL statement in RMAN
From Oracle Database 12c, we can execute any SQL and PL/SQL commands in RMAN without SQL prefix
Example:
  RMAN> SELECT username,machine FROM v$session;


15. Turning off redo for Data Pump the import
The new TRANSFORM option, DISABLE_ARCHIVE_LOGGING, to the impdp command line causes Oracle Data Pump to disable redo logging when loading data into tables and when creating indexes. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
Example:
  impdp directory=mydir dumpfile=mydmp.dmp logfile=mydmp.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

I hope you have enjoyed learning the new features of Oracle 12c. Your feedback are well appreciated.

Related Post
- Top 15 new features of Oracle Database 12.2 for developers
- JSON in Oracle Database with Example
- Generate XML data using SQL in Oracle Database
- New Features for Developers in Oracle 11g
- SQL Interview Question Answers
- Auto Increment Column Performance Enhancement with each Oracle Version
- Complex View Merging Transformation and ORA-00979 in Oracle 12c

31 comments:

  1. very usefull stuff

    thanks a lot

    ReplyDelete
  2. Can you plz explain me about the difference between below 3 .

    1. Index by table
    2. Varray
    3. Nested Table
    4. Objects (Which Is in PL\SQL)


    Thanks,

    ReplyDelete
    Replies
    1. Off course there is "Index by Table" also known as associative array.Its a Oracle PL/SQL collection which have sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.Generally Binary_Integer,Pls_Integer is used as the key, Also can be indexed by Varchar2.

      Delete
  3. can u plz explain me abt Data Blocks, Extents, and Segments

    ReplyDelete
    Replies
    1. http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm

      Delete
  4. excellent features...

    ReplyDelete
  5. Hi,

    1. What is the disadvantages of using BULK Collect in oracle..?

    2. Can a out parameter return Multiple values using a procedure..?


    Thx

    ReplyDelete
    Replies
    1. You can return an object or even ref-cursor from procedure to return multiple values. or simply you can have multiple out parameters
      http://nimishgarg.blogspot.in/2012/11/view-results-of-refcursor-out-parameter.html

      Delete
    2. Exploit Google to gain further knowledge

      Delete
  6. Hi,

    I have a requiremnt in my project..Below is the requiremnt..

    we have a existing table which is having Huge million set of records and more than 200 columns.Also the performance of the table is high.
    We need to add a new column in the existing table.But DBA is saying that if we add one more column ..The performance of the table will be very slow..

    Please help me how can go for it..

    Thank,

    ReplyDelete
    Replies
    1. I think it is better to export the entire table and perform the import i.e entire data is stored in a single block and now the table performance will increase...later u can add another column easily...

      Delete
  7. Hi,

    If you want to add new column better create a new table with linking with the existing table.(PK ,FK).

    ReplyDelete
    Replies
    1. Easily to Understand...
      Nice & Thanks boss....

      Delete
  8. Excellent Stuff...

    ReplyDelete
  9. Excellent Documents..it's really helpful.

    ReplyDelete
  10. Very useful information. Thank you so much

    ReplyDelete
  11. wonderful information...Thank you very much..!!

    ReplyDelete
  12. oh my god really you are amazing, many thank

    waleed fetoh

    ReplyDelete
  13. Good information. Thank you.

    ReplyDelete
  14. Useful information. Thanks for sharing.

    ReplyDelete
  15. amazing, very useful information....

    ReplyDelete
  16. very very usefull and handy.. thanks for sharing

    ReplyDelete
  17. Vikram BrahmachariJuly 17, 2016 at 9:35 AM

    Thanks for consolidated list of 12c features

    ReplyDelete
  18. Really quick way to refer list of new features. thank you

    ReplyDelete
  19. Very useful
    Just for point 8 (Cascade for TRUNCATE) worth mentioning, that the foreign key constraint should be created with on delete cascade, then only truncate cascade will work.

    ReplyDelete
  20. Wonderful, being an Oracle developer for over 14 years, I was always thinking about why Oracle is not providing these features and finally they are out.
    Sequence as Default Value
    VARCHAR2 length up to 32767
    Top-N feature, would be a beauty if this works with group by too. I have not checked it yet.
    IDENTITY Columns

    I am now eagerly waiting for my customer to upgrade with 12c.

    ReplyDelete
    Replies
    1. You should look at the Oracle 12c R2 features too, amazing list
      http://nimishgarg.blogspot.in/2016/10/top-15-new-features-of-oracle-database.html

      Delete
  21. very useful and handy i have few questions between sequence and identity columns .
    where sequence can generate the numbers as per your requirement based on increment value.
    where as identity column will increment the value.
    Apart from this any difference are there in using irrespective to above comments

    ReplyDelete