I have a large reporting warehouse built in a relational format, with a large number of parent-child dependencies.

We are running: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

In order to support partition pruning, to ease the rolloff of historical data, and to accommodate straggling child records when parent records are aged off, I developed a complex partitioning plan involving range (date) partitioning and range (integer) subpartitioning. Supporting this structure without moving data around requires us to issue ALTER TABLE SPLIT PARTITION / SUBPARTITION statements quite frequently.

A side-effect of this complex partitioning plan is that we end up with a lot of subpartitions with zero rows.

I just discovered today that Oracle is creating segments for most empty subpartitions created by the ALTER TABLE SPLIT PARTITION command, and this is consuming an unexpectedly large amount of space in the database (81 GB / 96 GB for one table). Truncating the affected subpartition does not seem to eliminate these empty segments.

What can be done (preferably) to prevent these segments from being created, or (alternatively) force Oracle to drop the empty segment when the subpartition is empty?

This behavior seems to occur in both partitions and subpartitions - here is a sample based on partitions:

When this table is initially created:
create table test (
part_key varchar2(1)
)
partition by range (part_key) (
partition p1 values less than (MAXVALUE)
);

No segments are created (due to delayed segment creation):
select * from user_segments where segment_name = 'TEST';

no rows selected.

And splitting the partition does not change that:
alter table test split partition P1 at ('Y') into (partition p1_a, partition p1_b);
select * from user_segments where segment_name = 'TEST';

Table altered.
no rows selected.

Starting again with the same table and inserting a row:
drop table test;
create table test (
part_key varchar2(1)
)
partition by range (part_key) (
partition p1 values less than (MAXVALUE)
);

insert into test values ('Z');
commit;

As expected produces one segment:
select substr(partition_name,1,10) partition_name, count(*) cnt, sum(bytes) bytes from user_segments where segment_name = 'TEST' group by partition_name;

PARTITION_NAME CNT BYTES
-------------- ---------- ----------
P1 1 8388608
1 row selected.

Splitting the existing partition below the lowest value (to add a new, lower, empty partition), however, produces a new, empty segment:
alter table test split partition P1 at ('Y') into (partition p1_a, partition p1_b);
select substr(partition_name,1,10) partition_name, count(*) from user_segments where segment_name = 'TEST' group by partition_name;

PARTITION_NAME CNT BYTES
-------------- ---------- ----------
P1_B 1 8388608
P1_A 1 8388608

2 rows selected.

Triuncating the partition does not eliminate the extra segment:
alter table test truncate partition p1_a;
select substr(partition_name,1,10) partition_name, count(*) cnt, sum(bytes) bytes from user_segments where segment_name = 'TEST' group by partition_name;

Table truncated.

PARTITION_NAME CNT BYTES
-------------- ---------- ----------
P1_B 1 8388608
P1_A 1 8388608

2 rows selected.

SQL> alter table test truncate partition p1_a DROP ALL STORAGE;

Table truncated.

SQL> select substr(partition_name,1,10) partition_name, count(*) cnt, sum(bytes) bytes
  2  from user_segments
  3  where segment_name = 'TEST' group by partition_name;

PARTITION_        CNT      BYTES
---------- ---------- ----------
P1_B                1    8388608