how to change initial_extent size of a table

Posted on 2001-07-09
Medium Priority
Last Modified: 2007-12-19
how to change initial_extent size of a table
Question by:HEYONGGANG
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 6264349

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
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6264352
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...


Expert Comment

ID: 6264361
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)

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


Expert Comment

ID: 6264376
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?

Expert Comment

ID: 6264603
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.

Accepted Solution

dbalaski earned 200 total points
ID: 6267266
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 www.orapub.com

Those are the best ways..


Author Comment

ID: 6267355

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question