New features in Oracle Database 12c

New features in Oracle Database 12c

Size Limit on Varchar2, NVarchar2, Raw Data Types increased:

The previous limit on these data types was 4K. In 12C, it has been increased to 32,767 bytes. Upto 4K, the data is stored inline. I am sure everyone will be happy with this small and cute enhancement

 We can make a column invisible. 

SQL> create table test (column-name column-type invisible);
SQL> alter table table-name modify column-name invisible;
SQL> alter table table-name modify column-name visible;

Oracle Database 12c has new feature called "Identity Columns" 

which are auto-incremented at the time of insertion (like in MySQL).
SQL> create table dept (dept_id number generated as identity, dept_name varchar);
SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name varchar);

 Temporary undo (for global temporary tables) will not generate undo. 

We can manage this by using init parameter temp_undo_enabled (=false|true).

Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we'll get an error. In some cases, we might want two different types of index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).

PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function)

Pagination query:-

SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records.
SQL> select ... fetch first n rows only;
SQL> select ... offset m rows fetch next n rows only;
SQL> select ... fetch first n percent rows only;
SQL> select ... fetch first n percent rows with ties;

select * from employees FETCH FIRST 2 PERCENT ROWS ONLY;

Moving and Renaming datafile is now ONLINE, no need to put data file in offline.
SQL> alter database move datafile 'path' to 'new_path';

The TRUNCATE command :-has been enhanced with a CASCADE option which follows child records.