Wednesday, 11 January 2017

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c

The PDB CONTAINERS clause allows data to be queried across multiple PDBs.

Setup

We need to create 3 PDBs to test the CONTAINERS clause. The setup code below does the following.
  • Creates a pluggable database called PDB1.
  • Creates a PDB1 with a local user called LOCAL_USER that owns a populated table called LOCAL_USER_TAB.
  • Creates two clones of PDB1 called PDB2 and PDB3.

Tuesday, 10 January 2017

Oracle Data Type Implicit Conversion Hierarchy

In Oracle, under certain circumstances, an implicit data type conversion precludes the use of indexes. Perhaps you have a vague idea of what an implicit data type conversion hierarchy is, and you might even ignore the subtlety of the implicit data type conversion direction within a query predicate expression. Hopefully this article will shed some light on this concept.

Monday, 9 January 2017

Interval-Reference Partitioning in Oracle Database 12c

Oracle 12c lifts that restriction, so you can now use interval-reference partitioning.

Interval-Reference Partitioning

The following code creates an interval partitioned table (T1) with yearly partitions and an interval-reference partitioned table (T2).

DROP TABLE t2 PURGE;
DROP TABLE t1 PURGE;

Friday, 6 January 2017

Asynchronous (Delayed) Global Index Maintenance for DROP and TRUNCATE Partition in Oracle Database 12c

Oracle 12c can optimize the performance of some DROP PARTITION and TRUNCATE PARTITION commands by deferring the associated index maintenance, while leaving the global indexes in a valid state.

1. Setup

The following code creates and populates a partitioned table with global indexes.

-- Create a partitioned table with some global indexes.
DROP TABLE t1 PURGE;

Thursday, 5 January 2017

Edition-Based Redefinition Enhancements in Oracle Database 12c Release 1

Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero down time. Editionable object types include:
  • FUNCTION
  • LIBRARY
  • PACKAGE and PACKAGE BODY
  • PROCEDURE
  • TRIGGER
  • TYPE and TYPE BODY
  • SYNONYM
  • VIEW

Wednesday, 4 January 2017

Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 7 (OL7)

Oracle Linux 7 is a production release, but Oracle Database 12c is only supported on it from (12.1.0.2) onward. This installation should not be used for a real system when using 12c database versions prior to 12.1.0.2.

This article describes the installation of Oracle Database 12c Release 1 (12.1) 64-bit on Oracle Linux 7 (OL7) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

Tuesday, 3 January 2017

Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT.

Monday, 2 January 2017

Reading Data in Oracle Database 12c

1. For DBAs and Developers, the words READ and SELECT have been for years somehow synonyms. In 12c, is there now any difference?

2. Before pluggable databases, selecting data from the SALES table for instance meant selecting data from a table called SALES in a certain SCHEMA within the database. How about if a table called SALES belongs to several pluggable databases under the same schema name?