New clause ACCESSIBLE BY  in Oracle 12C enables to extend standard security. It defines list of database objects (later called accessors) that can call your PL/SQL code.

Clause ACCESSIBLE BY can be defined for following statements:

• CREATE FUNCTION
• CREATE PACKAGE
• CREATE PROCEDURE
• CREATE TYPE

List of accessors that can call your code includes database objects like:

• TRIGGER
• FUNCTION
• PROCEDURE
• PACKAGE
• TYPE

Continue reading →

This new feature in Oracle 12C is as default always on. Each TRUNCATE or DROP commands performed on a partition automatically triggers asynchronous global index maintenance. It means that you don’t need to wait for global INDEX maintenance :).

For backward compatibility you still need to specify UPDATE INDEXES clause.

Limitations of asynchronous global index maintenance:

• Only performed on heap tables
• No support for tables with object types
• No support for tables with domain indexes
• Not performed for the user SYS

Asynchronous cleanup orphans in an index can be done:

• Automatically
  • by Oracle job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
• Manually
  • just run above job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
  • run procedure DBMS_PART.CLEANUP_GIDX
  • run sql statement ALTER INDEX REBUILD [PARTITION] – the same like in previous releases
  • run sql statement ALTER INDEX [PARTITION] COALESCE CLEANUP

Continue reading →

In Oracle 12C new parameter for Oracle Data Pump can be specified LOGTIME. It defines if Data Pump messages are timestamped so you can quickly figure out elapsed time between different phases of a Data Pump. Very useful to analyze performance problems and general timings for Data Pump processes.

Syntax

LOGTIME=[NONE | STATUS | LOGFILE | ALL]

Description:

• NONE      – No timestamps on status or log file messages (same as default)
• STATUS   – Timestamps on status messages only
• LOGFILE – Timestamps on log file messages only
• ALL          – Timestamps on both status and log file messages

Continue reading →

In release 12C Oracle automatically gathers statistics for following operations:

• CREATE TABLE AS SELECT
• INSERT /*+ APPEND */ INTO … SELECT on en empty table using direct path

It’s very similar behavior to statistics gathering done during a CREATE INDEX or INDEX REBUILD. No init.ora or any manual settings are required to turn on this feature. Now you can save a lot of time by skipping extra step to collect statistics Continue reading →

In Oracle 12C ALTER TABLE … MOVE PARTITION has been extended with new clause ONLINE.

ALTER TABLE <table name> 
MOVE PARTITION <partition name> ONLINE

ONLINE clause indicates that DML on the table partition will be allowed while moving the table partition.

Global indexes are maintained during the move partition, so a manual index rebuild is no longer required.

Continue reading →

In 12C Oracle has extended 11G reference partitioning. Now It’s possible to use interval partitioned tables as parent tables for reference partitioning.

Requirements and features

• Requires compatibility set to 12.0.0.0 or higher
• ALTER TABLE SET INTERVAL can be set for parent but not for reference-partitioned child tables
• ALTER TABLE SET STORE IN can be set for parent but not for reference-partitioned child tables
• ALTER TABLE SPLIT PARTITION that transforms interval partitions to conventional partitions in parent table construct the corresponding transformation in the child table, creating partitions in the child table as necessary.

Let’s create simple example. Parent table parent_tbl is using RANGE INTERVAL partitioning and child table child_tbl are reference partitioned. Such combination was not possible in previous releases 11g.

create table parent_tbl
(
  id1 number, 
  id2 number,
  constraint parent_tbl_pk primary key(id1)
)
partition by range(id2) INTERVAL(10)
(
  partition p1 values less than (10)
);

create table child_tbl
(
  id1 number, 
  id2 number not null,
  constraint child_tbl_fk foreign key(id2) references parent_tbl
)
partition by REFERENCE(child_tbl_fk);

Continue reading →

In Oracle 12c new word CASCADE can be specified for TRUNCATE and EXCHANGE command for “master” partitioned tables which are referenced by enabled ON DELETE CASCADE referential constraint.

Truncate Cascade for partitioned tables

If you specify TRUNCATE … CASCADE for “master” partitioned table then Oracle Database truncates all child reference tables that reference master partitioned table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child reference tables, grandchild reference tables, and so on, using the specified options.

Following options are available for “master” partitioned tables:

1. In this case child, grandchild tables don’t need to be partitioned

TRUNCATE <table_name> CASCADE

2. In this case child, grandchild tables must be reference partitioned tables

ALTER TABLE <table_name> 
TRUNCATE PARTITION <partition_name> CASCADE

ALTER TABLE <table_name> 
TRUNCATE SUBPARTITION <subpartition_name> CASCADE

Continue reading →