Sunday, 30 April 2017

Multitenant : Startup and Shutdown 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 demonstrates how to startup and shutdown container databases (CDB) and pluggable databases (PDB).

  • Container Database (CDB)

Startup and shutdown of the container database is the same as it has always been for regular instances. The SQL*Plus STARTUP and SHUTDOWN commands are available when connected to the CDB as a privileged user. Some typical values are shown below.


Friday, 28 April 2017

What is Multitenant Architecture and Its Benefits Pluggable and Container Databases

What is Multitenant Architecture?  

This feature allows you to create and maintain many pluggable databases within an multitenant container database.

Multiple tenants share same resources on a mutual benefit for different purposes at a very broad level. The same applies to Oracle Database where Multiple Databases share a single instance of resources aiming for different purposes on the same Server. This Oracle Database which is built on Multitenant foundation is called Container Database(CDB), and each container(tenant) residing inside is called Pluggable Database(PDB, Container).

Monday, 24 April 2017

Getting Started with Azure SQL Data Warehouse - Part 4


Microsoft introduced Azure SQL Data Warehouse, a 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 talked about the different types of tables we can create in SQL Data Warehouse and how it impacts performance and best practices around them. I also demonstrated how to get started with creating a SQL Data Warehouse database and to connect to it from SQL Server Management Studio (SSMS). In this article, I am going to talk in detail about round-robin and distributed tables, and how to create them. I will also discuss how partitioning works in SQL Data Warehouse and look at the impact of choosing the right distribution key. I will also cover PolyBase in detail and how you can leverage it to easily and quickly import or export data from SQL Data Warehouse.

Thursday, 20 April 2017

Database as a Service: A Practicum

Vendors now offer Database as a Service (DBaaS) as part of a bundled solution of managed IT services. The selling point is that businesses will not need to purchase and install database software, create and maintain databases, hire database administrators, or worry about upgrades and scaling up when their business expands. Delegating database management services to an external provider may speed up development time, and allows a company to pay only for the data they store and the database services they consume.

However, delegating database administration services has many hidden dangers. In this article, we focus on how delegating data modeling to an outside service can cause problems when making application updates, changing business rules or doing performance tuning.

Thursday, 13 April 2017

Big Data DBA Support Strategies

The Database Administrator (DBA) is usually a technical professional who supports one or more hardware and software platforms that provide application solutions. However, technical details such as SQL tuning, hardware and software upgrades, and database designs tend to be tactical in nature.  It is essential that the DBA also maintain a strategic outlook in order to get ahead of potential problems. This article addresses two of these strategies: knowing application breaking points and preparing for future enhancements to big data applications.

Wednesday, 12 April 2017

The Problem with SQL Calling PL/SQL Calling SQL

The reduction in query execution time was ten seconds. The observant among you will have noticed that the statement processed over 60,000 rows. That's a saving of less than 200 microseconds per row.

Oracle Database PL/SQL, SQL, Oracle Database Certifications

Tuesday, 11 April 2017

Oracle Data Warehouse Solutions: Delivering Business Value From The Datacenter To The Cloud

Leading-edge organizations differentiate themselves through analytics, and are further expanding their competitive advantage with new data sources.  This initiative does include several obstacles and challenges.  Today’s digital world is already creating data at an explosive rate, but the next wave is on the horizon, and this momentum is driven by the emergence of IoT data sources.  These sources include connected cars, smart-appliances, wearable technology and industrial sensor data.  As a result, IT leadership is faced with two diametrically opposed forces.  On one hand, IT resources are consumed dealing with the operational challenges associated with the constant acquiring, deploying and maintaining of necessary infrastructure to support data consumption.  On the other hand, IT leadership is pulled in the opposite direction to support evolving requirements of line-of-business owners.  These polarizing pressures create a bitter dichotomy where IT attempts to address both demands, but becomes incapable of doing so to the satisfaction of all stakeholders.

Monday, 10 April 2017

PL/SQL Object Types for JSON in Oracle Database 12c

  • Introduction

In Oracle Database 12c Release 1 the JSON functionality was focused on consuming JSON data and converting it into relation data. Generation of JSON relied on string handling or packages such as the APEX_JSON package. Oracle Database 12c Release 2  includes new JSON object types to support the in-memory parsing, generation and update of JSON data directly from PL/SQL.
  1. JSON_ELEMENT_T : The supertype some of the other object types extend. You will not often use this type directly. You can cast a JSON_ELEMENT_T to a subtype using TREAT AS. For example, "l_obj := TREAT (l_elem AS JSON_OBJECT_T);"
  2. JSON_OBJECT_T : An object representing a JSON object.

Wednesday, 5 April 2017

Node.js and Oracle NoSQL Database

Oracle NoSQL Database is an interesting option to consider when you want a schemaless, fast, scale-able database which can provide relaxed (eventual) consistency. Oracle provides a Node.js driver for this database. In this blog I’ll describe how to install Oracle NoSQL database and how to connect to it from a Node.js application.

The Node.js driver provided by Oracle is currently in preview version 3.3.7. It uses NoSQL client version which does not work with 4.x versions of NoSQL database, so I downloaded Oracle NoSQL Database, Enterprise Edition 12cR1 ( from here (the version number was closest to the version number of the client software).

Tuesday, 4 April 2017

How to Move a Table to a Different File Group

There may be a time when you want to move a table from one file group to another.  There could be lots of different reasons for wanting to move your data file.  One of the reasons why you might want to do this is to improve performance.  It is easy to move a table from one filegroup to another if it contains a clustered index.

To show you how to move a table from one file group to another let me create an example.

First let me create a sample database using the following code:

Saturday, 1 April 2017

Oracle for Absolute Beginners: Data Types

All databases stand on a tripod of datatypes: strings, numbers and dates. And so I’d imagine that by Day 2 or so of dabbling with Oracle you’d be starting to get reasonably comfortable with the VARCHAR2, NUMBER and TIMESTAMP data types. And that’s a good thing, familiarity with those three data types will take you a long way into a career of working with the Oracle database.

However, there are a legion of ‘second string’ data types that you’ll probably want to be passingly familiar with too. It’s kinda like when you meet someone, and fall in love. Sure you want to know every single thing about the gorgeous person you’re in love with – but to really know them, don’t you need to know a bit about the family they come from too?