Wednesday, 28 December 2016

Database Magic with Oracle Database 12c

A query on the SALES table takes normally more than 2 minutes but setting the database_performance parameter to SUPER_FAST makes it … as expected super fast: less than 1 second. Setting the database_performance parameter to SUPER_SLOW makes the query hang. Again “as expected”.

Tuesday, 27 December 2016

Invisible Columns In Oracle Database 12C

In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the “SELECT *” or “INSERT INTO VALUES” statements unless specifically selected – it can be reverted back to visible by using ALTER TABLE command.

Monday, 26 December 2016

Enterprise Manager Database Express in Database 12c

  • Introduction
Key things to remember about Enterprise Manager Database Express.

EM DB Express is not Cloud Control or Grid Control!
EM DB Express is not a replacement for the DB Control from 11g, as it has significantly less functionality.
A DBA will not be able to administer the database using just EM DB Express.

Friday, 23 December 2016

Implicit Statement Results in Oracle Database 12c (DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT)

Implicit statement results will simplify the migration of stored procedures from other database engines to Oracle 12c onward.
  • Background
Spend more than a few minutes with a Transact-SQL developer and you will have a conversation something like this.

Thursday, 22 December 2016

Recovery Manager (RMAN) Database Duplication Enhancements in Oracle Database 12c

This article discusses the new functionality added to the DUPLICATE command in Oracle 12c.
  • Active Database Duplication using Backup Sets
In previous releases, active duplicates were performed using implicit image copy backups, transferred directly to the destination server. From 12.1 it is also possible to perform active duplicates using backup sets by including the USING BACKUPSET clause. Compared to image copy backups, the unused block compression associated with a backup set can greatly reduce the amount of data pulled across the network for databases containing lots of unused blocks. The example below performs an active duplicate of a source database (cdb1) to a new destination database (cdb2) using backup sets rather than image copy backups.

Tuesday, 13 December 2016

Real-Time Database Operation Monitoring in Oracle Database 12c

Oracle 11g introduced Real-Time SQL Monitoring, allowing you to watch the progress of long running SQL, or SQL you explicitly asked to be monitored using the MONITOR hint. Oracle 12c extends this functionality, allowing you to explicitly monitor composite operations made up of a variety of SQL statements and PL/SQL calls containing SQL. The DBMS_SQL_MONITOR package provides the API for this functionality, as well as rehousing some of the subroutines previously found in the DBMS_SQLTUNE package.

Monday, 12 December 2016

Control Invoker Rights Privileges in Views in Oracle Database 12c (BEQUEATH CURRENT_USER)

Oracle 12c Release 1 introduced proper support for invoker rights function calls in views for the first time.
  • Setup
Create the following users to support the examples below.

CONN sys@pdb1 AS SYSDBA

Friday, 9 December 2016

Session-Private Statistics for Global Temporary Tables in Oracle Database 12c

Statistics gathered for global temporary tables (GTTs) were common to all sessions. If you knew the GTTs would need vastly different statistics for each session, you could avoid statistics and rely on dynamic sampling to provide the relevant information. In Oracle database 12c it is possible to have session-private statistics for global temporary tables.
  • Controlling Session-Private Statistics
Session-private statistics are controlled using the GLOBAL_TEMP_TABLE_STATS global preference in the DBMS_STATS package. By default session-private statistics are enabled, as shown below.

Thursday, 8 December 2016

DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c

Views can be a useful way to hide complexity from developers, but that can in turn cause problems. It's easy to write apparently simple statements, that result in extremely complex SQL being sent to the server. The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement. A simple example of this is shown below.

Wednesday, 7 December 2016

Edition-Based Redefinition Enhancements in Oracle Database 12c

  • FUNCTION
  • LIBRARY
  • PACKAGE and PACKAGE BODY
  • PROCEDURE
  • TRIGGER
  • TYPE and TYPE BODY
  • SYNONYM
  • VIEW
In Oracle 11gR2, if editioning was enabled for a user and you created a new object that was an editionable object in that user's schema, the object you created was automatically editionable. It was not possible to create non-editionable objects of one of these types in an edition enabled schema.

Tuesday, 6 December 2016

Multitenant : Flashback of a Container Database (CDB) in Oracle Database 12c Release 1

Setup

This article assumes the following things are in place for the examples to work.
  • You have a container database (CDB). You can see how to create one.
  • Your container database (CDB) has at least one pluggable database (PDB). You can see how to create one.
  • You have the flashback database feature enabled on the CDB. You can see how to do that.
  • You have backups of your CDB and PDBs. You can see how to do that.

Monday, 5 December 2016

Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB)

Overview

The multitenant option represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB).
  • Container Database (CDB) : On the surface this seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (controlfiles, datafiles, undo, tempfiles, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.

Friday, 2 December 2016

Re-registering Databases In A Cluster With srvctl: Problems and Solutions

Recently I wanted to create a policy managed database in my 12.1.0.2 flex cluster setup, but free servers are not available. Hence I had to unregister an administrator managed RAC database (amdbh12) to make the servers available. Having completed my testing, I dropped the policy managed database and then wanted to drop the database amdbh12 using DBCA. It was then that I realized that:

Thursday, 1 December 2016

SQL/JSON Functions in Oracle Database 12c Release 2

Setup

The examples in this article use the following tables.

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;