2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 840 times in 2015. If it were a cable car, it would take about 14 trips to carry that many people.

Click here to see the complete report.

6 Times More LinkedIn Profile Views with The Oracle Digital Certification Badges

Thanks to the Mozilla Open Badges Standard the real benefit for certified software developers started to show up.
According to the OPN, Oracle PartnerNetwork, the individuals with the open LinkedIn account are likely to receive 6 times more profile views if they are in the possession of one of four possible digital badges issued by Oracle.

Read more …


Mozilla Open Badges Standard


Acclaim Home Page

On October 12, 2015 Oracle issued me a badge “Oracle PL/SQL Developer Certified Associated”, stating that

You’ve worked hard to earn this achievement. Share the good news with your professional network, friends and family by claiming your badge.

You can click on my badge (in the main sidebar on my Home Page, or on the About page) and see my credentials.

The badge is issued on the platform Acclaim which

is a badging platform that works with credible organizations to recognize, manage and share professional achievements. Acclaim is backed by Pearson, the world’s leading learning company.

The badge could be shared on the Social Media Platforms like Facebook, Twitter, LinkedIn, and Google+, and could be sent in an e-mail.

Acclaim’s explanation about Open Badges Usefulness is the following:

Representing your credentials as Open Badges gives you the ability to broadcast your skills across the online destinations you are already using to tell your professional story. More importantly, they differentiate you by offering employers single-click access to validated information that is provided in the context necessary to evaluate it. While it is true that many employers are still learning about Open Badges, there is no doubt that the additional efficiency they offer will make them a convenient currency for communicating professional skills over time.

The Mozilla’s short video explains the purpose of Open Badges

Mozilla Open Badges Standard http://openbadges.org/
Acclaim https://www.youracclaim.com/
Pearson Education https://www.pearson.com/

Create Pluggable Database using the seed database as a template

In my previous posts, I combined tools SQL*Plus, SQL Developer, and Oracle Enterprise Manager EM12c for many different tasks. They allow me to connect to the pluggable database PDBORCL, to create PL/SQL subprograms and packages, to run SQL queries, DML, and DDL statements. I also showed how to make the PDBORCL pluggable database larger by adding a new tablespace to it.

All of that was the introduction to this post as I am going to demonstrate how to create a new pluggable database.

Oracle database version introduces 3 new SQL DDL statements:

The CREATE PLUGGABLE DATABASE statement enables us to perform the following tasks:
1) Create a PDB by using the seed pluggable database PDBSEED as a template.
The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
2) Create a PDB by cloning an existing PDB or non-CDB.
The files associated with the existing PDB or non-CDB are copied to a new location    and the copied files are associated with the new PDB. Here, it should be noticed that creating a PDB by cloning a non-CDB is available starting with Oracle Database 12c Release 1 (
3) Create a PDB by plugging an unplugged PDB or a non-CDB into a CDB.
This option is done by  using an XML metadata file.

The pluggable database has four different open modes:
A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs.
A PDB in open read-only mode allows queries but does not allow user changes.
When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB.
When a PDB is in mounted mode, it behaves like a non-CDB in mounted mode. It does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Cold backups of the PDB are possible.

To remove a PDB from a CDB the following should be done:
– Unplug the PDB from a CDB.
– Drop the PDB.

task#11 Connect to the Container database CDB$ROOT as SYSDBA and create a pluggable database “BOOKSTORE” by using the seed pluggable database PDBSEED as a template.

step#1 Create BOOKSTORE database

SYS@orcl > create pluggable database "BOOKSTORE"
2  admin user "BOOKSTOREADMIN"
3  identified by "BOOKSTOREADMIN"
4  file_name_convert = ('C:\app\orauser\oradata\orcl\pdbseed\',
5  'C:\app\orauser\oradata\orcl\bookstore\');

Pluggable database created.

The other way to create the pluggable database “BOOKSTORE” is to omitt the clause FILE_NAME_CONVERT.
In that case, the database will attempt to use the Oracle Managed Files or the PDB_FILE_NAME_CONVERT initialization parameter.
In my environment, the Oracle Managed Files are not specified, so the database will attempt to use the parameter PDB_FILE_NAME_CONVERT which could be defined by using statements ALTER SYSTEM or ALTER SESSION. For this task I will be using ALTER SESSION statement

SYS@orcl > ALTER SESSION SET PDB_FILE_NAME_CONVERT = ('C:\app\orauser\oradata\orcl\pdbseed\',
2  'C:\app\orauser\oradata\orcl\bookstore\');

Session altered.

Now, the CREATE PLUGGABLE DATABASE statement is simple

SYS@orcl > create pluggable database "BOOKSTORE"
2  admin user "BOOKSTOREADMIN"
3  identified by "BOOKSTOREADMIN";

Pluggable database created.

If the parameter PDB_FILE_NAME_CONVERT is not set, then an error will occur after the CREATE PLUGGABLE DATABASE statement.
step#2 Open BOOKSTORE database in READ WRITE mode

SYS@orcl > alter pluggable database "BOOKSTORE" open read write;

Pluggable database altered.

step#3 Prove that CDB$ROOT now contains the seed and two PDBs

SYS@orcl > show PDBS

---------- --------------------  ---------- ----------
2          PDB$SEED              READ ONLY  NO
3          PDBORCL               READ WRITE NO
4          BOOKSTORE             READ WRITE NO

step4# Show the open time of PDBs

SYS@orcl > select substr(name,1,10) name
, con_id, open_mode
, to_char(open_time,'DD-MON-YYYY') open_time
from v$pdbs;

---------- ---------- ---------- ----------- ----------
PDB$SEED            2 READ ONLY  10-SEP-2015  975175680
PDBORCL             3 READ WRITE 10-SEP-2015 2747269120
BOOKSTORE           4 READ WRITE 10-SEP-2015  996147200

step5# Start SQL Developer and show that CDB$ROOT has two pluggable databases: PDBORCL and BOOKSTORE

The Container Database

The Container Database

step#6 Open the list Datafiles in the DBA View and show that there is no additional datafiles for CDB$ROOT.



step#7 Open the list Tablespaces in the DBA View and show that there is no added tablespaces for CDB$ROOT either.



step#8 Create a new connection to BOOKSTORE database in the Connection View

Create Connection

Create Connection

The following elements should be specified in order to create a connection: the connection name,  the user name,  the password,  connection type and role, the hostname, the port, and the service name.

The Connection has been Created

The Connection has been Created

We are connected to the BOOKSTORE database which is empty of the user elements for now.

step#9 Click on the bookstore connection and choose the option Manage Database to show BOOKSTORE Tablespaces.


Manage Database

step#10 BOOKSTORE contains the following tablespaces: SYSTEM, SYSAUX, and TEMP. In order to create any schema, BOOKSTORE needs a new tablespace for users tables and other schema elements.

BOOKSTORE tablespaces

BOOKSTORE tablespaces

step#11 Open the Database Configuration Assistant and create EM12c port 5504 as the entry point for BOOOKSTORE database.

The Configuration Assistant

The Configuration Assistant

step#12 Open the browser with “localhost:5504/em” and login to BOOKSTORE as SYSDBA



step#13 Show how many tablespaces are there in the BOOKSTORE database

Tablespaces Of BOOKSTORE

Tablespaces Of BOOKSTORE

step#14 Create a new tablespace “USER”

Create Tablespace USER

Create Tablespace USER

step#15 The USER tablespace is created and we are ready to create the very first schema in the BOOKSTORE database.

The USER tablespace

The USER tablespace

step#16 Back to SQL Developer again, the BOOKSTORE database is ready for the new elements.

SQL Developer worksheet

SQL Developer worksheet

step#17 Click on the BOOKSTORE connection in the Connect View and choose the option Create User.

Create Schema

Create Schema

step#18 Enter the name, the password, roles, privileges, quotas, and default and temporary tablespace for the new user.

Enter details about the schema

Enter details about the schema

The following SQL CREATE USER statement will be automatically created and executed and the user “CODE” will be created



step#19 Open the Oracle Network Manager and create the service @bookstore,
enter the connection type, protocol, the host name, and the port number.

Net Manager

Net Manager

step#20 Use SQL*Plus and connect to the BOOKSTORE database as user code/code@bookstore



step#21 Show The Oracle Listener configuration


LSNRCTL for 64-bit Windows: Version - Production on 10-SEP-2015 23:13:18

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

The following operations are available
An asterisk (*) denotes a modifier or extended command:

start           stop            status          services
version         reload          save_config     trace
quit            exit            set*            show*

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version - Production
Start Date                10-SEP-2015 15:04:56
Uptime                    0 days 8 hr. 8 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\orauser\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\orauser\diag\tnslsnr\HPblue\listener\alert\log.xml
Listening Endpoints Summary...
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "bookstore" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
"DEDICATED" established:0 refused:0
Service "bookstore" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
"DEDICATED" established:50 refused:0 state:ready
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
"DEDICATED" established:50 refused:0 state:ready
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
"D000" established:68 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: HPBLUE, pid: 2564>
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
"DEDICATED" established:50 refused:0 state:ready
The command completed successfully


The Oracle Database 12c SQL statement CREATE PLUGGABLE DATABASE allows us to create a new pluggable database  by using the seed pluggable database PDBSEED as a template. The new database does not contain any user tablespace for schemas and the schema elements ( tables, indexes, etc.) and it should be created next.

The other ways to create pluggable databases are by cloning an existing PDB or non-CDB, or by plugging an unplugged PDB or a non-CDB into a CDB.

Beside SQL*Plus, SQL Developer, and Oracle Enterprise Manager EM12c,  two new tools are introduced : the Oracle Net Manager and the Database Configuration Assistant.

TRUE or FALSE – Extended Period of Support is June 2021

The investment in the Oracle Database once done has never been lost. Now, almost any older versions of the Oracle Database could be upgraded to the latest Oracle Database 12c which is

The Premier Support for Oracle Database Version 11.2 has ended on Jan 31, 2015.

It is now 9 months over!

But, the Extended Period of Oracle Support for the current database version is June 2021.

There are two ways to upgrade to Oracle database 12c:

1) The Direct Path and Higher

2) The Indirect Path

7.3.3 (lower) to 7.3.4 to to to 12.1.x

8.0.5 (or lower) to 8.0.6 to to to 12.1.x

8.1.7(or lower) to to to 12.1.x (or lower) to to to 12.1.x (or lower)to to 12.1.x lower) to to 12.1.x to to 12.1.x to to 12.1.x

The direct path allows you to upgrade directly from versions,, and and Higher to The Premium Period of Support for is until June 2018.

The indirect path requires you to plan and organize the upgrade to in several steps. The older the version of the Oracle database that you are going to upgrade, the longer the process.

The oldest version that could be upgraded to is 7.3.3 or lower to 7.3.4.

As of 8i and 9i upgrade, there are 2 to 4 steps to do it, for an example, versions 8.1.7(or lower) are first to be upgraded to, then will be upgraded to, and finally the version will be upgraded to

Again, think about it, it is a big advantage! The Premium Period of Oracle Support for is until June 2018 and the Extended Period of Oracle Support for is June 2021. It is about 6 years from today!

The Oracle Corporation recommendations about each possible path of the upgrade, direct or indirect, are explained in detail in the document

Oracle® Database Upgrade Guide 12c Release 1 (12.1) E41397-11

The cost of the upgrade includes in some extreme cases:

  1. the replacement of the hardware,
  2. the upgrade or replacement of the operating systems,
  3. the change or replacement of the applications,
  4. the change of the used client software,
  5. the prolonged downtime of your system,
  6. the number of the databases that you are upgrading.

The longer you avoid the upgrade to the Oracle Database 12c, the harder and more expensive it becomes.

To speed up the hole process of the upgrade, the Oracle Corporation advises you to run the new preupgrade script preupgrd.sql which will generate the fix up scripts and gives you the starting information about issues that might be present both before and after the upgrade. That would be the base of how to organize your upgrade and calculate the cost of it.

For the owners of the large systems with more than 100 Oracle databases running, there is the option of the PARALLEL UPGRADE that guarantee you:



Beside the command line script upgrade, there is the Database Upgrade Assistant (DBUA) which automates the hole upgrade process.

The Hands-On-Lab “Upgrade, Migrate and Consolidate to Oracle Database 12c” is available for DOWNLOAD from the website http://blogs.oracle.com/UPGRADE.

The lab will guide you through the following tasks:

  1. Upgrade an database to Oracle
  2. Plug in this database into a CDB
  3. Migrate an database with Full Transportable Export into another PDB
  4. Unplug an PDB and plug/upgrade it into an CDB

If you are in a doubt about when to start the upgrade, now or later, the Hands-On Lab is a good starting point as it will show you how to do it, and allow you to experience the features of straight forward.

The Oracle Database 12c with a new architecture allows a multitenant container database to hold up to 252 pluggable databases.

The main features of the Oracle Database 12c are:

  • High Consolidation Density
  • Rapid Provisioning and Cloning
  • Rapid Patching and Upgrades
  • Managing Many Databases as One
  • Pluggable Database Resource Management.


The process of upgrading to Oracle Database may be costly and long and separated in many steps that should be done as required, but the final product is worth it. In the end, the Oracle Database 12c replaces all previous database versions and gives you the new start. Remember, the Premium Period of Oracle Support for is until June 2018 and the Extended Period of Oracle Support for is June 2021.

The above facts are taken from the following sources:

1) Oracle® Database Upgrade Guide 12c Release 1 (12.1) E41397-11


2) Nitin Maheshwari, PDF document “upgrade-to-oracle-database-12c-2412040-en-in”

3) Mike Dietrich, “Upgrade, Migrate, and Consolidate to Oracle Database 12c” , Oracle Webcast, 16 June 2015


4) Database Upgrade OTN website


EM12c comes to the rescue when you need a Tablespace for the new Application

I like to combine all Oracle Database 12c Tools: SQL*Plus, SQL Developer, and Oracle Enterprise manager Database Express 12 – EM12c. While I am working on a project, this allows me to switch fast from one tool to the other especially when I need to run any DDL statements, then go back to SQL Developer and create and test some PL/SQL code. Usually, EM12c is extremely fast for the tablespace creation or its management, especially when the new project starts.

task#9 In this task I will demonstrate the fastest way to create a tablespace when you  needed it the most.

step# Connect to the pluggable database PDBORCL through EM12c using address “localhost:5502/em”. Ports 5500 and 5502 are by default reserved and assigned to the CDB$ROOT and PDBORCL databases accordingly on my Oracle Database 12c.

EM12c Connect To PDBORCL

EM12c Connect To PDBORCL

step#2 Open the Tablespace control window.

The Pluggable Database Tablespaces

The Pluggable Database Tablespaces

At the moment, we have 6 tablespaces: SYSTEM, SYSAUX, USERS, TEMP, EXAMPLE, and COMPANYAB.

step#3 Click on the Action Button and choose the Create option.

The Action Button

The Action Button

There are other options too, we can Drop any of the existing tablespaces, change the Status of the tablespace, Add a new datafile to the existing tablespace, and set a tablespace as Default one.

step#4 In the open Create tablespace window, enter the name of the new tablespace, in this case “COMPANYCD”, then click “>” button and follow the procedure.

Create a New Tablespace

Create a New Tablespace

step#5 See what SQL “CREATE TABLESPACE” statement will be executed, and make sure that all parameters are correct, such as the name of the datafile, size, logging, and compression. And if it is all as it should be, press OK button to execute that DDL statement.



step#6 The new tablespace has been created successfully.

The Tablespace Successful Creation

The Tablespace Successful Creation

step#7 Finally, The list of tablespaces  has a new member, the tablespace “COMPANYCD”, and we are ready to start a new app.

The List Of the Current Tablespaces

The List Of the Current Tablespaces

step#8 Check with SQL*Plus that the tablespace datafile “COMPANYCD.DBF” is in the right directory on your computer

c:\TEMP>sqlplus / as sysdba

SQL*Plus: Release Production on Wed Sep 9 00:42:10 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > select dbms_xdb.getHttpPort() from dual;




SYS@orcl > select dbms_xdb_config.getHttpsPort() from dual;




SYS@orcl > host

Microsoft Windows [Version 6.3.9600]

(c) 2013 Microsoft Corporation. All rights reserved.

c:\TEMP>dir C:\app\orauser\oradata\orcl\pdborcl

Volume in drive C has no label.

Volume Serial Number is C2DD-F7B2

Directory of C:\app\orauser\oradata\orcl\pdborcl

09/09/2015 01:00  .

09/09/2015 01:00  ..

06/09/2015 22:41 104,865,792 COMPANYAB.DBF

09/09/2015 01:00 104,865,792 COMPANYCD.DBF

06/09/2015 22:41 1,456,218,112 EXAMPLE01.DBF

06/09/2015 22:32 20,979,712 PDBORCL_TEMP012014-12-09_02-14-58-AM.DBF

07/09/2015 18:54 22,290,432 SAMPLE_SCHEMA_USERS01.DBF

06/09/2015 22:41 734,011,392 SYSAUX01.DBF

06/09/2015 22:41 304,095,232 SYSTEM01.DBF

7 File(s) 2,747,326,464 bytes

2 Dir(s) 562,110,873,600 bytes free


SYS@orcl >


The Oracle Corporation recommends that the separate tablespaces should be created for any customer new applications in order to guarantee isolation, security, and easy management of the application and its tablespaces.

In this task I have shown how fast and easy is to create a tablespace on the fly using EM12c when you are desperate to start a new project.

Review – Oracle Database 12c New SQL*Plus commands

SQL*Plus is the Command Line User Interface that is installed with every Oracle Database installation.
It allows the user to connect to the database, perform database administration, create and execute batch scripts, get the description of any schema object, and format, save, and print query result sets.

Every new release of the Oracle Database brings to the users a new SQL*Plus set of commands and options.

task#8 For this task we will use the table DEMO.TEXTS that was introduced in the previous post task#7. Also, the new procedure proc_impl_res was created to demonstrate the SQL*Plus support for implicit results.
step#1 Last Login Time

By default, SQL*Plus displays the time the user last logged on.
This security feature can be turned off with a SQLPLUS command option -NOLOGINTIME.

SQL*Plus Last Login Time

SQL*Plus Last Login Time

step#2 New Administrative Privileges

The new user privileges SYSBACKUP, SYSDG, and SYSKM are supported by SQLPLUS and CONNECT commands.
The existing privileges SYSASM, SYSDBA, and SYSOPER are supported too.

SQL*Plus new user privileges SYSBACKUP, SYSDG, and SYSKM

SQL*Plus new user privileges SYSBACKUP, SYSDG, and SYSKM

step#3 Support for Implicit Results

SQL*Plus can iteratively return results from a PL/SQL Statement without using a local ref cursor.

create or replace procedure proc_impl_res
authid current_user
  l_cursor_min sys_refcursor;
  l_cursor_max sys_refcursor;
  open l_cursor_min for SELECT * FROM TEXTS where winter > 100;
  open l_cursor_max for SELECT * FROM TEXTS where summer > 1000000;
  when others then
SQL*Plus Support for Implicit Results

SQL*Plus Support for Implicit Results

step#4 Displaying Invisible Columns

SQL*Plus has the command SET COLINVI[SIBLE] to allow invisible column
information to be viewed with the SQL*Plus DESCRIBE command.

SQL*Plus Displaying Invisible Columns

SQL*Plus Displaying Invisible Columns

step#5 Pluggable Database Support

The STARTUP command options support pluggable databases.
There are also SHOW command options to display information about pluggable

SQL*Plus Pluggable Database Support

SQL*Plus Pluggable Database Support


[ref8] SQL*Plus® Release Notes Release 12.1 E18402-06
[ref9] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12