Introduction to Oracle Database 12c

With the launch of DB 12c in 2013, Oracle introduced a new architectural concept, called “multitenant databases”, where you have one super database (=container; CDB) and one or more sub databases (= pluggable DBs; PDB).

Before running the installer on an Oracle Linux 6 environment, a library can be installed through yum to meet all the system per-requisites:

yum install oracle-rdbms-server-12cR1-preinstall

The software installer and DBCA are similar to 11g, except for this screen where you can pre-configure your CDB and PDBs:


This DBCA execution will not only create a CDB and 1 PDB, but a “seed pluggable database” as well.  You can use this seed database as a template to create other pluggable databases.


By default, after running the DBCA, all CDBs and PDBs are up and running.

Now, we will reboot the host machine and try to start all of our components.

Is there a difference when starting the listener and the CDB?

No!  You can start the listener and your CDB in exactly the same way as you did with your pre-12c database.

How can you connect to the CDB?

Very simple: just the same as in the past with pre-12c databases.

$ sqlplus system@apexdev

SQL*Plus: Release Production on Thu Mar 19 22:53:55 2015

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

Enter password:

Last Successful login time: Mon Mar 16 2015 22:20:50 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release – 64bit Production

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


Are the PDBs opened by default, when starting the CDB?

No.  This can be verified by this query:

SQL> select open_mode from v$pdbs where name=’APEXDEV_PDB1′;


The PDB is mounted; to open it, just run this command:

SQL> alter pluggable database apexdev_pdb1 open read write;

Pluggable database altered.

SQL> select open_mode from v$pdbs where name=’APEXDEV_PDB1′;


Note: this must be done as “SYSDBA”.

How can we connect to the PDB?

There are 2 methods:

First method: connect to the CDB and then switch to the PDB by setting the container:

$ sqlplus system@apexdev

SQL> show con_name

SQL> alter session set container=apexdev_pdb1;

Session altered.

SQL> show con_name


Second method: Modify your tnsnames.ora file by adding an entry for the PDB, based on the CDB entry.

Now, you can connect as usual to the PDB:

[oracle@ol6db1 oracle]$ sqlplus system@apexdev_pdb1

SQL*Plus: Release Production on Tue Mar 24 20:37:38 2015

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

Enter password:
Last Successful login time: Thu Mar 19 2015 18:59:51 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name


As you can see, it is all quite easy.  One of the main benefits of this architecture is that you can handle every PDB as a separate database that can be upgraded or plugged/unplugged independently from other databases.

And of course your database will be ready for the cloud!

Another 5 neat 12c features for Oracle developers

In this post I will put 5 other new 12c features in the spotlight (in addition to the features of a previous post), that really makes the 12c an improvement against the previous versions of the Oracle database.

To get this result I listed up all the major new features and wanted to know my top 5 features that would make my life easier(as a developer), excluding the features from the previous  post (I certainly would have added the sequence modification(feature 1)), when doing development on an Oracle database.

  1. Top end query -> I really like this feature and I’m still wondering why it took Oracle so long before creating it. It is something I could have used a lot in the past, but instead I had to create far too complicated, not as nice readable queries to achieve this. How does it work? Well it’s very easy and it’s readable and can be used in a wide variety of cases. Some examples:
    Only get the first 3 rows:
    select * from X order by id
    fetch first 3 rows only;

    Skip the first 3 rows and get the next 3 rows:

    select * from X order by id
    offset 3 rows fetch next 3 rows only;

    Get the first 50% of records

    select * from X order by id
    fetch first 50 percent rows only;

    Get the first 3 rows together with the records equal to these department id’s

    select * from emp order by deptno
    fetch first 3 rows with ties;

    If you want the capture the last rows, you can obviously change ‘first’ with ‘last’…

  2. In the 12c database the use of 32767 characters for a VARCHAR2 in SQL is now available instead of the maximum of 4000(this is also the case for RAW and nvarchar2).
    We all have been waiting a long time for this one and before we had to use the clob datatype.
    But beware this is not an out of the box feature, you will have to execute the lines below before this is enabled :
    shutdown immediate
    startup upgrade
    alter system set max_string_size=EXTENDED scope=both;
    Shutdown immediate

    More info can be found on:

  3. The invisible column is a feature of which I was wondering where I could use it for.
    Well it could be handy when you are adding a column to your table, but you don’t want any existing code to be impacted by it.
    Another case where it could be useful, is when using audit columns.  Columns as the creation_dt, update_dt, user_creation and user_update will only be of any added value when you would like to audit a certain column.
    Packages with inserts, updates, references to this table will not be impacted by the creation of this column.
    On the other hand there is also a risk that you forget that this column is in there, because you have to explicitly call for it (a describe or select * will not show this column). You can create invisible columns like this:


    If you want to make the column visible again, use this:


    In summary it could be handy, but don’t forget this column or it will pollute your table.

  4.  The with clause inline plsql feature is also something that I think is very welcome.
    It will make it possible to create a procedure or function inside your select statement instead of having to create this in a package or function. Oracle also says that this will optimize the performance against having to call a schema procedure/function(I still have to test this).
    A little example:

    FUNCTION fnc$_add_one(p_num IN NUMBER) IS
    RETURN p_num+1;
    SELECT fnc$_add_one(1)
  5.  Most of the time I use the ANSI way of writing for a left outer join, but the oracle way of writing left outer joins is still often used by many of the oracle developers.
    But there was one thing that you could do in ANSI, that you couldn’t in the oracle way.  You couldn’t write multiple tables on the left of an outer join, untill12c…
    In 11g and before when coding something like this:
    select *
    from a,b,c
    where =
    and =
    and = c.id2(+);

    This resulted in -> ORA-01417: a table may be outer joined to at most one other table

    In 12c this will work, also the ANSI solution obviously still works both on 12c and on 11g

    select *
    from a
    JOIN b ON ( =
    LEFT OUTER JOIN c ON ( = AND = c.id2);

Together with the previous post this makes 10 reasons why you should start to use the Oracle 12c database :-)

All things Oracle

There’s a new Oracle source available:  All Things Oracle.

The aim of All Things Oracle is to provide a gateway to the wealth of information and material available for Oracle developers and DBAs.
The site brings articles and other resources of Oracle experts.
Just to name a few:

All very experienced experts that will bring interesting articles!

I will also contribute to this site.
My specialities are SQL, PL/SQL, Forms and Forms Modernization, so expect articles on these topics in the near future on All Things Oracle.

Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more Continue reading

Calling Webservices from pl/sql

For a client of us we had to call a webservice from a database, and the received information that we received from this call was needed in another procedure.

It was the first time for me that I had to do this and I believe that not so many people now of this functionality in the database and that’s why it is obviously a good idea to post this on our blog.

It is even possible to let the database be a webservice provider, but in this case the webservice already existed and should simply be called by the database.

What was the objective: we wanted to call the webservice from the database, get the resulting XML file and analyze some of the content of the returned XML file and then do some actions with the information that the webservice provided us.

Therefore I wrote 3 procedures in a package:
• fnc$_get_xml
• fnc$_handle_xml
• prc$_ws_call

As you will see when you look at the code it is possible to make this much more dynamically but for this case we only had to call only one specific webservice, as usual you can make it very complex but for the blog I made the procedures and functions as simple as possible.

The first function that I will explain is the prc$_ws_call
This procedure will contact the function fnc$_get_xml that will get the xml file. After this function the fnc$_handle_xml will be called to retrieve specific information out of the XML file.
When this is done the received values will be printed

PROCEDURE prc$_ws_call
v_xml VARCHAR2(32767);
v_type cab_base_adres.type%TYPE;
r_receive r_info;
v_xml := fnc$_get_xml(p_search => ‘search value’); –call to webservice
r_receive := fnc$_handle_xml(v_xml); –analization of xml content
dbms_output.put_line(‘value nr1=’||r_receive.value1) ;
dbms_output.put_line(‘value nr2=’||r_receive.value2) ;
dbms_output.put_line(sqlcode||sqlerrm) ;

This function will receive a parameter(search) this parameter will be included in the soap call(called v_soap_request).
Ones that I have build my soap envelope, I have to create a httpRequest, this is the variable v_httpRequest which is of the type utl_http.req.
For this variable I have to set some parameters like the webservice url that I will call, the content-type (plain text in this case), the content_length of the soap envelope and the specification that this is a soapaction. This is the preparation for the call to the webservice.

Next thing to do is to write this data to the body of the http request, this is done with the utl_http.write_text where we give in the http_request and the soap_request.
Now we want to receive the response from our soap call.
This will be created with the utl_http.get_response which is of the utl_http.resp type.

Next thing we want, is to have this file in a readable form. Therefore we use the utl_http.read_text, that will translate the response in a readable variable.

FUNCTION fnc$_get_xml(p_search VARCHAR2)
v_soapRequest VARCHAR2(32000);
v_soapResponse VARCHAR2(32767);
v_httpRequest utl_http.req;
v_httpResponse utl_http.resp;
v_soapRequest :=
‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”no”?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=”; xmlns:apachesoap=”; xmlns:impl=”<the webservice>” xmlns:intf=”<the webservice>” xmlns:soapenc=”; xmlns:tns1=”” xmlns:wsdl=”; xmlns:wsdlsoap=”; xmlns:xsd=”; xmlns:xsi=”; >
<mns: <webservice name>xmlns:mns=”<services link>” SOAP-ENV:encodingStyle=””&gt;
<inputAddress xsi:type=”tns1: <input parameter name>”>
<input search xsi:type=”xsd:string”>’||p_search||'</search>
</mns: <webservice name> >

v_httpRequest:= utl_http.begin_request

utl_http.set_header(v_httpRequest, ‘Content-Type’, ‘text/xml’);
utl_http.set_header(v_httpRequest, ‘Content-Length’, length(v_soapRequest));
utl_http.set_header(v_httpRequest, ‘SOAPAction’, ”);

utl_http.write_text(v_httpRequest, v_soapRequest);
v_httpResponse:= utl_http.get_response(v_httpRequest);
utl_http.read_text(v_httpResponse, v_soapResponse);
RETURN v_soapResponse;
dbms_output.put_line(sqlcode||sqlerrm) ;
dbms_output.put_line(‘Error in fnc$_get_xml’) ;

Great, we have now received the xml file that contains the result of our request. Now we want to retrieve the data we need, out of this XML file.
Therefore I will call the fnc$_handle_xml function with the received XM file as parameter.

I will have to handle the content of the xml file. To make this easier I am going to put the content of the XML file into an XMLType by using the XMLType.createXML function. Now the parameter resp contains the XML file. But I am only interested in a certain part of the XML file. By using the ‘extract’ function I am able to get a certain part out of the hierarchical structure of the XML file.

Next thing that I want to receive is the information of certain parts of this resp variable. I will put his in the resp1 variable which make it possible to always use the resp file for the next value I want to retrieve.

In this example I get 2 values out of the XML file. And I will return this back to the calling procedure.

FUNCTION fnc$_handle_xml(p_xml VARCHAR2)
RETURN r_info
resp XMLType;
resp1 XMLType;
r_result r_info;
resp:= XMLType.createXML(p_xml);
resp:= resp.extract(‘/soap:Envelope/soap:Body/child::node()’
, ‘xmlns:soap=””&#8216;

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
r_result.value1 := resp1.getStringVal();

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
r_result.value2 := resp1.getStringVal();

RETURN r_result;
dbms_output.put_line(sqlcode||sqlerrm) ;
RETURN r_result;

How to reformat the highwatermark of a table after a delete?

For test purpose I had to delete and recreate a lot of records in a database.
So I just did a delete of all the records, every time I wanted to clean up my database.

Some of these tables have a few millions of records. So after a couple of times cleaning up and re-entering data into the database,
I had the impression that the database was getting slower and slower in showing me the results of my query.

I thought that this could have something to do with my indexes but I didn’t know how to fix this.

That’s why I contacted a few of my expert database colleagues and they learned me the following:
The table uses a highwatermark value and every time you add a record into the table the table will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was wrong in suspecting the index to be the cause of this problem it was in fact the table itself who caused this.

So how do you fix this?

If you want to clean a lot of records in a database(in bulk) you have 2 possibilities to keep your Highwatermark clean.

First option, and the one that worked best in my case was: use TRUNCATE TABLE

This will remove all the content of the table and will put the ‘highwatermark’ back to 0.

The advantage of this option is that is will will work a bit faster then a normal deletion.
But there is also one disadvantage. If you want to use this, you will have to disable all the foreign keys for this table before starting the truncate.

So doing this in a live database is not really the best thing to do I believe :-)

The other option is just use the delete as you used to do, but after the deletion of the records use : ALTER TABLE

You can even use the cascade option for this shrink, this will shrink all the highwatermarks of the depending objects.
This option is only available from oracle 10g.

Thanks Erwin and Hans for helping me with this!

SYSMAN Account is Locked

This morning I opened Enterprise Manager and instead of the regular “login”-screen, I found following screen:


Everything is up and running but Enterprise Manager is not able to connect to the database instance. Sounds like a connection problem, not?

These are the steps I followed to solve this issue and to reset the “sysman”-account:

STEP1: Check EM log file:

[oracle@myserver log]$ pwd


 [oracle@ myserver log]$ tail -50 emoms.log

2008-02-11 23:12:04,968 [ApplicationServerThread-11]
ERROR app.SessionObjectManager sessionDestroyed.128 -
java.sql.SQLException: ORA-28000: the account is locked

java.sql.SQLException: ORA-28000: the account is locked

STEP 2: Check database users:

SYSTEM> select username, account_status from dba_users;
------------------------------ -------------


STEP 3: Stop the Database Console

[oracle@myserver dbascripts]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release

Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.


Stopping Oracle Enterprise Manager 11g Database Control ...

 ...  Stopped.

[oracle@myserver dbascripts]$

STEP 4: Reset the SYSMAN-account

[oracle@myserver log]$ sqlplus "/ as sysdba"
SQL*Plus: Release - Production on Tue Feb 12 09:51:59 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> alter user sysman identified by <new_password> account unlock;
User altered.

STEP 5: Reset the SYSMAN-account in the “targets.xml”-file ($ORACLE_HOME/myserver.mydomain _mySID/sysman/emd) and modify the “Username”-Property and the “Password”-property as follows:

[oracle@myserver emd]$ pwd
[oracle@myserver emd]$ cp targets.xml targets.xml.ori
[oracle@myserver emd]$ vi targets.xml

<Property NAME="password" VALUE="<new_password>" ENCRYPTED="FALSE"/>

STEP 6: Reset the SYSMAN-account in the “”-file ($ORACLE_HOME/myserver.my_domain_mySID/sysman.config) and modify the “oracle.sysman.eml.mntr.emdRepPwd”-parameter and the “oracle.sysman.eml.mntr.emdRepPwdEncrypted” in the following way:

[oracle@myserver config]$ pwd
[oracle@myserver config]$ cp
[oracle@myserver config]$ vi



STEP 7: Start the Database Console

[oracle@myserver dbascripts]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting Oracle Enterprise Manager 11g Database Control ...... started.
Logs are generated in directory


STEP 8: Test


Get every new post delivered to your Inbox.

Join 53 other followers