SQL on how to calculate next extent if pctincre >0

From: Coles Technical Services <cts_at_coles.com.au>
Date: 1995/10/13
Message-ID: <DGDEtp.ILt_at_cnw15.coles.com.au>#1/1

I am trying to write a report to report on objects that are unable to extend in a given tablespace if and object should need to extend. I am having trouble trying to workout the SQL that would calulate the next extent if the pctincrease is >0. I was wondering if anyone out there has done this before and could point me in the right direction in regards to the calculation of the next extent.

I had tried rounding and ceiling to no avail.

 The Oracle DBA manual says:

                     The new NEXT equals 1+PCTINCREASE/100, multiplied by the 
                     size of the last incremental extent (the old NEXT) and 
                     rounded up to the next multiple of the block size.

                     I created a table called "A" as follows:
                     create table a
                     storage (initial 20k next 10k pctincrease 20)
                     as select * from dual;
                     I did a few inserts to make the table extents grow.
                     select EXTENT_ID ,BYTES,BLOCKS
                     from user_extents

                 EXTENT_ID      BYTES     BLOCKS
                ---------- ---------- ----------
                         0      20480         10
                         1      10240          5
                         2      20480         10
                         3      20480         10
                         4      20480         10
                         5      20480         10
                         6      20480         10
                         7      30720         15
                         8      30720         15
                         9      30720         15
                        10      40960         20
                        11      40960         20
                        12      51200         25
                        13      51200         25
                        14      61440         30

Any help would be appreicated,
Regards Peter
--

<<<<<<<<<<<<<<<<<<<<<  Coles Technical Services  >>>>>>>>>>>>>>>>>>>>>
<<  Post: Coles Supermarkets, PO Box 480, Glen Iris 3146, Australia >>
<< Phone: +61 3 9829 5149                   E-mail: cts_at_coles.com.au >>
<<   FAX: +61 3 9829 6886                                            >>

--

The opinions of the poster do not necessarily represent those of the company. Received on Fri Oct 13 1995 - 00:00:00 CET