Shrink Tablespace

In a comment on my previous post on shrinking tablespaces Jason Bucata and Karsten Spang both reported problems with small objects that didn’t move to the start of the tablespace. This behaviour is inevitable with dictionary managed tablespaces (regardless of the size of the object), but I don’t think it’s likely to happen with locally managed tablespaces if they’ve been defined with uniform extent sizes. Jason’s comment made me realise, though, that I’d overlooked a feature of (more...)

12c Fixed Subquery

It’s been about 8 months since I posted a little note about a “notable change in behaviour” of the optimizer when dealing with subqueries in the where clause that could be used to return a constant, e.g.:

from	t1
where	id between (select 10001 from dual)
	   and     (select 90000 from dual)

There’s been a note at the start of the script ever since saying: Check if this is also true (more...)

Lock Time

Here’s a little detail I was forced to re-learn yesterday; it’s one of those things where it’s easy to say “yes, obviously” AFTER you’ve had it explained so I’m going to start by posing it as a question. Here are two samples of PL/SQL that using locking to handle a simple synchronisation mechanism; one uses a table as an object that can be locked, the other uses Oracle’s dbms_lock package. I’ve posted the code for each fragment, and a (more...)

Partitioned Clusters

In case you hadn’t noticed it, partitioning has finally reached clusters in 12c – specifically They’re limited to hash clusters with range partitioning, but it may be enough to encourage more people to use the technology. Here’s a simple example of the syntax:

create cluster pt_hash_cluster (
        id              number(8,0),
        d_date          date,
        small_vc        varchar2(8),
        padding         varchar2(100)
-- single table
hashkeys 10000
hash is id
size 700
partition by range (d_date) (
        partition  (more...)

Shrink Tablespace

A recent question on the OTN database forum raised the topic of returning free space in a tablespace to the operating system by rebuilding objects to fill the gaps near the start of files and leave the empty space at the ends of files so that the files could be resized downwards.

This isn’t a process that you’re likely to need frequently, but I have written a couple of notes about it, including a (more...)


I’ve often found in my travels that I’ve come up with a (potential) solution to a problem and wanted to test it “right now” – only to run onto the horns of a dilemma. A typical client offers me one of two options:

  • Option 1: test it on the production system – which is generally frowned on, and sometimes I can’t even get access to the production system anyway.
  • Option 2: test it on something that looks (more...)

Unusual Deadlock

Prompted by a question on OTN I came up with a strategy for producing an ORA-00060 deadlock that DIDN’T produce a deadlock graph (because there isn’t one) and didn’t get reported in the alert log (at least, not when tested on It’s a situation that shouldn’t arise in a production system because it’s doing the sorts of things that you shouldn’t do in a production system: but possibly if you’re trying (more...)

Quiz Night

I have a table with several indexes on it, and I have two versions of a query that I might run against that table. Examine them carefully, then come up with some plausible reason why it’s possible (with no intervening DDL, DML, stats collection, parameter fiddling etc., etc., etc.) for the second form of the query to be inherently more efficient than the first.

        bit_1, id, small_vc
        bit_1  (more...)

ASSM Truncate.

Here’s one that started off with a tweet from Kevin Closson, heading towards a finish that shows some interesting effects when you truncate large objects that are using ASSM. To demonstrate the problem I’ve set up a tablespace using system allocation of extents and automatic segment space management (ASSM).  It’s the ASSM that causes the problem, but it requires a mixture of circumstances to create a little surprise.

	tablespace test_8k_auto_assm
	datafile	-- OMF
	SIZE 1030M
	autoextend  (more...)

Group By Bug

This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12c.

alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';

       /* optimizer_features_enable('')*/
       trunc (ts,'DD') ts1, sum(fieldb) fieldb
from (
        ts, max(fieldb) fieldb
  from (
  select trunc(sysdate) - 1/24 ts, 1 fieldb from dual
  union all
  select trunc(sysdate) - 2/24 ts, 2 fieldb from dual
  union all
  select trunc(sysdate) - 3/24 ts, 3 fieldb from dual
  union  (more...)