Few New features in Oracle 12C (As published in asktheoracle.net)
1)Cascading Truncates:-
If you've been using Oracle for a while you probably already know about cascading deletes - when the parent record is deleted any child records are also deleted. This is enabled by specifying the clause ON DELETE CASCADE when defining the foreign key constraints. Here's an (artificial) example using employees and departmnets just to refresh your memory.
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY
,department_name VARCHAR2 (30));
department_id NUMBER PRIMARY KEY
,department_name VARCHAR2 (30));
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY
,employee_name VARCHAR2 (30)
,department_id NUMBER,
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE);
INSERT INTO departments VALUES (1,'one');
INSERT INTO employees VALUES (1,'one',1);
DELETE departments ;
SELECT * FROM employees;
employee_id NUMBER PRIMARY KEY
,employee_name VARCHAR2 (30)
,department_id NUMBER,
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE);
INSERT INTO departments VALUES (1,'one');
INSERT INTO employees VALUES (1,'one',1);
DELETE departments ;
SELECT * FROM employees;
The select statememnt returns no rows because the employee record (the child) was deleted (silently) when we deleted the department (the parent) record. Without the ON DELETE CASCADE clause we would get the error ORA-02292: integrity constraint (HR.FK_EMP_DEPT) violated - child record found.
If we attempt to truncate the table we also get an error message -ORA-02266: unique/primary keys in table referenced by enabled foreign keys. However in Oracle 12c we can add the clauseCASCADE to the TRUNCATE statement like so:
TRUNCATE TABLE departments CASACDE
to do a fast, unlogged and, therefore, irreversible, delete on departments and employees as long as the foreign key constraint has been declared with the ON DELETE CASCADE clause, otherwise we getORA-14705: unique or primary keys referenced by enabled foreign keys in table "HR"."EMPLOYEES2".
2)Session-level sequences:-
In Oracle 12c the CREATE SEQUNCE has a new clause to specify the scope of the sequence - either GLOBAL the default, or SESSION. For example CREATE SEQUNCE sec1 SESSION. Session-level sequnces are designed to be used with global temporary tables and generate values that are unique only within the session. Also, as soon as the session ends, the state of the sequence is lost so a new session would generate values starting from the initial value defined for the sequence again.
