Friday, 30 September 2016

DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1

DEFAULT Values Using Sequences

In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column. You should also consider using Identity columns for this purpose.

In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the ON NULL clause described in the next section.

Thursday, 29 September 2016

Oracle Database 12.1.0.2c: Hot Cloning Of Pluggable Databases

Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 2 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c:
  • PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online. In Oracle 12.1.0.1c, a pluggable database can be cloned only if it is read-only.
  • PDBs can be remote cloned, i.e. the source PDB may be located in a remote container. This feature was listed in Oracle Database 12c Release 1 (12.1.0.1), but didn’t work.

Wednesday, 28 September 2016

Oracle Database 12.1.0.2c : Hot Cloning of Non-Container Databases

In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods:
  • Using DBMS_PDB
  • Using datapump (expdb, impdp)
  • Using GoldenGate replication
The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network. Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the CREATE PLUGGABLE DATABASE command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in READ ONLY mode for cloning so that it can be cloned online. It is a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

Thursday, 22 September 2016

JSON Support in Oracle Database 12c Release 1

This article describes the server side support for JSON in Oracle Database 12c Release 1 (12.1.0.2). There is a lot of variety in the syntax of the JSON functionality, which for simplicity is avoided here.
  • Introduction to JSON
1. JSON stands for JavaScript Object Notation
2. JSON is a lightweight data-interchange format
3. JSON is language independent *
4. JSON is "self-describing" and easy to understand

The following JSON example defines an employees object, with an array of 3 employee records:

Wednesday, 21 September 2016

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c

The PL/SQL language is a combination of two distinct parts:
  1. PL : Procedural Logic.
  2. SQL : Structured Query Language
The two are meshed together so well it is easy to forget this separation exists. One area where the separation does show is in the differing support data types. In previous database versions, binding of PL/SQL-only data types from JDBC, OCI, static and native dynamic SQL was a problem. Oracle 12c now supports the binding of additional PL/SQL-only data types to anonymous blocks, PL/SQL function calls in SQL, the TABLE operator in SQL and CALL statements.

Monday, 19 September 2016

Automatic Diagnostics Repository (ADR) Enhancements in Oracle Database 12c (ADRCI)

The basic concepts of the Automatic Diagnostics Repository (ADR) remain unchanged in Oracle Database 12c, but there have been several notable changes to the way the diagnostics information is handled.

DDL Log


Setting the ENABLE_DDL_LOGGING parameter to TRUE activates DDL logging. All DDL statements are logged into a plain text file and an XML file in the ADR location.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl_{SID}.log
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

Saturday, 17 September 2016

Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1

By default, PL/SQL program units are created using definer rights and as such are executed with all the privileges granted directly to the user that created them. This can be very useful when you want low privileged users to perform tasks that require a high level of privilege. In these cases the tasks can be wrapped up in a PL/SQL program unit, with execute privilege granted on that program unit to the low privileged user. The problem with definer rights is it is very easy to accidentally expose excessive functionality to a user.

An alternative is to create the program unit with invoker rights, so it is run in the context the calling user, rather than the user that created it. The advantage of this is the program unit is only able to perform tasks that the calling user has privilege to perform, including those privileges granted via roles. Invoker rights has a number of issues including:

Friday, 16 September 2016

Oracle 12.1.0.2c : Hot Cloning of Non-Container Databases

In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods:
  • Using DBMS_PDB
  • Using datapump (expdb, impdp)
  • Using GoldenGate replication
The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network. Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the CREATE PLUGGABLE DATABASE command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in READ ONLY mode for cloning so that it can be cloned online. It is a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

Thursday, 15 September 2016

WITH Clause Enhancements in Oracle Database 12c

In addition to the Subquery Factoring Clause, Oracle 12c includes a PL/SQL declaration section in the WITH clause.

1. Setup


The examples in this article require the following test table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM   dual
CONNECT BY level <= 1000000;

Tuesday, 13 September 2016

This Oracle Bug Could Bite You

An interesting bug in Oracle 11.2.0.4 was reported to Oracle Support recently, a bug that can cause a DBA to scratch his or her head in either wonder or disbelief. Listed as Bug 24390197 it is fairly easy to replicate. The example starts by creating an empty tablespace of sufficient size to contain several tables, then creates the DEMO schema and adds records to consume space, leaving more than enough space to create a table of approximately 1 MB in size:

SQL> --
SQL> -- Create a blank tablespace to run
SQL> -- the example from
SQL> --
SQL> connect / as sysdba
Connected.

Monday, 12 September 2016

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1

Introduction


A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set. This concept is not a new one. In fact, Oracle already provides multiple ways to perform Top-N queries, as discussed here. These methods work fine, but they look rather complicated compared to the methods provided by other database engines. For example, MySQL uses a LIMIT clause to page through an ordered result set.

Sunday, 11 September 2016

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."
The first part of the second paragraph seems pretty obvious. Why would we expect to have two PDBs with the same name in the same container? The second part of the second paragraph is less obvious though. The PDB name must be unique amongst all the PDBs serviced by a single listener.

Friday, 9 September 2016

Managing SQL Server Backup and Restore History Information

Every time you backup or restore a database SQL Server keeps history information about the backup or restore operation.  The backup and restore information is captured and stored in the msdb database.  This backup and restore information if left unchecked can take up more and more space over time.  To minimize msdb storage space you need to consider removing the backup and restore history records when they are no longer useful.

Determining how long to keep backup history information is site specific.  Meaning how long to keep the backup and restore information is based on the requirements of an individual environment.  For most environments the value of backup and restore information diminishes over time.  For example the backup information for the current backup chain has more value than the backup information for a backup taken two months ago, or maybe even a week ago.

Thursday, 8 September 2016

SQL*Loader Enhancements in Oracle Database 12c Release 1

This article presents an overview of the enhancements to SQL*Loader in Oracle Database 12c Release 1.
  • Setup
  • Direct NFS (DNFS) Support
  • Extended Data Type Support
  • SQL*Loader Express
  • SQL*Loader Command Line Changes
  • SQL*Loader Control File Changes
  • Audit SQL*Loader Direct Path Loads

Wednesday, 7 September 2016

Collections in Oracle PL/SQL

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.

1. Index-By Tables (Associative Arrays)


The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

Tuesday, 6 September 2016

Using the TABLE Operator with Locally Defined Types in PL/SQL

In Oracle 12c, the TABLE operator can now be used in PL/SQL with locally defined types. In previous releases, the TABLE operator would only work with locally defined types if they were used within pipelined table functions. The removal of this restriction means this functionality is available for use with associative arrays, as well as nested tables and varrays in PL/SQL.
  • Setup
  • Associative Array Example
  • Nested Table Example
  • Varray Example
  • Supported Types
  • Location of Type Definition

Saturday, 3 September 2016

PL/SQL Enhancements in Oracle Database 10g

PL/SQL Native Compilation


The process of PL/SQL native compilation has been simplified in Oracle 10g. The compiled shared libraries are now stored in the database and extracted as necessary. This means they form part of the normal backup and recovery process, require no manual maintenance and are available in Real Application Cluster (RAC) configurations. Native compliation of the package specification and body are independant of each other, meaning either one, the other or both can be natively compiled.

The PLSQL_NATIVE_LIBRARY_DIR parameter is the only one which must be set to use native compilation. All other parameters have been obsoleted. The associated compiler commands are stored in the $ORACLE_HOME/plsql/spnc_commands file which should not need to be modified.

Friday, 2 September 2016

Masking Your Data with SQL Server 2016

There are times when you want some users to see part of a column value but not the whole thing, but you want other users to see the complete column value without any masking.  For example you may want to only allow your call center staff to only see the last 4 digits for a credit card number, with the rest of the credit card number masked with 9’s.  Whereas you want your managers to see column values in an unmasked format.  Or you want the same call center employees to only see email addresses where the middle of an email address is masked with X’s, but the first character and the domain suffix contain unmasked character values.  The new SQL Server 2016 Dynamic Data Masking feature will allow you to define these different kinds of masking rules.