Re: Should my extents be bigger?
Date: 1996/09/26
Message-ID: <DyCz50.HqF_at_cs.openu.ac.il>#1/1
Jacques Raymond Kilchoer <jrkilch_at_costcare.com> wrote:
>Oracle7 Server Release 7.1.3.2.0 - Production Release
>VAX OpenVMS V6.1-1H2
>I have a tablespace called TEMP with an initial_extent = 256,000
> a next_extent = 1,048,576
> a min_extents = 2
> a max_extents = 121
>(see below)
>The data file for the TEMP table has 524,288,000 bytes
>(see below)
>It seems to me that since tablespace TEMP has a max_extents of 121, it
>can use up
>at most 1 * 256,000 (initial_extent) + 120 * 1,048,576 (next_extent)
> = 256,000 + 125,829,120 = 126,085,120
>Since 126,085,120 is less than 524,288,000 I would think that the
>datafile for the
>tablespace never gets used to its full capacity.
>Is my understanding correct?
>If so, what should I do? Increate the next_extent size or the
>max_extents?
>*******************************************************************
>tablespace and datafile information
>SQL> select * from dba_tablespaces where tablespace_name = 'TEMP' ;
>TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
>------------------------------ -------------- ----------- -----------
>MAX_EXTENTS PCT_INCREASE STATUS
>----------- ------------ ---------
>TEMP 256000 1048576 2
> 121 0 ONLINE
>SQL> select * from dba_data_files where tablespace_name = 'TEMP' ;
>FILE_NAME
>--------------------------------------------------------------------------------
> FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
>---------- ------------------------------ ---------- ----------
>---------
>ORA$DISK3:[MISDEV]TEMP_01.DBF
> 3 TEMP 524288000 256000
>AVAILABLE
the values you have selected from dba_tablespaces are the default
values that oracle use when you create table in tablespace temp.
if specify other values when you create a table, those values (from
dba_tablespaces) are ignoed. for example:
CREATE TABLE TEMP_TAB( COL1 NUMBER ) STORAGE( INITIAL 10M NEXT 15M) TABLESPACE TEMP;
so table TEMP_TAB will have initial extent of 10 megabyte and next extent of 15 megabyte. (other values - such as minextents - will be taken from dba_tablespaces)
anyway, you can create many other tables in this tablespace, so all it's space is used.
hoope it helped,
bartal. Received on Thu Sep 26 1996 - 00:00:00 CEST