DB Unit Testing with SQL Developer: REF CURSORs still having problems [UPDATE!]

Database Unit Testing is underestimated by many (too many!) IT companies. Personally, in my long career, I’ve never seen it properly implemented. In the past there might have been lack of right tools for testing database objects but nowadays the market offers a good choice of software able to fill this gap. Because of my deep love for Oracle products (and I’ve never kept it secret) I’ve recently decided to spend some time analyzing Oracle SQL Developer and give it a shot to see how good is the unit testing support. This post refers to SQL Developer version 18.1 connected to an Oracle database release 12.1.0.2.

The version 18.1, latest release at the moment, has a ton of interesting features but the purpose of this post is to just focus on the db unit testing part, specifically on REF CURSORS. I really like how Oracle implemented this testing module in SQL Developer: it’s clear, it has a guided wizard which makes it easy to create tests, it supports shared repositories and allows to save libraries for reusing code. The only problem I had in the past releases is that REF CURSORS were not properly supported. I was hoping to see an improvement in this latest update but unfortunately it’s not the case and I will show in this post what I mean.

Read More…

How to use a custom index on primary key constraints

I admit it, I had a completely wrong belief when thinking about primary key constraints and their related index. When creating a PK on a table, Oracle automatically associates a unique index to it

The index name is internally generated and associated to the constraint. Fact is that, as I discovered some time ago, it’s not the index that enforces the uniqueness but the constraint itself. The constraint uses the underlying index to go faster but the index itself does not enforce anything. But you can’t really blame me for thinking differently in the beginning, not after seeing that Oracle creates a unique index for the pk!

Read More…

Oracle Virtual Private Database is not so private!

Virtual Private Database (Oracle VPD) was introduced by Oracle in release 8i. It’s a security feature that provides access restriction at row/column level for privacy and regulatory compliance. It allows sophisticated logic to be applied through the use of custom functions in which rules can be written for fine-grained data access. If you want to know more about how it works check the official documentation.
The point of this post is that the column-level security of Oracle VPD can be easily tricked! It all started when I decided to check the new Redaction feature released with Oracle 12c. This is somehow similar to VPD but it works at a different level. I came across a very interesting article from David Litchfield that in his article demonstrates how this feature is broken and not safe at all. Well, starting from there I made my own similar investigations on VPD and got some really surprising results.

Read More…

How locks work with move partition ONLINE on Oracle 12c

With the release 12c Oracle introduced the “online partition movement” making the operation a non-blocking DDL command. This means that it’s possible to run any DML on a partition while the same is being “moved” using the new ONLINE option.
Let’s see how that works on our usual test table populated with 1 million rows in the first partition

We can now take a look at how move partition works normally when not running it in ONLINE mode.
Let’s execute

Read More…

Partial indexing for unique indexes on Oracle 12c

With release 12c Oracle has introduced partial indexing, the possibility to define partial local indexes on partitioned table. This is an interesting feature that allows to save some space getting rid of index segments for old partitions. Let’s quickly see how this works.
First of all, when creating a partitioned table, the INDEXING property can be specified to define if we want or not to create the indexes for that partition (default value is ON unless a different default is set at table level)

This property can also be changed in a second time issuing a simple alter command

The local indexes need to be created with the option INDEXING PARTIAL otherwise they will not be affected by the INDEXING ON/OFF property set at partition/subpartition level.
There is a restriction though: local unique indexes cannot be created as partial so it’s not possible to drop partitions of a unique index. Well, this is at least what Oracle says but let’s go on.

Read More…

How the GLOBAL_STATS statistic affects the Oracle CBO

Some time ago I came across a query that was using dynamic sampling on a partitioned table even though statistics were present at global level. After some time spent investigating this issue I discovered an interesting but unexpected behaviour of the Oracle CBO when dealing with the GLOBAL_STATS statistic (observed on Oracle RDBMS ver. 11.2.0.2.0, 11.2.0.3.0, 12.1.0.2.0).
Let me go through a simple demo so I can explain what I’ve found.

Read More…