how to change initial_extent size of a table

how to change initial_extent size of a table
Who is Participating?
Well,   there are two answers to this question -- depending upon your version

1)   Pre-Oracle 8i --  drop an recreate the table and then reload  (probably best done with export/import)

2)  8i and beyond --     use the new "alter table ....  move tablespace .... storage..;'  feature.
there is a new function under the alter tablecommand called  MOVE TABLE clause.
The  move_table_clause lets you relocate data of a nonpartitioned table into a new segment,
optionally in a different tablespace, and optionally modify any of its storage attributes.
This can be done without taking down the database  (however,  I recommend that you take an
export for safety)...
The syntax is:
alter table <TABLE_NAME>  move tablespace <NEW_TBSP>  storage( <STORAGE_CLAUSE>);

One restriction,  you must rebuild all associated indexes after moving a table because the move invalidates
them.   You rebuild indexes with the following command;
alter index <INDEX_NAME>  rebuild tablespace <NEW_TBSP>  storage( <STORAGE_CLAUSE>);

here is sample (actual)  output from the SCOTT schema  running on Oracle 8.1.6 on Sun E5000:
$ sqlplus scott/tiger
SQL*Plus: Release - Production on Mon Jan 15 19:36:28 2001
(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production

SQL> set linesize 132
SQL> desc scott.emp
Name      Null?    Type
------------------------------------------------------------------------ -------- -------------------------------------------------
ENAME              VARCHAR2(10)
JOB                VARCHAR2(9)
SAL                NUMBER(7,2)
COMM               NUMBER(7,2)
DEPTNO             NUMBER(2)

SQL> get table_size
 1  select a.owner, a.table_name,
 2         a.initial_extent,a.next_extent,
 4         a.NUM_ROWS, b.extents NUM_EXTENTS
 5  from dba_tables a, dba_segments b
 6  where a.owner='SCOTT'
 7    and a.owner=b.owner
 8    and a.table_name =b.segment_name
 9*   and a.table_name ='EMP'
SQL> /

---------- ---------- -------------- ----------- ----------- ----------- --------------- ----------
SCOTT      EMP                 40960       40960           1         199 DATA                    14
1 row selected.

SQL> alter table scott.emp
 2   move tablespace DATA
 3  storage (initial 200K next 200K minextents 1 maxextents 299)
 4  /
Table altered.

Note:  there is one catch -- the table cannot contain a column of the long datatype

3) Random advice:
Don't go crazy about compressing tables into a single extent  (or just a few extents).  There was
According to presentations at IOUG2000 -- from Oracle's Performance Group, TUSC, & others (Mike Ault,  Dave Ensor...),  the number of outstanding  extents that a table has DOES NOT IMPACT performance until  a high number is reached  -- they have noticed  performance slowdowns after 1000 extents -- even with Locally managed Tablespaces.   I have tested this in Oracle 8 and have confirmed this.

Follow the traditional rules,  such as one of mine is to size growth rates for apx 1 extent per month
When allocating storage for an Object (table or index),  I like to set the initial extent size = next
extent size, plus I place objects of the same extent sizes in the same tablespace:   Why?
This reduces the fragmentation of the extents and makes maximum efficient use of the tablespace's space
(even with tables that grow and shrink dynamically)   while reducing the possibility of creating dead
space fragments.  And while extents do grow,  In theory  I shouldn't have to touch them for a long period
of time.
So,   you must should know you database  & data   Thus plan in advance -- do calculations of the growth
and set storage sizes appropriately --  while the new tools allow you to do storage management on the
fly,  you do not want to be babysitting extent managment all the time -- takes away from the really
challenging DBA tasks!

There are two good papers to read about extent sizing and fragmentation & Storage Management.
1) "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation"    from Oracle
presented at OracleWorld 1999
2) I cannot remember the name -- but the is a great paper on

Those are the best ways..


Initial extent of a table depends upon the initial extent of your tablespace you can give initial extent more than the tablespace extent but not less then that.

For changing the initial extent you have to recreate your table with increased initial extent.

for that
export the table
drop the table
recreate the table
create table ()
initial next max;
import the data with compress=y
all the data for this table will come to initial extent.
hope this will help
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You must drop the table and recreate it with the appropriate settings.
To keep the data, you might create another table to keep copies of the data, copy the data, drop your table, recreated it, copy the data back, and drop the copy table.

You won't be able to change the initial extent size of a table after the table has been created, because this value is only used for the creation of the table...

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I guess you already have a table say A. To change its initial_extent size you can create a new table with the storage you want as select * from the source table (say A).

You can also export the table, specifying compress=Y in the export command. By that option after importing the table its initial extent size will equal the size of the table (it will only occupy one extent)

Oracle Database 9i provides this feature through 'Online Table Redefination feature'

However in versions earlier to that You can only drop and recreate the table with new initial value along with it's
associated triggers and all

e.g create tabele emp2 storage(initial 10) as select * from emp;

but this doesn't create the triggers on emp2 which were on emp.

What do u think Guys?
I don't catch very well this question?
I you have already this table i think that you have to be careful with next extent size not with initial one.
But if you want to recreate table the suggestion of j2020242 is good enough. One more addition after importing data in new table do command
alter table tablename storage ( next xxx mb/bk);
in order to change next extent size.
HEYONGGANGAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.