Re: Should my extents be bigger?

From: <bartavi_at_mail.tlm.openu.ac.il>
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

Original text of this message