Top 60 Oracle Blogs

Recent comments

February 2020

Join Elimination bug

It is possible to take subquery factoring (common table expressions / CTEs) too far. The most important purpose of factoring is to make a complex query easier to understand – especially if you can identify a messy piece of text that is used in more than one part of the query – but I have seen a couple of patterns appearing that make the SQL harder to read.

XS$NULL - Can we login to it and does it really have no privileges?

I have read on line about XS$NULL over the years and particularly the documentation that states that it has no privileges. The documentation states the following: An internal account that represents the absence of a user in a session. Because....[Read More]

Posted by Pete On 17/02/20 At 01:09 PM

MobaXterm 20.0 and KeePass 2.44

And in other news about things I’ve missed recently…

MobaXterm 20.0 was released a couple of days ago. It looks like they’ve switched across to the yearly naming like many other companies. </p />

    	  	<div class=

Video : Multitenant : Online Move of Datafiles in CDBs and PDBs

Today’s video is a quick look at online datafile moves in container databases (CDBs) and pluggable databases (PDBs).

If you’ve used this functionality in a non-CDB database, it’s going to look familiar, with a PDB-specific gotcha.

These articles discus moving and renaming files.


By Franck Pachot

Those parameters, SQLNET.EXPIRE_TIME in sqlnet.ora and ENABLE=BROKEN in a connection description exist for a long time but may have changed in behavior. They are both related to detecting dead TCP connections with keep-alive probes. The former from the server, and the latter from the client.

Char problems

The semantics of comparing character columns of different types can lead to some confusion, so before I get into the main body of this note here’s a little test based on a table with one row:

create table t1(c2 char(2), c3 char(3), vc2 varchar2(2), vc3 varchar2(3));

insert into t1 values ('XX','XX','XX','XX');

select count(*) c2_c3   from t1 where c2 = c3;
select count(*) c2_vc3  from t1 where c2 = vc3;
select count(*) c3_vc2  from t1 where c3 = vc2;
select count(*) c3_vc3  from t1 where c3 = vc3;

I’ve inserted one row, using the same value for every single column; then I’ve been counting the row(s) where various pairs of columns match. Which (if any) of the four queries return the value 1 and which (if any) return the value zero ?

Oracle Database 20c : Cloud Preview, Docs and Desupport

A little while ago Dominic Giles tweeted about the release of an Oracle Database 20c preview on Oracle Cloud and the Oracle Database 20c documentation. Some lucky people have already deployed the 20c preview. </p />

    	  	<div class=

Printing all table preferences affecting dbms_stats.gather_table_stats

Oracle 11g introduced the abilty to control the behaviour of the dbms_stats package by setting preferences on the database, schema, and table level. These affect the way dbms_stats goes about doing its work. This feature has been extensively documented, I found the post by Maria Colgan exceptionally good at explaining the mechanism.

How can Docker help a MariaDB cluster for Disaster/Recovery

Mistakes or accidental data deletions can sometimes happen on a productive MariaDB Galera Cluster and this can be disastrous.
There are so many cases I have heard by customers and hereafter are some of the most common:
– dropping one column of a table
– dropping one table
– updating a big table without a where clause
What if it was possible to restore online a subset of data without downtime?

Restoring & recovering using either mysqldump or mariabackup is not satisfying when you have just to recover a subset of data.
In both case, there will be a downtime and it will be a very long process.
– with mysqldump: you will have first to stop the application, restore the latest Full backup and then apply all the binary logs until the guilty command.

ORA-12850: Could not allocate slaves on all specified instances error and a workaround for my scripts

My ASH wait chains scripts started returning the ORA-12850 error on Oracle 19c RAC installations:
SQL> @ash/dash_wait_chains program2||event2 1=1 sysdate-1 sysdate -- Display ASH Wait Chain Signatures script v0.5 BETA by Tanel Poder ( ) , REPLACE(SYS_CONNECT_BY_PATH(program2||event2, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ... ERROR at line 63: ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1 allocated While this error message sounds like a resource shortage issue, it is actually often due to the remote instance(s) not being able to reproduce the exact same execution plan as in the query coordinator instance.