Home » RDBMS Server » Server Administration » Changing a subpartition initial extent (10.2.0.4 on Solaris)
Changing a subpartition initial extent [message #394117] Wed, 25 March 2009 14:25 Go to next message
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 #394118 is a reply to message #394117] Wed, 25 March 2009 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Initial extend size cannot be changed.
Why don't you move to LMT (locally managed tablespace) and no more care about extends size?

Regards
Michel
Re: Changing a subpartition initial extent [message #394121 is a reply to message #394117] Wed, 25 March 2009 14:33 Go to previous messageGo to next message
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 #394125 is a reply to message #394121] Wed, 25 March 2009 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use LMT uniform size but LMT auto/system allocate.

Precreate the tables with the initial extend you want then import will just fill them.

Regards
Michel
Re: Changing a subpartition initial extent [message #394128 is a reply to message #394117] Wed, 25 March 2009 15:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> the initial extents are not 'adjusting' themselves to the new tablespace in test.
So why does the INITIAL size matter?
I challenge you to provide a reproducible benchmark that shows that INITIAL size actually matter & results in a measurable performance difference.

If you can not measure any change in performance, why do you care what the actual INITIAL value may be.
Re: Changing a subpartition initial extent [message #394130 is a reply to message #394125] Wed, 25 March 2009 15:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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...
Re: Changing a subpartition initial extent [message #394133 is a reply to message #394117] Wed, 25 March 2009 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
WHOA!

AUTO vs. MANUAL is a whole different can of worms.

What I know to be true, is that if all other factors are the same between 2 identical system & the ONLY difference is the size of INITIAL; you won't be able to measure any performance difference.

Good Luck with your gremlin hunt!
Re: Changing a subpartition initial extent [message #394153 is a reply to message #394117] Wed, 25 March 2009 19:59 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I get the structure from a production DB and I am importing them ( using datapump ) to development.
If you pre-create the objects & then use IGNORE=YES, you'll end up with objects customized as you desire.

[Updated on: Wed, 25 March 2009 19:59]

Report message to a moderator

Previous Topic: Active Table at Current DB Session
Next Topic: DBMS_STATS.GATHER_SCHEMA_STATS
Goto Forum:
  


Current Time: Fri Dec 09 23:23:56 CST 2016

Total time taken to generate the page: 0.08529 seconds