INITIAL & MINEXTENTS - how to change?
Roy deCarvalho
I know that the storage parameters INITIAL and MINEXTENTS cannot be
altered. Any suggestions on how to go around this restriction? I need to
increase the INITIAL and MINEXTENTS of one table.
Thanks for your help.
Roy
--
Eugene Freydenzon
following:
1. export that table
2. drop it
3. precreate in nessesary configuration
4. import it back with IGNORE=Y.
Note 1: All referencial integrity TO that table should be disabled first
and enabled afterwards (triggers included). Also, I preffer drop all
indexes and recreate them after data has been imported.
Note 2: In case of export and import full scheme (user), "Note 1" should
be ignored (at lest for Oracle 7.16 and higher).
Note 3. For index, just drop and recreate it with new storage
parameters.
If I miss something, please forgive me.
Eugene.
PS: If somebody knows, how to solve this problem without export, please
inform us.
********************************************************
* Everything above is only my opinion *
********************************************************
* *
* If you see a lion in a cage and sign says "elephant" *
* , *
* DO not belive your eyes ! *
* (Kozma Prutkov. (informal translation)) *
* *
********************************************************
Jonathan Lewis
I haven't tried it yet, but there is a note in 7.3 about the
ALTER TABLE DEALLOCATE UNUSED
command that implies that if the space left is less than
MINEXTENTS, or INITIAL, then they will be adjusted downwards.
This may help in some cases.
---
Jonathan Lewis
ora_...@jlcomp.demon.co.uk
Alain chereau (CAP)
Eugene Freydenzon <efrey...@corpinfo.com> wrote:
>Roy deCarvalho wrote:
>>
>> I know that the storage parameters INITIAL and MINEXTENTS cannot be
>> altered. Any suggestions on how to go around this restriction? I need to
>> increase the INITIAL and MINEXTENTS of one table.
>>
>> Thanks for your help.
>>
>> Roy
>>
>> --
>
>
>PS: If somebody knows, how to solve this problem without export, please
>inform us.
>
You can do the same with :
Create table new_table as select * from table current_table
storage ( ... );
drop current_table;
rename new_table to current_table;
But you need to have the place for the two tables and you have the
work to put again referentials constraints and indexes.
my be it will be done in less time.
--------------------------------------------------------
As usual :
statements and opinions are mine and do not necessarily
reflect the opinions of my employer.
Scott T. Johnson
Roy deCarvalho wrote:
>
> I know that the storage parameters INITIAL and MINEXTENTS cannot be
> altered. Any suggestions on how to go around this restriction? I need to
> increase the INITIAL and MINEXTENTS of one table.
>
> Thanks for your help.
>
> Roy
>
> --
Another option is to copy the table with to a temp table, drop the
original table then copy the table back with the new storage paramters.
CREATE TABLE TEMPTAB AS SELECT * FROM ORIGINAL_TABLE;
DROP ORIGINAL_TABLE;
CREATE TABLE ORIGINAL_TABLE
TABLESPACE TABSPC_NAME
STORAGE (INITIAL 10M NEXT 1M PCTINCREASE 0);
Just an example above but you get the idea. Also set pctincrease to 0.
I have found that anything else tends to get me in trouble.
Good Luck,
Scott
Danny Roosens
HI,
rename your table
create as you would like it to be.
insert into ....
Eugene Freydenzon
Jonathan:
If you have 7.3 near you, I was wandering if could possibly try it.
I would appreciate that.
Thanx in advance,
Eugene.
--
Jonathan Lewis
Eugene.
Quick sample of using ALTER TABLE DEALLOCATE
Table temp was created with:
create table
temp (c1 varchar2(20))
storage (initial 50K next 50K minextents 4 pctincrease 0)
Script p1.sql contains the text:
select initial_extent, next_extent, min_extents
from user_tables
where table_name = 'TEMP'
================================
SQL*Plus session follows:
================================
SQL>start p1.sql
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
-------------- ----------- -----------
51200 51200 2
SQL> alter table temp deallocate unused keep 10K;
Table altered.
SQL>start p1.sql
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
-------------- ----------- -----------
12288 2048 1
======================================
Note: the KEEP gives the number of bytes ABOVE the high water
mark that should be kept, not the total size of the object.
Note: Since the baseline is the high water mark, you cannot
reduce the storage of a table from which you have deleted
large number of rows. The 'use' blocks are still below HWM
=======================================
---
Jonathan Lewis
ora_...@jlcomp.demon.co.uk
Jonathan Lewis
Eugene,
I lost a line in cutting and pasting.
The first line of the SQL*plus session should have
read -
alter table temp deallocate unused keep 50K;
Eugene Freydenzon
Thanx very much. Very important to me. Unfortunately, it does not work
for 7.1.6. and 7.2.3.
Thank you for your time,
DAVID DEVEJIAN
Scott T. Johnson <sjoh...@ibm.net> wrote in article
<32396D...@ibm.net>...
> Another option is to copy the table with to a temp table, drop the
> original table then copy the table back with the new storage paramters.
>
> CREATE TABLE TEMPTAB AS SELECT * FROM ORIGINAL_TABLE;
>
> DROP ORIGINAL_TABLE;
>
> CREATE TABLE ORIGINAL_TABLE
> TABLESPACE TABSPC_NAME
> STORAGE (INITIAL 10M NEXT 1M PCTINCREASE 0);
>
> Just an example above but you get the idea. Also set pctincrease to 0.
> I have found that anything else tends to get me in trouble.
>
> Good Luck,
> Scott
>
1) Why create temptab? Why not simply rename table? Should be quicker,
involve fewer steps and avoid problems with rollback segments that may not
be sized for such a large transaction.
2) I have heard that if you set pctincrease to something above 0, Oracle
will coalesce contiguous extents, while it will not do so for segments with
pctincrease set to 0. Something to do with not wanting to alter rollback
segments. As a result I usually set pctincrease to 1 (by the time this
compounds enough to get me in trouble, the table is fragmented enough that
I need to drop and recreate anyway).
Regards,
David Devejian
d...@bank2000.com