Top 60 Oracle Blogs

Recent comments


Join View

It’s strange how one thing leads to another when you’re trying to check some silly little detail. This morning I wanted to find a note I’d written about the merge command and “stable sets”, and got to a draft about updatable join views that I’d started in 2016 in response to a question on OTN (as it was at the time) and finally led to a model that I’d written in 2008 showing that the manuals were wrong.

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions and NULLs

While researching interval partitioning in Oracle 19c I came across a phenomenon I had already solved a long time ago but then forgot about. This time I decided to write about it so I don’t have to spend 5 minutes rediscovering the wheel. As always, if you are following along, make sure you are appropriately licensed for this feature

The Situation

Consider the following example:

Oracle Connection Manager (CMAN) quick reporting script

Here is a script I use to parse Connection Manager “show service”

List services registered by instance

CMCTL can show the services in a long list, but I want something quick like this, with one line per service, and one column per endpoint that registers to CMAN:

script output, a bit obfuscated

The following script


What happens when you execute dbms_stats.gather_system_stats() with the ‘Exadata’ option ?

Here’s what my system stats look like ( test results) after doing so. (The code to generate the two different versions is at the end of the note).

Azure VMs with Oracle- Next Steps

Microsoft has done a great job of documenting how to create a VM with the appropriate VM image and Oracle version, then how to log in, startup the listener and create the database.  I just have some enhancements I’d like to make to it, hoping to help it move up one level.

I Bequeath to You

All instructions provided by the Microsoft documentation show how to connect to the database using a bequeath, (BEQ) connection.  This is done by the following command, using SQL Plus, (or similar):

sqlplus / as sysdba

It bypasses the need for a connection string, only requiring the SID to be set at the environment level:

export ORACLE_SID=

This information, for Oracle DBAs, is often gathered from a running database server executing the following command:

How 19c Auto Indexes are named?

As a SQL_ID-like base 32 hash on table owner, name, column list

The indexes created by the 19c Auto Indexing feature have a generated name like: “SYS_AI_gg1ctjpjv92d5”. I don’t like to rely on the names: there’s an AUTO column in DBA_INDEXES to flag the indexes created automatically.

But, one thing is very nice: the name is not random. The same index (i.e on same table and columns) will always have the same name. Even when dropped and re-created. Even when created in a different database. This is very nice to follow them (like quickly searching in my e-mails and find the same issue encountered in another place). Like we do with SQL_ID.

Yes, the generation of the name is similar to SQL_ID as it is the result of a 64-bit number from a hash function, displayed in base 32 with alphanumeric characters.

How to drop an index created by Oracle 19c Auto Indexing?

ORA-65532: cannot alter or drop automatically created indexes

Oracle 19c Automatic Indexing is not like the autonomous features that happen without your control. You can decide to enable it (if you are on a platform that allows it) or not, and in report-only or implementation mode.

But when you have enabled it to create new indexes, you are not supposed to revert its effect. What if you want to drop those indexes?


If I want to drop an index that has been created automatically (i.e with the AUTO=’YES’ in DBA_INDEXES) I get the following error:


Here’s a problem (and I think it should be called a bug) that I first came across about 6 years ago, then forgot for a few years until it reappeared some time last year and then again a few days ago. The problem has been around for years (getting on for decades), and the first mention of it that I’ve found is MoS Bug 2891576, created in 2003, referring back to Oracle, The problem still exists in Oracle 19.2 (tested on LiveSQL).

Here’s the problem: assume you have a pair of tables (call them parent and child) with a referential integrity constraint connecting them. If the constraint is enabled and not deferred then the following code may fail, and if you’re really unlucky it may only fail on rare random occasions:

MVCC in Oracle vs. Postgres, and a little no-bloat beauty

MVCC in Oracle vs. PostgreSQL, and a little no-bloat beauty

Databases that are ACID compliant must provide consistency, even when there are concurrent updates.

Let’s take an example:

  • at 12:00 I have 1200$ in my account
  • at 12:00 My banker runs long report to display the accounts balance. This report will scan the ACCOUNT tables for the next 2 minutes
  • at 12:01 an amount of 500$ is transferred to my account
  • at 12:02 the banker’s report has fetched all rows

What balance is displayed in my banker’s report?


Here’s a question to provoke a little thought if you’ve got nothing more entertaining to do on a Sunday evening.  What threats do you think of when you see a statement like the following in (say) an AWR report, or in a query against v$sql ?

update tableX set
        col001 = :1, col002 = :2, col003 = :3, ...
        -- etc. 
        -- the names are supposed to indicate that the statement updates 302 columns
        -- etc.
        col301 = :301, col302 = :302
        pk_col = :303

I’ll be writing up some suggestions tomorrow (Monday, UK BST), possible linking to a few other articles for background reading.


The first three comments have already hit the high points, but I’m going to jot down a few notes anyway.

The first two things that really (should) make an impact are: