Truncate changes storage parameter in table

[grjohnson]

Oracle 8.0.6.0.0 HP-UX 11.0

I came across the following issue. I had some incorrectly sized NEXT extent sizes on some tables. I changed their NEXT EXTENTS to a more appropriate size. Every thing was fine, until a week later I came back to recheck.. only to discover the NEXT EXTENTS were... back to their original sizes. This was strange, so I did the following test.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2129920

SQL> ALTER TABLE LOCN_NST_SUT_QTY STORAGE (NEXT 2048K);

Table altered.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2097152

SQL> INSERT INTO LOCN_NST_SUT_QTY
(SELECT * FROM LOCN_NST_SUT_QTY_TEMP); 2

129918 rows created.

SQL> COMMIT;

Commit complete.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2097152

SQL> TRUNCATE TABLE LOCN_NST_SUT_QTY;

Table truncated.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2129920

Tablespace info
SQL> select * from dba_tablespaces
where tablespace_name = 'NDSD_TS1'; 2

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING
----------- ------------ ---------- --------- --------- ---------
NDSD_TS1 1048576 1048576 1
2147483645 0 0 ONLINE PERMANENT LOGGING

Table info

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ----------------- ------------------------------
BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
---------- ---------- ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_
----------- ------------ ---------- --------------- -------
LOCN_NST_SUT_QTY
TABLE NDSD_TS1
1064960 130 1 1048576 2129920 1
2147483645 0 1 1 DEFAULT

What I found out later is that if perform an

ALTER TABLE table_name STORAGE (NEXT );

Then TRUNCATE the table.

The NEXT storage value is reset to the size of the last extent that is deleted during the truncation. Hence, the size set in the ALTER statement is effectively ignored since no extent of that size has been created. Interesting, and painfully annoying.

Therefore, ORACLE suggests you must perform the ALTER TABLE statement AFTER the TRUNCATE Statement. As follows.

Lets see if it works...


SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2129920

SQL> TRUNCATE TABLE LOCN_NST_SUT_QTY;

Table truncated.

SQL> ALTER TABLE LOCN_NST_SUT_QTY STORAGE (NEXT 2048K);

Table altered.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2097152

SQL> INSERT INTO LOCN_NST_SUT_QTY
(SELECT * FROM LOCN_NST_SUT_QTY_TEMP); 2

129918 rows created.

SQL> COMMIT;

Commit complete.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2097152

SQL> TRUNCATE TABLE LOCN_NST_SUT_QTY;


Table truncated.

SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2129920


OPPPPSsssss….. didn’t work. Any other ideas people.

[Edited by grjohnson on 02-13-2002 at 07:58 PM]

[gpsingh]

why don't u do this way,
create table a ( a number storage (next 2K));
insert int o a values(1);
insert int o a values(1);insert int o a values(1);
insert int o a values(1);
insert int o a values(1);
insert int o a values(1);
insert int o a values(1);
insert int o a values(1);
commit;
insert into a as select * from a;
insert into a as select * from a;
commit;
check if more than 2 extents have been used.
alter table a (storage next 3K);
insert further so that the number of extents used is more than before the alter command.
truncate table a;
now select what is the value for next extent.
It should be 3K.

[grjohnson]

But in my second test, I am altering my table AFTER the truncate and then re-inserting all the data back to the tables. When I truncate the table again the extent size changes back again.

I'm sure that by recreating the table, your test would be correct. But I thought recreating the table little EXCESSIVE, especially if there are hundreds of tables that need changing. This additionally means, re-issuing all grants etc. and turning a seemingly "invisable" task to users, into one that requires significant outage.

[SANJAY_G]

I tried on my test db and it does change the NEXT_EXTENT after truncate. Wondering why??

Sanjay

[grjohnson]

Got it, from some help from Thirupathi Majjiga from Metalink.

ALTER TABLE LOCN_NST_SUT_QTY STORAGE (NEXT 2048K);

TRUNCATE TABLE LOCN_NST_SUT_QTY REUSE STORAGE;

Doing this will keep the NEXT extent size as to what has been set. Although, it keeps the existing extents. I'm still looking at a way to get around this.
SQL> select next_extent from user_segments where segment_name ='LOCN_NST_SUT_QTY
';

NEXT_EXTENT
-----------
2097152

SQL> select next_extent, extents from user_segments where segment_name ='LOCN_NS
T_SUT_QTY';

NEXT_EXTENT EXTENTS
----------- ----------
2097152 10

SQL> TRUNCATE TABLE LOCN_NST_SUT_QTY REUSE STORAGE;

Table truncated.

SQL> select next_extent, extents from user_segments where segment_name ='LOCN_NS
T_SUT_QTY';

NEXT_EXTENT EXTENTS
----------- ----------
2097152 10

SQL> TRUNCATE TABLE LOCN_NST_SUT_QTY;

Table truncated.

SQL> select next_extent, extents from user_segments where segment_name ='LOCN_NS
T_SUT_QTY';

NEXT_EXTENT EXTENTS
----------- ----------
1064960 1

SQL> ALTER TABLE LOCN_NST_SUT_QTY STORAGE (NEXT 2048K);

Table altered.

SQL> select next_extent, extents from user_segments where segment_name ='LOCN_NS
T_SUT_QTY';

NEXT_EXTENT EXTENTS
----------- ----------
2097152 1


SQL> INSERT INTO LOCN_NST_SUT_QTY
(SELECT * FROM LOCN_NST_SUT_QTY_TEMP);

COMMIT; 2

129918 rows created.

SQL> SQL>
Commit complete.

SQL> select next_extent, extents from user_segments where segment_name ='LOCN_NS
T_SUT_QTY';

NEXT_EXTENT EXTENTS
----------- ----------
2097152 6

SQL> TRUNCATE TABLE LOCN_NST_SUT_QTY;

Table truncated.

SQL> INSERT INTO LOCN_NST_SUT_QTY
(SELECT * FROM LOCN_NST_SUT_QTY_TEMP);

COMMIT; 2

129918 rows created.

SQL> SQL>
Commit complete.

SQL> select next_extent, extents from user_segments where segment_name ='LOCN_NS
T_SUT_QTY';

NEXT_EXTENT EXTENTS
----------- ----------
2129920 6
Crickey, seems like I can't trick it either.

Cheers,

[Edited by grjohnson on 02-14-2002 at 02:01 AM]