Solved

how to change initial_extent size of a table

Posted on 2001-07-09
7
2,555 Views
Last Modified: 2007-12-19
how to change initial_extent size of a table
0
Comment
Question by:HEYONGGANG
7 Comments
 
LVL 1

Expert Comment

by:sunrock_in
Comment Utility
Hi

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
Thanks
Sunrock
0
 
LVL 142

Expert Comment

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

Cheers
0
 
LVL 1

Expert Comment

by:i020242
Comment Utility
Hi,
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)

Cheers,
J.K
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:UsamaMunir
Comment Utility
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?
0
 
LVL 1

Expert Comment

by:misho2000
Comment Utility
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.
0
 
LVL 9

Accepted Solution

by:
dbalaski earned 50 total points
Comment Utility
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 8.1.6.0.0 - Production on Mon Jan 15 19:36:28 2001
(c) Copyright 1999 Oracle Corporation.  All rights reserved.

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

SQL> set linesize 132
SQL> desc scott.emp
Name      Null?    Type
------------------------------------------------------------------------ -------- -------------------------------------------------
EMPNO     NOT NULL NUMBER(4)
ENAME              VARCHAR2(10)
JOB                VARCHAR2(9)
HIREDATE           DATE
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,
 3         a.MIN_EXTENTS, a.MAX_EXTENTS, a.TABLESPACE_NAME,
 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> /

OWNER      TABLE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS TABLESPACE_NAME   NUM_ROWS
NUM_EXTENTS
---------- ---------- -------------- ----------- ----------- ----------- --------------- ----------
-----------
SCOTT      EMP                 40960       40960           1         199 DATA                    14
          6
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..

sincerely,
dBalaski
0
 

Author Comment

by:HEYONGGANG
Comment Utility
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now