Even if its an empty partition, MOVE is the way to do it:
create table t1 (c1 number, c2 varchar2(100))
segment creation immediate
partition by range (c1)
(
partition p1 values less than (10),
partition p2 values less than (20)
);
SQL> select partition_name, initial_extent, next_extent
from user_tab_partitions where table_name = 'T1';
PARTITION_NAME INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1 8388608 1048576
P2 8388608 1048576
Default 8M/1M. Now move the partition:
SQL> alter table t1 move partition p1 storage (initial 65536 next 65536);
Table altered.
SQL> select partition_name, initial_extent, next_extent from
user_tab_partitions where table_name = 'T1';
PARTITION_NAME INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1 65536 65536
P2 8388608 1048576
Modifying default attributes will have its effect only on partitions created after setting it:
SQL> alter table t1 modify default attributes storage ( initial 1M next 1M );
Table altered.
SQL> alter table t1 add partition p3 values less than (30);
Table altered.
SQL> select partition_name, initial_extent, next_extent from
user_tab_partitions where table_name = 'T1';
PARTITION_NAME INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1 65536 65536
P2 8388608 1048576
P3 1048576 1048576
SQL> select owner||'."'||table_name||'"' t from dba_tables where table_name like 'CLI_SWP$%';
T
-----------------------------
AUDSYS."CLI_SWP$18c7c2a9$1$1"
SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";
truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1"
*
FEHLER in Zeile 1:
ORA-55941: DML- und DDL-Vorgänge sind auf Tabelle
"AUDSYS"."CLI_SWP$18c7c2a9$1$1" nicht zulässig
SQL> shutdown immediate;
…
ORACLE-Instanz heruntergefahren.
SQL> startup upgrade;
…
Datenbank geöffnet.
SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";
Tabelle mit TRUNCATE geleert.
SQL> shutdown immediate;
SQL> startup;
Best Answer
Those two methods are very different.
will move all the data blocks into the new tablespace. while it doing so, the table will be locked for DML.
DBMS_REDEFINITION will allow you to create a new table on the desired tablespace and sync the data between them ONLINE. when the sync ends the tables names will be switched. you will have to be careful with DBMS_REDEFINITION as you have to create the new table youself including indexes, constraints, partition, privs, etc.