Re: TRUNCATE ???

From: Andreas Hess <ahess_at_truworths.co.za>
Date: 1997/11/07
Message-ID: <01bceb51$dce980e0$300610ac@trahe>#1/1

Hi again

Thanks for the response - I guess I should have made myself a little clearer. I have re-created the 'error' and really do think this may be a minor bug.

So for your info, here goes...

Wrote file afiedt.buf
  1 create table abc storage (initial 20k next 20k maxextents 121)   2 as select * from contract_header
  3* where rownum < 1
SQL> / Table created.

SQL> select * from user_segments
  2 where segment_name = 'ABC';

SEGMENT_NAME                                                               
      SEGMENT_TYPE
----------------------------------------------------------------------------


----- -----------------

TABLESPACE_NAME                    BYTES    BLOCKS   EXTENTS INITIAL_EXTENT

NEXT_EXTENT MIN_EXTENTS

------------------------------ --------- --------- --------- --------------

----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ------------ --------- ---------------

ABC                                                                        
      TABLE
RTK_DATA                           20480         5         1          20480
      20480           1
        121            0         1               1

SQL> insert into abc select * from contract_header; insert into abc select * from contract_header

            *
ERROR at line 1:
ORA-01631: max # extents (121) reached in table RTKVT65.ABC

SQL> select * from user_segments
  2 where segment_name = 'ABC';

SEGMENT_NAME                                                               
      SEGMENT_TYPE
----------------------------------------------------------------------------


----- -----------------

TABLESPACE_NAME                    BYTES    BLOCKS   EXTENTS INITIAL_EXTENT

NEXT_EXTENT MIN_EXTENTS

------------------------------ --------- --------- --------- --------------

----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ------------ --------- ---------------

ABC                                                                        
      TABLE
RTK_DATA                         2478080       605       121          20480
      20480           1
        121            0         1               1

SQL> alter table abc storage (next 500k maxextents 150);

Table altered.

SQL> select * from user_segments
  2 where segment_name = 'ABC';

SEGMENT_NAME                                                               
      SEGMENT_TYPE
----------------------------------------------------------------------------


----- -----------------

TABLESPACE_NAME                    BYTES    BLOCKS   EXTENTS INITIAL_EXTENT

NEXT_EXTENT MIN_EXTENTS

------------------------------ --------- --------- --------- --------------

----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ------------ --------- ---------------

ABC                                                                        
      TABLE
RTK_DATA                         2478080       605       121          20480
     512000           1
        150            0         1               1

SQL> truncate table abc;

Table truncated.

SQL> select * from user_segments
  2 where segment_name = 'ABC';

SEGMENT_NAME                                                               
      SEGMENT_TYPE
----------------------------------------------------------------------------


----- -----------------

TABLESPACE_NAME                    BYTES    BLOCKS   EXTENTS INITIAL_EXTENT

NEXT_EXTENT MIN_EXTENTS

------------------------------ --------- --------- --------- --------------

----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ------------ --------- ---------------

ABC                                                                        
      TABLE
RTK_DATA                           20480         5         1          20480
      20480           1
        150            0         1               1

Any comments ?

Thanks,

Andreas Received on Fri Nov 07 1997 - 00:00:00 CST