We've covered top-n queries in Oracle 12c New Features part 1, invisible columns in part 2 and multiple indexes and identity columns in part 3, so let's move on to look at cascading truncates, ddl logging and indentity columns.
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 departments just to refresh your memory.
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY
,department_name VARCHAR2 (30));
SQL> CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY
,employee_name VARCHAR2 (30)
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE);
SQL> INSERT INTO departments VALUES (1,'one');
1 row created.
SQL> INSERT INTO employees VALUES (1,'one',1);
1 row created.
SQL> DELETE departments ;
1 row deleted.
SQL> SELECT * FROM employees;
no rows selected
The select statement 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 clause CASCADE to the TRUNCATE statement like so:
Oracle Tips & Tricks to SKYROCKET Your Career!
If you're not already a subscriber to Oracle Tips and Tricks, you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.
SQL> 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 get ORA-14705: unique or primary keys referenced by enabled foreign keys in table "HR"."EMPLOYEES2".
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.
DDL logging means logging of SQL data definition language (DDL) commands and is not to be confused with redo logging of DML commands (insert/update/delete).
In Oracle 12c there is a new initialization parameter ENABLE_DDL_LOGGING which can be set to TRUE or FALSE and this can also be set by use of the ALTER SYSTEM and ALTER SESSION commands for database-wide logging or logging of just the current session, respectively.
When set to TRUE this flag has the effect of logging all DDL commands executed in the database to an XML file located in c:\app\oracle\diag\rdbms\orcl\orcl\log\ddl.
One of the best ways to enhance your Oracle expertise quickly amd easily is to have some formal Oracle training.
This doesn't have to be expnsive. We partner with Gogo training to provide high quality but inexpensive, online,
self-paced training courses to help you gain the kowledge and skills you need to advance your career. All the courses are "on demand" so you can do them in your own time and are taught by industry experts to give you the best resources at your fingertips.
Go to GogoTraining
to see the courses available and enter "ASKTHEORACLE" as
the coupon code when you check out to get a 5% discount.