Friday, 30 June 2017

Getting Started with Azure SQL Data Warehouse - Part 5

Azure SQL Data Warehouse is Microsoft’s new enterprise-class, elastic petabyte-scale, data warehouse service that can scale according to organizational demands in just few minutes. In my last article of the series, I discussed round-robin and the distributed table, how partitioning works in SQL Data Warehouse and looked at the impact of choosing the right distribution key. I also talked about PolyBase and how you can leverage it to import data-in or export data-out from SQL Data Warehouse very easily and quickly. In this article, I am going to cover performance optimization in SQL Data Warehouse and the different aspects that impact performance.

Thursday, 29 June 2017

Multitenant : Configure Instance Parameters and Modify Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article shows how to configure instance parameters and modify container databases (CDB) and pluggable databases (PDB).

Wednesday, 28 June 2017

Multitenant : Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article shows how to manage users and privileges for container databases (CDB) and pluggable databases (PDB).

Tuesday, 27 June 2017

Multitenant : Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB) in Oracle Database 12c

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article demonstrates how to manage tablespaces in a container database (CDB) and pluggable database (PDB).

Thursday, 22 June 2017

Multitenant : Prevent Accidental Creation of a Pluggable Database (PDB) - Lone-PDB

Oracle 12.1 allowed 252 user-defined pluggable databases. Oracle 12.2 allows 4096 user-defined pluggable databases, including application root and application containers. From Oracle 12.1.0.2 onward the non-CDB architecture is deprecated. As a result you may decide to use the Multitenant architecture, but stick with a single user-defined pluggable database (PDB), also known as single-tenant or lone-PDB, so you don't have to pay for the Multitenant option. In Standard Edition you can't accidentally create additional PDBs, but in Enterprise Edition you are potentially one command away from having to buy some extra licenses. This article gives an example of a way to save yourself from the costly mistake of creating more than one user-defined PDB in a Lone-PDB instance.

Wednesday, 21 June 2017

Dynamic Statistics in Oracle Database 12c

Oracle Database 10g introduced Dynamic Sampling to allow the optimizer to gather additional information at parse time if database statistics were missing, stale or insufficient to produce a good execution plan. Dynamic sampling was controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter at instance or session level, or for individual queries using the DYNAMIC_SAMPLING hint, with available values ranging between "0" (off) to "10" (aggressive sampling) with a default value of "2".

Monday, 19 June 2017

Creating a Container Database in 12c

Creating a CDB:


To use the pluggable database feature, we have to specifically create a pluggable enabled CDB.
There are a several different techniques for creating a CDB:

• Manually issuing the SQL CREATE DATABASE command

• Using DBCA

• Generating the required scripts with the DBCA, and then manually running the scripts to create a CDB.

How to Read the Transaction Log File

Have you ever wanted to be able to see the actual transactions that are contained in the transaction log file?  Well there is a way to read the transaction log file using an undocumented function.   In this article, I will show you how to browse the transaction log using this undocumented function.

Thursday, 15 June 2017

Multitenant : PDB Subset Cloning in Oracle Database 12c

The 12.1.0.2 patchset introduced the concept of PDB subset cloning, which allows a subset of all the tablespaces in a PDB to be cloned. Excluding tablespaces can be useful when you want to build a PDB to test a specific piece of functionality, which doesn't require the whole database. It is also useful when splitting instances that were used for consolidation into their individual functional areas.

Wednesday, 14 June 2017

LISTAGG Function Enhancements in Oracle Database 12c Release 2

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to perform string aggregations. The LISTAGG function has been enhanced in Oracle Database Release 2 (12.2), allowing it to handle overflow errors gracefully.

Tuesday, 13 June 2017

Logic/Branch Ordering in PL/SQL

PL/SQL evaluates CASE and ELSIF statements from the top down. As a result, placing the most likely outcomes at the top reduces the amount of evaluations that must be done, improving the performance of your code. This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.

Friday, 9 June 2017

Multitenant : Pluggable Database (PDB) Names

The CREATE PLUGGABLE DATABASE command includes a very brief, but important statement about the naming of pluggable databases (PDBs).

"Specify the name of the PDB to be created. The first character of a PDB name must be alphanumeric and the remaining characters can be alphanumeric or the underscore character (_).

The PDB name must be unique in the CDB, and it must be unique within the scope of all the CDBs whose instances are reached through a specific listener."

Thursday, 8 June 2017

Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c

Oracle allows you to specify the database timezone using a time zone name, listed in the V$TIMEZONE_NAMES view, or using a UTC offset (+/-HH:MI). For performance reasons, Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.

The multitenant architecture allows you to specify a different database time zone for each pluggable database, with the time zone of the container database being used as the default.

Wednesday, 7 June 2017

Multitenant : Metadata Only PDB Clones in Oracle Database 12c

The 12.1.0.2 patchset introduced the ability to do a metadata-only clone. Adding the NO DATA clause when cloning a PDB signifies that only the metadata for the user-created objects should be cloned, not the data in the tables and indexes.

  • Setup


Create a clean PDB, then add a new user and a test table with some data.

CONN / AS SYSDBA

Tuesday, 6 June 2017

Resource Manager : Per-Process PGA Limits in Oracle Database 12c Release 2

Oracle has a long history of improving the management of the Process Global Area (PGA). Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to automate the management of the PGA and set a soft limit for its size. Oracle 11g introduced Automatic Memory Management (AMM), which you should probably avoid. Oracle 12c Release 1 introduced the PGA_AGGREGATE_LIMIT parameter to define a hard limit for PGA size.

Monday, 5 June 2017

Relocate a PDB in Oracle Database 12c Release 2

From Oracle 12.2 onward you can relocate a PDB by moving it between two root containers with near zero-downtime.

Oracle Database Tutorials and Materials

The process is similar to performing a remote clone of a PDB.

Friday, 2 June 2017

Multitenant : PDB Refresh in Oracle Database 12c Release 2

From Oracle Database 12.2 onward it is possible to refresh the contents of a remotely hot cloned PDB provided it is created as a refreshable PDB and has only ever been opened in read only mode. The read-only PDB can be used for reporting purposes, or as the source for other clones, to minimise the impact on a production system when multiple up-to-date clones are required on a regular basis.

Multitenant, Oracle PDB, Oracle Database 12c

Thursday, 1 June 2017

Multitenant : PDBs With Different Character Sets to the CDB in Oracle Database 12c Release 2

In the previous release the character set for the root container and all pluggable databases associated with it had to be the same. This could limit the movement of PDBs and make consolidation difficult where a non-standard character set was required.

In Oracle Database 12c Release 2 (12.2) a PDB can use a different character set to the CDB, provided the character set of the CDB is AL32UTF8, which is now the default character set when using the Database Configuration Assistant (DBCA).