OCP 12C – Index and Table Enhancements

Table Enhancements

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.

Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung.
Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening.
You can access real-time performance data from ASH and access the hang analysis data.
Unlike in 11g, you don’t have to explicitly activate the memory access mode, emergency monitoring connects directly to the SGA to retrieve the necessary data.

Real-Time ADDM unlike Emergency Monitoring is used to analyze the root cause of the hung.
Real-Time ADDM works like the normal ADDM but access only the last 10 minutes of ASH data to provide recommendations.
Because Real-Time ADDM only uses data from memory you can still access it when the database is hung.
You can use Real-Time ADDM triggers to proactively monitor the performance of an instance.
- High load
- I/O bound
- CPU bound
- Over-allocated memory
- Interconnect bound
- Session limit
- Process limit
- Hung session
- Deadlock detected
Reports can be viewed in DBA_HIST_REPORTS and DBA_HIST_REPORTS_DETAILS.
To manually generate a report you can use the following procedure

SQL> select dbms_addm.real_time_addm_report() from dual;

You can also generate an ADDM report using the EM Database Express by going to the performance hub et click on “Current ADDM findings“.