Database DataWarehousing (MOSC)

MOSC Banner

Is there a way to make extent sizes smaller (64 K) while compressing sub-partitions of a table that

edited Apr 18, 2016 1:12PM in Database DataWarehousing (MOSC) 4 commentsAnswered ✓

In my 11g R2 database, I have a large table that is interval-partitioned and hash-subpartitioned:

CREATE TABLE pathsens (...)

PARTITIONED BY RANGE (valuationuid) INTERVAL (1)

SUBPARTITION BY HASH (pathuid) SUBPARTITIONES 16

(

  PARTITION dummy VALUES LESS THAN (1)

)

STORAGE(INITIAL 8M next 8m);

The initial size 8M is too big. It takes ~4 M rows to fill the 1st extent of all 16 sub-partitions. In many cases, partitions have less than 4 M rows. Therefore, when I try to compress the table using ...

ALTER TABLE pathsens MOVE SUBPARTITION SYS_SUBP119285 PARALLEL 16 COMPRESS FOR QUERY HIGH;

... I do not get any disk space back because the new extents are still 8M in size and there are still 16 of them.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center