| Changing a subpartition initial extent [message #394117] |
Wed, 25 March 2009 14:25  |
Orna
Messages: 62
Registered: November 2008
|
Member |
|
|
Hi
I have a table with subpartitions and I need to reduce the initial extent of the subpartition without dropping and recreating .
I can do that on a table level and on a partition level using :
alter table xxx move storage (initial 65k);
alter table XXX move partition YYY storage (initial 65k);
But I cannot do a similar thing for subpartition
alter table xxx move subpartition yyy storage (initial 65k);
ORA-14160: this physical attribute may not be specified for a table subpartition
Any way to accpmplish this without dropping and recreating the table ?
This is needed as part of a huge data structure copy .
I get the structure from a production DB and I am importing them ( using datapump ) to development.
I need to "shrink" the tables because the initial extents defined are big and there are so many of them that the copied schema, thoufght empty of data is taking many GB of space.
I managed to "shrink " everything , only subpartitioned tables are still occupying a lot of space
Any advise ?
Thanks
Orna
|
|
|
|
|
|
| Re: Changing a subpartition initial extent [message #394121 is a reply to message #394117] |
Wed, 25 March 2009 14:33  |
Orna
Messages: 62
Registered: November 2008
|
Member |
|
|
I am using LMT in both production and test
but when I export and import ( datapump ) - the initial extents are not 'adjusting' themselves to the new tablespace in test.
So the original tablespace in production have a 100MG initial and next extent and the target tablespace to which I remap during the import has 65k initial/next extent - but nevertheless - the tables are getting created with the old extent sizes of 100MG.
The only way I found to fix this is to actually run alter table commands on the empty tables after import, and that works fine for everything other then subpartitions.
thanks
Orna
[Updated on: Wed, 25 March 2009 14:34] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Changing a subpartition initial extent [message #394130 is a reply to message #394125] |
Wed, 25 March 2009 15:11 |
Orna
Messages: 62
Registered: November 2008
|
Member |
|
|
well , this is exactly what I have in test :
Allocation type SYSTEM and space management type AUTO.
However - in production the management type is MANUAL.
We had severe performance problems with our I/O intensive Real time systems when we had them in AUTO and we had to switch it back to manual to improve performance.
So, having it AUTO in the target tablespace is not enough, looks like - the extent size are inherited over the import
|
|
|
|
| Re: Changing a subpartition initial extent [message #394132 is a reply to message #394128] |
Wed, 25 March 2009 15:13 |
Orna
Messages: 62
Registered: November 2008
|
Member |
|
|
It matters in my case.
I need to create a "mini" test schema that will hold much less data then production and actually in most cases will hold no data at all in 90% of that table, so the only space it will ever occupy is that initial extent...
However - all the structures needs to be there for the packages to be compiled.
Because of the big initial extent , each mini schema I create is very large, and since we need multiple of these - we are in trouble...
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
