-
Hi:
In one of our production database has storage problem.
Tablespace was created with intial 500M next 2M, due to the next extent
size we have so many extents.
I know one of the solution for this
1)get the tablename which are in this tablespace.
2)export those tables.
3)take the tablespace offline.
4)drop the tablespace
5)create a new tablespace with new storage parameter
6)import those tables.
If I change the next extent then it will affect when object need more space
but it will not change the used extent.
Is there any better way to change the storage parameter?
How about TRASPORT_TABLESPACE? will it work?
Let me know.
Thanks.
-
-
Hi:
I have a problem with tablespace storage. next extent is 1M so I have somany extents.
In order to reduce the number of extent and improve the perf. I need to change my
next extent size. I know I can change the new extent which will be effected only for
new next extent but no the exist one. Objects in this tablespace use the default parameter
from tablespace.
As you suggested, recreate a new tablespace with new storage parameter then move
the tables to this new tablespace one-by-one but if I have 50 tables then this process
will take more time. Also, when I move the table what happened to my extents?
extent are going to be recreated as new tablespace storage parameter?
Once I move all the tables then drop the old tablespace and rename the new
tablespace to old tablespace name.
Is there any easier way to do this because I have more then 400 objects and
more then 50 tablespace. Out 50 tablespace I have problem with about 30 tablespace.
TRANSPORT_TABLESPACE will it work?
Thanks.
-
Are you worried about the "extents" of the tablespace? A tablespace doesn't work like a table. If your tablespace extends, you don't have multiple extents, your datafile just gets bigger. Having a NEXT 1M parameter for the autoextend is not a problem.
-
Hi
If you know any thing about this, please let me know.
Thanks.
-
Hi Jeff:
If I don't specify the storage parameter for table then it will take tablespace's storage parameter
as default storage parameter. Once the intial extents fills up then it will take next extent (next extent size is 1M). So, I am having so much extents in my production database. As far as I know
having a so many extent can impact on performance.
So, I want to change the next extent size big enough so I wan't have so many extents.
Also, I want to reduce the number of extents which I have at this point.
If I am going to use tablespace's storage parameter as default then I need to
change the next extent size. Otherwise I have to create an object with it's own
storage parameter to overwrite tablespace storage parameter.
Let me know.
Thanks for your help.
-
To change the default storage parameter for a tablespace:
alter tablespace xyz default storage (initial n next n ...)
However, this will not change any existing structures in the database. There, you will have to either export/import or move them.
-
Jeff:
Thanks.
How TRANSPORT_TABLESPACE will work?
