How can you make an oracle table, index or partition 'lose' its storage definition and revert to tablespace defaults...
I have a 'legacy' table in production that was built with storage parameters inherited from the 'consolidate storage' option on the old export utility (exp). It has INITIAL set to 2 GB.
The tablespace has Segment Management set to Auto and Extent Management set to Local with Auto Allocate.
I want to convert the table to partitions, but all partitions get built with INITIAL set to 2 GB.
My solution, so far, is to first MOVE the table with a STORAGE clause with INITIAL set to 10M.
However, I prefer to build all new objects, by using the defaults from the tablespace...
Is there way to do this?
Or is the only option to re-create the object with the INITIAl size set to the same value as the tablespace default?
Thanks in advance...
I don't know of a command to reset these to the defaults.
Note that the default initial for partitions is 8Mb, regardless of the tablespace settings. You can override this for new partitions by changing the table's default attributes.
I'm also struggling to understand what the issue is here. It's easy enough to write a script to move all tables/partitions, setting the initial value as needed. I don't see how re-creating these is better as there's the risk of losing indexes, triggers, grants, etc. in the process.
That said, as you're on 19c there is a workaround of sorts. You can change the partitioning scheme for a table. So you could alter the table to use a different partition method, then back to the one you want. This will pick up the default (8Mb unless you've overridden it) in the process.
For example:
create table t (
c1 int
) partition by range ( c1 ) (
partition p1 values less than ( 10 ) storage ( initial 1k ),
partition p2 values less than ( 20 ) storage ( initial 10k ),
partition p3 values less than ( 30 ) storage ( initial 100k ),
partition p4 values less than ( 40 ) storage ( initial 1000k )
);
select partition_name, initial_extent
from user_tab_partitions
where table_name = 'T';
PARTITION_NAME INITIAL_EXTENT
P1 16384
P2 16384
P3 106496
P4 1024000
alter table t
modify partition by hash ( c1 )
partitions 1;
select partition_name, initial_extent
from user_tab_partitions
where table_name = 'T';
PARTITION_NAME INITIAL_EXTENT
SYS_P45145 8388608
alter table t
modify default attributes
storage ( initial 10m );
alter table t
modify partition by range ( c1 ) (
partition p1 values less than ( 10 ),
partition p2 values less than ( 20 ),
partition p3 values less than ( 30 ),
partition p4 values less than ( 40 )
);
select partition_name, initial_extent
from user_tab_partitions
where table_name = 'T';
PARTITION_NAME INITIAL_EXTENT
P1 10485760
P2 10485760
P3 10485760
P4 10485760 Again, I don't see how this is any better/easier than moving the partitions as needed.