Skip to Main Content
  • Questions

  • Truncate changes next extent automatically

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Hakan.

Asked: December 28, 2000 - 3:14 pm UTC

Last updated: August 29, 2012 - 11:32 am UTC

Version: 8.1.6.2

Viewed 1000+ times

You Asked

hi tom,
to transfer modules from development to production, here is what i do,
i take an export, and import it into the production,
then i change all tables&indexes next extents and pctincrease (1m and 0)
then i use 'alter index ... rebuild tablespace INDX' to place the indexes,
and then truncate the tables...
pls mention that developers use various storage parameters...

after that, when i start to pump the real data to tables,
the tables and indexes gets their next extent back, (maybe not their, at least they are not 1m). also, although i set pctincrease to 0, some of them strangely expands like pctincrease<>0

i am really confused,
am i missing something ?

thank you very much.
have a good day.
hakan.



and Tom said...

Well, before we dive into this -- I think you REALLY want to use locally managed tablespaces with uniform sizes. In this fashion, the storage parameters are basically ignored (initial is obey but if you say initial 5m with a uniform size of 1m you'll get 5-1meg extents).


As for what happens with the truncate. From the SQL Reference manual:

...
The table’s storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.
....


So, that is where the "mystery" extent sizes are coming from. You want to do things in THIS order:

o take an export, import into production
o TRUNCATE then tables
o and THEN change the next
o and THEN rebuild the indexes (which will be much faster since
the tables are empty)


Don't forget -- extent sizes are requests, not mandates. We feel free to round them up and will round them up -- to make things fit (to avoid leaving really tiny holes). That'll explain the "pctincrease" which isn't really a pctincrease at all probably.


Again -- I think you really want to rebuild the database using locally managed tablespaces and avoid all of this alltogether. It is the ONLY type of tablespace I use now (except for system which cannot be locally managed)

Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Please clarify

Rob, April 25, 2002 - 4:36 pm UTC

The table’s storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.

Tom:

Does "last" extent mean the last extent deleted and then
the truncate is finished or the last extent that was
allocated to the segment. We have noticed this change
of next extent size when we truncate after a direct path load with sqlldr. I believe that direct path load trims the
last extent so it would seem necessary to reset next extent
after the truncate. Am I missing anything here.
(I know, I know, use LMT)

Tom Kyte
April 25, 2002 - 7:01 pm UTC

The table?s storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.



The extents should be removed in reverse order. If you direct path load into say an empty table and do not fill up the extent -- extent trimming will kick in on that exent and that would change the next downwards.

Addl. clarification

A reader, April 26, 2002 - 10:40 am UTC

Just to be clear, "reverse order" means the last extent
allocated to the table.
For example:

Table X has 1 initial extent, a direct path load causes
extent 2 to be allocated, as the load gets further along
extents 3,4 and 5 are allocated. Extent 5 would be trimmed
at the end of the load. Would truncate cause extent 1 or
extent 5 to be deallocated first.

Is it a valid workaround to use truncate with the reuse
storage clause?

Tom Kyte
April 26, 2002 - 11:19 am UTC 

no, not the last extent allocated.  Consider this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
  2  tablespace system
  3  storage ( initial 1k pctincrease 10 )
  4  as
  5  select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select extent_id, bytes, blocks
  2   from user_extents
  3  where segment_name = 'T'
  4  order by extent_id;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      16384          2
         1      16384          2
         2      24576          3
         3      32768          4
         4      40960          5
         5      81920         10
         6      81920         10
         7      81920         10
         8      81920         10
         9      81920         10
        10     122880         15
        11     122880         15
        12     122880         15
        13     163840         20
        14     163840         20
        15     204800         25
        16     204800         25
        17     245760         30
        18     245760         30
        19     286720         35
        20     327680         40

21 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
     335872

ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;

Table truncated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
      16384

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

The extents were freed in reverse order, from 20 on back.

So, the example I was talking about is when the "first second" extent (that LAST de-allocated extent) was trimmed.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
  2  tablespace system
  3  storage ( initial 1k next 5000k )
  4  as
  5  select * from all_objects where 1=0;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
    5120000

ops$tkyte@ORA817DEV.US.ORACLE.COM> host sqlldr 'ops$tkyte/xxx' t.ctl direct=true parallel=true

SQL*Loader: Release 8.1.7.3.0 - Production on Fri Apr 26 11:13:24 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Load completed - logical record count 22902.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select extent_id, bytes, blocks
  2   from user_extents
  3  where segment_name = 'T'
  4  order by extent_id;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      16384          2
         1    2580480        315    <b><<<< some trimming going on</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
    5120000

ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;

Table truncated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
    2580480   <b><<<<====== phenomena you observe</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


<b>Now, if I do it like this:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
  2  tablespace system
  3  storage ( initial 1k pctincrease 50)
  4  as
  5  select * from all_objects where 1=0;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
      16384

ops$tkyte@ORA817DEV.US.ORACLE.COM> host sqlldr 'ops$tkyte/megan95' t.ctl direct=true parallel=true

SQL*Loader: Release 8.1.7.3.0 - Production on Fri Apr 26 11:18:22 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Load completed - logical record count 22902.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select extent_id, bytes, blocks
  2   from user_extents
  3  where segment_name = 'T'
  4  order by extent_id;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      16384          2
         1      16384          2
         2      24576          3
         3      40960          5
         4      81920         10
         5     122880         15
         6     163840         20
         7     245760         30
         8     368640         45
         9     532480         65
        10     778240         95
        11     204800         25

12 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
      16384

ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;

Table truncated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
      24576

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select extent_id, bytes, blocks
  2   from user_extents
  3  where segment_name = 'T'
  4  order by extent_id;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      16384          2

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

<b>the next is not 204,800 (last trimmed extent)</b>

A reader, May 12, 2005 - 9:49 am UTC

Is truncate needed before drop on a 300000 row table? Would there be a difference in the number of extents managed by doing truncate? Thanks.

Tom Kyte
May 12, 2005 - 1:02 pm UTC

3,000,000 or 0 rows -- no difference in the answer which is:

if you are using dictionary managed tablespaces, the number of extents will affect the performance of drop and truncate (when the truncate releases the storage)

if you are using locally managed tablespaces, you will not notice much of a difference.

A reader, May 12, 2005 - 1:19 pm UTC

Thank you. My question actually was: Is the extent management changed by doing truncate before drop (we are using DMT)? Could you tell the difference between a "truncate & drop" and "drop" in general

Tom Kyte
May 12, 2005 - 1:42 pm UTC

just drop it if that is the goal. the truncate (if it must release the extents) will take as long to remove them as drop.

sqlldr truncate

friend, August 20, 2005 - 9:29 pm UTC

Tom,
I have a table name plant and there is one job with sqlldr with truncate option.
This job start loading data from flat files to plant table and next extent become 840 M whihc result in paging.
Please suggest
Oracle version is 9.2.0.6

Tom Kyte
August 21, 2005 - 8:07 am UTC 

what bearing does extent size have on paging?!?!

Truncate in sqlldr uses 

TRUNCATE TABLE T REUSE STORAGE


what that means is the "next" that is there (i'm assuming you are using dictionary managed tablespaces since you are talking about extents like this) is the "next" that was there when you truncated:


ops$xp8i\tkyte@ORA8IR3W> /*
DOC>
DOC>drop table t;
DOC>
DOC>create table t
DOC>(  x char(2000),
DOC>   y char(2000),
DOC>   z char(2000)
DOC>)
DOC>storage ( initial 1k next 1k pctincrease 10 )
DOC>/
DOC>
DOC>insert into t select 'x', 'y', 'z' from all_objects;
DOC>*/
ops$xp8i\tkyte@ORA8IR3W>
ops$xp8i\tkyte@ORA8IR3W> select extent_id, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     BLOCKS
---------- ----------
         0         16
....
        62       2080
        63       2288

64 rows selected.

ops$xp8i\tkyte@ORA8IR3W>
ops$xp8i\tkyte@ORA8IR3W> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
   20520960

ops$xp8i\tkyte@ORA8IR3W> truncate table t reuse storage;

Table truncated.

ops$xp8i\tkyte@ORA8IR3W> select next_extent from user_tables where table_name = 'T';

NEXT_EXTENT
-----------
   20520960


<b>You have control over next using a dictionary managed tablespace, you can make it anything you like - but it doesn't have anything to do with "paging"</b>

sqlldr

friend, August 22, 2005 - 1:30 am UTC

Tom,

1. Paging -- > my aler system
2. I migrated tablespace from dictionary managed to LMT.
Do you think I should reorganize this table?
after migration we are suppose to reorganize all objects ??

Please suggest

Tom Kyte
August 22, 2005 - 7:34 am UTC

1) fix your alert system then, it is giving you false positives.

2) I don't like the "migrate to local", it doesn't give you the nice attributes of LMT's. It is a good way to drop/truncate a table with thousands of extents (migrate the DMT to LMT and then drop, it'll be faster than dropping the table in the DMT) but other than that, I would prefer to set up a new LMT and alter table T move tablespace new; and alter index I rebuild tablespace new to migrate to an LMT


but I still don't see the problem here, that the next extent is 840m, so what? That must be what it was before you sqlldr'ed the data.

But since you truncate and reload, just DROP this table, create it in a new LMT and use that. Get all of the benefits of LMT's

Truncate table

friend, September 09, 2005 - 1:10 pm UTC

Tom,

I changed the netx extent of table A to 10 m but after lodaing the record Its get increase to 840M

Here is the procedure
Truncate --> sqlldr lodaing causing 840m next extent size

Please suggest how to avoid

Tom Kyte
September 09, 2005 - 1:33 pm UTC

you'll have to "show me"

in sqlplus, truncate the table, set the next extent, host out, run sqlldr, and show me what you mean.

Truncate

friend, September 09, 2005 - 2:27 pm UTC

I really dont have code with me

A is table and tablespace is TBSBIG next extent size is 880803840
Let me investigate more

truncate

friend, September 09, 2005 - 4:08 pm UTC

Tom,

I have a question
Will it be a good idea if I schedule a job based on alter table a storage( next 10m) runs at 01:00 to 01:30 every 5 min.

Is it going to affect the sqlldr job if its still going on after 1?

Please suggest

Tom Kyte
September 09, 2005 - 4:14 pm UTC

I don't understand, you have a job running on a regular basis to change next?????!?!?!

truncate

friend, September 09, 2005 - 4:28 pm UTC

I am planning to schedule

following:
0,5,10,20,30 1 * * * /oracle/admin/e/a.sh 1 > /oracle/admin/a/a.log 2>&1
alter table i.A storage ( next 10m pctincrease 0 );


Tom Kyte
September 09, 2005 - 4:39 pm UTC

WHY WHY WHY????????

if you need to do this, that implies you have some job out there CHANGING IT

with pctincrease = 0, next isn't going to change unless you are changing it.

TRUNCATE

friend, September 09, 2005 - 4:46 pm UTC

No ....
right now its

NEXT_EXTENT
-----------
10485760 and when sqlldr job runs it automatically goes to 880803840...

Why? :(




Tom Kyte
September 09, 2005 - 4:52 pm UTC

show use the initial, next and pctincrease

(you know, if you used a locally managed tablespace, this would all be moot)

truncate

friend, September 09, 2005 - 4:48 pm UTC 

SQL> select next_extent ,PCT_INCREASE fROM dba_tables where table_name='A';

NEXT_EXTENT PCT_INCREASE
----------- ------------
   10485760            0

Tom Kyte
September 09, 2005 - 4:52 pm UTC

there you go, now, sqlldr won't change that. if it does, "show us", cut and paste just like I do.

truncate

friend, September 09, 2005 - 4:56 pm UTC 

SQL> select initial_extent ,next_extent ,PCT_INCREASE,tablespace_name fROM dba_tables where table_na
me='A;

INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE TABLESPACE
-------------- ----------- ------------ ----------
      10485760    10485760            0 BIG

SQL> select extent_management from dba_tablespaces where tablespace_name='BIG';

EXTENT_MANAGEMENT
------------------------------
LOCAL

I migrated above tablespace  form DMT to LMT.
at 12:20 one job starts which truncate this table and call sqlldr then at 01:30 i receive pager of next extent

Tom Kyte
September 09, 2005 - 5:14 pm UTC

ugh, migrated dmt's.

any chance you can use a *real* one?

truncate

friend, September 09, 2005 - 5:08 pm UTC

load data
append into table A truncate
fields terminated by '|' trailing nullcols
(
columns
.
.
.
)



A reader, January 26, 2006 - 3:47 pm UTC

It would save the time and get the answer quickly if the requester ("friend") checked the Metalink (published in 2003/04). Tom answers too many questions.

Doc ID: Note:269374.1
Next_extent of a table in LMT migrated from DMT does not get reset on deallocating extents

Bug 3338673 - Truncate Does Not Reset Next_Extent Value Of Table In Lmt Migrated From Dmt
Fixed in Oracle 10.2.0.

Working as DBA for 9 years, I occasionally read this useful website and this is my 1st message.

TRUNCATE - DROP ALL STORAGE

Rajeshwaran, Jeyabal, May 03, 2011 - 1:55 pm UTC

Tom:

I was reading about this DROP ALL STORAGE from documentation.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10007.htm#i2067571

Now i am getting this error message. Is there is any problem with script?

rajesh@ORA11GR2> truncate table T drop storage;

Table truncated.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> truncate table T drop all storage;
truncate table T drop all storage
                      *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword


Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Tom Kyte
May 04, 2011 - 1:44 pm UTC 

ops$tkyte%ORA11GR2> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

ops$tkyte%ORA11GR2> truncate table t drop all storage;

Table truncated.


what is your exact version - probably not 11.2.0.2 when this was introduced...

TRUNCATE - DROP ALL STORAGE

Rajeshwaran, Jeyabal, May 04, 2011 - 3:46 pm UTC


Sorry, Tom I am in 11.2.0.1

rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Tom Kyte
May 05, 2011 - 3:58 pm UTC

so, consider it answered...

what is your exact version - probably not 11.2.0.2 when this was introduced...

Truncate and Index

Ankit, June 28, 2012 - 4:45 am UTC

Hi Tom
Thanks for the knowledge sharing.

I have 5 huge tables (20m rows each). Each has 2-3 indexes.
I need to truncate all 5 and then refill them.
I am concerned about indexes.
How to make sure that indexes have latest data only? They must have pointers to new data after re-fill and OLD data must not be present in them.

Thanks.
Tom Kyte
June 28, 2012 - 11:51 pm UTC

20,000,000 is a pretty small number, definitely not huge. Unless your rows are 1gb in size, then maybe they are huge. But 20,000,000 is pretty small in 2012.

when you truncate a table, the indexes get truncated too. don't worry - we do the right thing.

truncate table

A reader, August 22, 2012 - 5:57 pm UTC

Hi Tom,

I observed for some of my tables TRUNCATE TABLE does not update LAST_DDL_TIME whereas it works for some.

Database:oracle 11gR2

Could you please suggest.
Tom Kyte
August 29, 2012 - 11:32 am UTC

probably they were already truncated, we realized that, and did nothing when you said "truncate"

ops$tkyte%ORA11GR2> create table t ( x int ) segment creation immediate;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'T';

LAST_DDL_TIME
--------------------
29-aug-2012 12:31:50

ops$tkyte%ORA11GR2> exec dbms_lock.sleep(3)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> truncate table t;

Table truncated.

ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'T';

LAST_DDL_TIME
--------------------
29-aug-2012 12:31:50

ops$tkyte%ORA11GR2> insert into t values  (1);

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> exec dbms_lock.sleep(3)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> truncate table t;

Table truncated.

ops$tkyte%ORA11GR2> select last_ddl_time from user_objects where object_name = 'T';

LAST_DDL_TIME
--------------------
29-aug-2012 12:31:56

ops$tkyte%ORA11GR2>

Thanks Tom

A reader, August 31, 2012 - 3:31 am UTC

Yes Tom

You are correct. I observed.. those were blank tables without any rows in those.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.