Very large subpartitions after table redefinition I'm trying to redefine a table to be partitioned. The original table has about 1.5M rows and about 150MB size.

select segment_type, segment_name, bytes / 1014 / 1024 as SIZE_MB from user_segments where segment_type = 'TABLE' and segment_name = 'WERTEVAR';

segment_type | segment_name | SIZE_MB
TABLE          WERTEVAR       153.543.......


What I did:

CREATE TABLE WERTEVAR_TMP(
  ...column definitions
)
TABLESPACE "USERS"
PARTITION BY RANGE (WVDATZEIT) INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY HASH (WVTEIL, WVMASCHINE) SUBPARTITIONS 8
(
    PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000',     'DD-MM-YYYY'))
);

BEGIN
    DBMS_REDEFINITION.start_redef_table(
        uname         =&gt; 'CHYSTAT_STAGING_MONITORING',
        orig_table    =&gt; 'WERTEVAR',
        int_table     =&gt; 'WERTEVAR_TMP',
        options_flag  =&gt; DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

SET SERVEROUTPUT ON
DECLARE
    l_errors  NUMBER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname            =&gt; 'CHYSTAT_STAGING_MONITORING',
        orig_table       =&gt; 'WERTEVAR',
        int_table        =&gt; 'WERTEVAR_TMP',
        copy_indexes     =&gt; DBMS_REDEFINITION.CONS_ORIG_PARAMS,
        copy_triggers    =&gt; TRUE,
        copy_constraints =&gt; TRUE,
        copy_privileges  =&gt; TRUE,
        ignore_errors    =&gt; FALSE,
        num_errors       =&gt; l_errors,
        copy_statistics  =&gt; FALSE,
        copy_mvlog       =&gt; FALSE
    );

    DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      =&gt; 'CHYSTAT_STAGING_MONITORING',
    orig_table =&gt; 'WERTEVAR',
    int_table  =&gt; 'WERTEVAR_TMP');
END;

DROP TABLE WERTEVAR_TMP;


Now when I look at the table size, it gets HUGE: about 23GB!!

select segment_type, segment_name, sum(bytes) / 1014 / 1024 as SIZE_MB from user_segments where segment_name = 'WERTEVAR' group by segment_type, segment_name;

segment_type       | segment_name | SIZE_MB
TABLE SUBPARTITION   WERTEVAR       23525.743.......


Where I can see that every single subpartition has exactly 8M. Did I something wrong? Are those subpartitions supposed to be large like this?

I can also see that those subpartitions have set:


PCT_FREE       = 10
INITIAL_EXTENT = 8388608
NEXT_EXTENT    = 1048576


I would change that INITIAL_EXTENT AND NEXT_EXTENT to be two orders of magnitude smaller. Something like 83886 and 10485. But I don't know if that is ok, or totally useless. 

I would appreciate any hint.
Yeah, it is recurring problem.Starting with 11.2.0.2, the default segment size of partitions was increased from 64K to 8M. This means, even if your partition is empty, it still consumes 8 MB space right after its creation.

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

You should specify the parameters you mentinoned when you create the new table, for example: INITIAL EXTENT 65536 NEXT EXTENT 65536

Alternatively you could set a hidden parameter, so the database reverts to the old behaviour, session or database level:

alter session set "_partition_large_extents" = false;
alter system set "_partition_large_extents" = false;


But I would suggest specifying the extent sizes manually over modifying a hidden parameter.