Category Archives: Articles

This pages contains all the articles of dba-scripts.com

OCP 12C – Partitioning Enhancements

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 ONLINE;

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 COMPRESS FOR QUERY UPDATE INDEXES ONLINE;

With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when you TRUNCATE PARTITION or EXCHANGE PARTITION.
- A parent table ORDERS
- A child-referenced table INVOICE containing invoces for the corresponding orders in the first table.

Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application.
To create an invisible column:

SQL> CREATE TABLE TEST (ID INT, TEXT VARCHAR2(100));

Table created.

SQL> ALTER TABLE TEST ADD (COMMENTS VARCHAR2(400) INVISIBLE);

Table altered.

You can’t create invisible columns on :
- External Table
- Cluster Tables
- Temporary tables
The views DBA_TAB_COLS, ALL_TAB_COLS, USER_TABLE_COLS now have a HIDDEN_COLUMN column to verify which column is visible or not.
In SQL*PLUS you can also use the following :

SQL> SET COLINVISIBLE ON

SQL> ALTER TABLE TEST MODIFY (COMMENTS VISIBLE);

In oracle 12c the Automatic Diagnostic Repository contains a new log directory with 2 subdirectories :
- DDL
- Debug

When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log.
The DDL logging feature is part of the Oracle Change Management Pack.

The debug log contains unusual events which are not sufficiently critical to be recorded as incidents.
This information is not destinated directly to the DBA and is mainly created to help the Oracle Support in case of problem.