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;
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);
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;
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;
Moving and Renaming datafile is now ONLINE,
no need to put data file in offline.
SQL> alter database move datafile 'path' to 'new_path';
SQL> alter database move datafile 'path' to 'new_path';
The TRUNCATE command :-has been enhanced
with a CASCADE option which follows child records.
