Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

with tst as (
  select 'qwe word1 asd ...............' s from dual
  union all
  select 'qwe word1 asd word2 .........' s from dual
  union all
  select 'qwe word1 asd word2 zxc word3' s from dual
  union all
  select 'qwe word2 asd word1 zxc word4' s from dual
  ,regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') subst
  ,case when regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$') then 'matched' end tst2
from tst
where 1=1
--and regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$')

How To Enable DDL Logging in the Database

If for whatever reason, you are required to log DDL, for example, I need to know why the LAST_DDL_TIME of a table was getting updated, you can do this from Oracle 11g.

To enable:

SQL> show parameter ENABLE_DDL_LOGGING

------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE


System altered.

SQL> show parameter ENABLE_DDL_LOGGING

------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

To disable:

SQL> show parameter ENABLE_DDL_LOGGING

------------------------------------ -----------  (more...)

Optimistic Locking 6: which ORA_ROWSCN?

Continuing my series on using ORA_ROWSCN to achieve optimistic locking, let’s zoom in on ORA_ROWSCN itself. I've been asking myself this question:
  • ORA_ROWSCN can be maintained at the block level or the row level. What is the difference and which one should we use?

The best Oracle technology week ever – Part 1: Helsinki and Stockholm

You remember my last blogpost describing how my #europeTour would be like:
#orclapex Europe tour 

It was a week with as little sleep as possible. Reasons:
 - traveling
 - just was to excited
 - to much party

It all started in Helsinki with the first Oracle APEX day in Finland.
I reached Helsinki with Richard Rieb around 10 o'clock in the evening. It was dark and cold, our taxi driver spoke 5 words to (more...)

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...)

Install Cassandra on Fedora

It was quite interesting to discover that DataStax no longer provides the DataStax Community version of Apache Cassandra or the DataStax Distribution of Apache Cassandra. Needless to say, I was quite disappointed because it means folks will get less opportunity to learn how to use Cassandra because it makes it more difficult for beginning developers.

I spent a good hour sorting through what was available and then figuring out the real requirements.

After installing and (more...)

Log Buffer #517: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers Oracle, SQL Server and MySQL.


Protecting Financial Data with Oracle WebCenter and Adobe LiveCycle

Oracle Forms 12c Error after Database change

The Future of Content Management: Oracle Content & Experience Cloud

Today Oracle released a very large „monster“ Upgrade. This July 2017 Update includes the first time the new RU „Release Upgrade“ and RUR „Release Update Revision“ Patches.

Cloud Ward: Who Will Win the Battle (more...)

Optimistic ORA_ROWSCN 2: the SCN

In my previous post, I covered lost updates. Before saying how ORA_ROWSCN can help avoid them, I need to talk about the SCN. It's hard to be simple and correct about such a fundamental element of the Oracle database, but I'll try.

Role of # in SQL*Plus

| Jul 13, 2017
The # character is for commenting in SQL*Plus, right?

The character # has been mostly used for comments in many languages, such as shell scripts and python. Interestingly # is legal syntax in SQL scripting as well; but is it considered a comment? The answer is no; it's not. The purpose of # in SQL scripts is very different. and you should be very careful using it.

Entering # tells SQL*Plus to temporarily pauses what (more...)

Unpivot mit Oracle

Vor einigen Wochen hat Jonathan Lewis einen Artikel zum Verhalten von UNPIVOT in Oracle veröffentlicht. Darin zeigt er mit Hilfe eines CBO-Traces, dass die UNPIVOT-Operation intern in eine Kombination mehrerer über UNION ALL verknüpfter Queries umgewandelt wird. Interessant ist dabei, dass die Zahl der Blockzugriffe (Buffers-Angabe in den rowsource-Statistiken) nicht höher ist als bei einem einzelnen Full Table Scan. Jonathan Lewis vermutet, dass der zugehörige code path dafür sorgt, dass die Blöcke im Cache Batch-weise (more...)

Removing Outliers using stddev()

A colleague asked me about removing outliers from reports and I knew just the function to give a go.

Some time ago I had to prove that some data we had conformed to a normal distribution. Remember those from high school?
A normal distrubition (bell curve)
That request was about 2007 and I ended up using stddev() and lpad() to produce a vertical shaped bell curve in SQL*Plus. That was pretty cool.

As for removing (more...)

OGh Tech Experience 2017 – recap

On June 15th and 16th 2017 the very first OGh Tech Experience was held. This 2-day conference was a new combination of the DBA Days and Fusion Middleware Tech Experience that were held in previous years. To summarize: OGh hit bullseye. It was two days packed with excellent in-depth technical sessions, good customer experiences and great networking opportunities.

The venue was well chosen. De Rijtuigenloods in Amersfoort is a former maintenance building of the Dutch (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.

Write (Even) Less with More – VALIDATE_CONVERSION

I wrote the post Write Less with More – Part 8 – PL/SQL in the WITH Clause in November 2015, when the latest released Oracle version was 12.1.
In that post I explained about PL/SQL in the WITH Clause – a new 12.1 feature – and demonstrated it using the following example:


Since then Oracle 12.2 was released, and introduced a new feature that enables solving this task in a simpler way – the VALIDATE_CONVERSION function. This function gets an expression and a data type, and returns 1 if the expression can be converted to the data type and 0 if not.
Using the same setup from the original post, the requested query becomes as simple as:

> select *
  from   people
  where  general_info is not null
  and    validate_conversion(general_info as date, 'dd/mm/yyyy') = 1;

---------- ---------- --------------- --------------------
       102 Paul       McCartney       18/6/1942
       202 Ella       Fitzgerald      15/6/1996
       203 Etta       James           20/1/2012

In addition to introducing the new VALIDATE_CONVERSION function, the older CAST and some of the TO_* conversion functions have been enhanced in Oracle 12.2 and include a DEFAULT ON CONVERSION ERROR clause, so when data type conversion fails we can get some default value instead of an error.

> select p.person_id,
         to_date(p.general_info default null on conversion error, 'dd/mm/yyyy') my_date
  from   people p;

---------- ---------- --------------- ----------
       101 John       Lennon
       102 Paul       McCartney       18/06/1942
       103 Ringo      Starr
       104 George     Harisson
       201 Louis      Armstrong
       202 Ella       Fitzgerald      15/06/1996
       203 Etta       James           20/01/2012
       317 Julie      Andrews

8 rows selected.

The post Write (Even) Less with More – VALIDATE_CONVERSION appeared first on @DBoriented.

PL/SQL in SQL in View in SQL in PL/SQL

I presented “Write Less (Code) With More (Oracle 12c New Features)” yesterday at OGh Tech Experience 2017.
One of the features I talked about was PL/SQL in the WITH Clause. One of the restrictions of this feature is that you cannot embed a static SQL query, that contains PL/SQL in the WITH clause, in PL/SQL (see the section PL/SQL in SQL in PL/SQL in this post).
I was asked, regarding this restriction, if it’s (more...)

Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password (more...)


The RETURNING INTO clause is one of my favorite PL/SQL features. It allows to write less code, improves readability and reduces context switches between PL/SQL and SQL.
In this post I’d like to highlight some less-known characteristics of the RETURNING INTO clause and emphasize differences that exist when it is used in different DML statements.

Supported Statements

The RETURNING INTO clause is supported by the UPDATE, DELETE, and single-table single-row (“values-based”) INSERT statements.
It is (more...)

SQL validation during PL/SQL compilation

A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:

During PL/SQL compilation Oracle checks static SQL using only:

  1. Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are (more...)

Adding a Column with a Default Value and a Constraint

The Constraint Optimization series:

In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a (more...)