Oracle Subpartitions – Handling 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         => 'CHYSTAT_STAGING_MONITORING',
        orig_table    => 'WERTEVAR',
        int_table     => 'WERTEVAR_TMP',
        options_flag  => DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

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

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

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      => 'CHYSTAT_STAGING_MONITORING',
    orig_table => 'WERTEVAR',
    int_table  => '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.