How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size

However, it’s also goo to know the historical size.  The below query will give you this from the Oracle Enterprise Manager (OEM) repository:


How to obtain the Database Current, Total and Max Size

From time to time, it’s useful to know the total current size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 *  (more...)

Delete Old SBT_TAPE Backups that are No Longer Available in RMAN

When in the situation of having ‘SBT_TAPE’ backups, that are still known by the catalog or control file, but the device type ‘SBT_TAPE’ is no longer available.  If you try to delete the backups you get the following error message:

RMAN> list backup summary;

List of Backups
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
11208 B 0 A SBT_TAPE  (more...)

RMAN-06403: could not obtain a fully authorized session

An automated process that refreshes a Development database from Production by doing a RMAN duplicate failed with ‘RMAN-06403: could not obtain a fully authorized session’:

Starting Duplicate Db at 25-JUL-2017 17:44
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/25/2017 17:44:46
RMAN-05501: aborting duplication of target database
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101:  (more...)

Identity Columns Compared

It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a (more...)

PeopleSoft and Adaptive Query Optimization in Oracle 12c

Adaptive Query Optimization is a significant feature in Oracle 12c. Oracle has made lots of information available on the subject.(See
Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics…There are two distinct aspects in Adaptive Query Optimization: adaptive plans, which focuses on improving the execution of a query and adaptive (more...)

Oracle SQL Strip Quotes

Somebody wanted to know how to strip double quotes from strings. Obviously, they’re playing with the DBMS_METADATA package. It’s quite simple, the TRIM function does it, like this:

SELECT TRIM(BOTH '"' FROM '"Hello World!"') AS "Message"
FROM   dual;

It will print:

Hello World!

As always, I hope this helps those looking for a solution.

Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.


Oracle Database are officially supported on Docker

Since yesterday, Tuesday 20th December 2016, the Oracle Database and later are officially supported on Docker :-)

The corresponding My Oracle Support Note 2216342.1 can be found here:

In the GitHub Repository of Oracle the related README is also updated to reflect the support for Oracle Database and later on Docker.
All required files to build such a supported Oracle Database Instance on Docker can be found here:

Book on Oracle 12c New features

A Book on Oracle 12c New features for administrators

Hi All,

It’s our pleasure to announce the availability of our first book – OCP 12c upgrade 1Z0-060 Exam Guide













The book covers the new features of the Oracle Database 12c for the DBAs from the OCP 12c upgrade exam standpoint. Book is available WW in kindle format as well as paperback.

Link to (more...)

Dallas Oracle User Group Performance & 12.2 New Features Technical Day

Just letting people in DFW area know that I’m speaking at the DOUG Performance & Tuning and 12.2 New Features Technical Day!


  • Thursday 20 October 2016 9:30am-5:30pm


  • Courtyard & TownePlace Suites DFW Airport North/Grapevine, TX
    2200 Bass Pro Court|Grapevine, TX 76051

Speakers (Seven Oracle ACE Directors!):

  • Jim Czuprynski

  • Charles Kim

  • Cary Millsap

  • Dan Morgan

  • Kerry Osborne

  • Tanel Poder

  • Nitin Vengurlekar


  • I’ll speak about In-Memory Processing for Databases where I plan (more...)

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

Oracle 12c Learning Series: Automatic Table Recovery Using RMAN

Table recovery was possible in earlier release as well. Until previous release, if we wanted to recover a table, we had following options

  • Database point in time recovery (DBPITR)
  • Tablespace point in time recovery (TSPITR)
  • Flashback technology

In Oracle 12c, RMAN has been enhanced to perform recovery of table. We have a new command in RMAN which automates complete process of recovering the table. New process does not affect the existing objects in the database (more...)

Oracle 12c Learning Series: In-database Archiving and Temporal Validity

One of the major challenge faced by an Oracle DBA is – how to effectively deal with historical data? Today, if we consider database tables for an enterprise, data in the table goes back several years and most of the data in the table is inactive. Challenge remains as to how to archive this data and make our query run efficiently.

If we choose to archive old data outside of database on a tape, cost (more...)

Oracle 12c Learning Series: Automatic Data Optimization – ADO

I am starting Oracle 12c learning series, where I am planning to publish multiple articles on Oracle 12c new features. I hope these articles will be helpful to DBAs aspiring for OCP 12c certification and also others who are planning to implement these features in there databases.

This is a very long post about ADO – Automatic Data Optimization, which is one of the ILM strategy to manage aging data. So please be patient while reading this article. This article provides end-to-end details (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

#DB12c feature – Secure External Procedures with DBMS_CREDENTIAL

Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and links it with a PL/SQL library object. Whenever an application calls an external procedure, the extproc process authenticates the connection before loading the shared library. The DBMS_CREDENTIAL package is … Continue reading

Temporal validity, multiple end dates

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)

APEX 5.0 Beyond basics

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the second tutorial is presented.

This tutorial helps you step by step through the process of migrating an application to the Universal Theme of Oracle Application Express 5.0. In this tutorial we talk about (more...)