Re: Should my extents be bigger?

Jacques Raymond Kilchoer 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
>
> --
> ___
> ___
> (___)=================================================================(___)
> | | Jacques Raymond Kilchoer MIS:Applications Support
> | |
> | | Technical Specialist /^\ ^ (714) 729-4500 x3733
> | |
> | | Cost Care, Inc. /\ _/ \/ \ fax 729-4651
> | |
> | | Newport Beach, CA /\ /\/ \// \ \_/\
> | |
> | | / \ / / \ / \ Schweiz
> | |
> |___| Suisse ____/____\____/_________\___ _\_______ Svizzera
> |___|
> (___)=================================================================(___)

The datafile is the file that contains the tablespace temp. It was created with a create tablespace statement that identified the file name and was created with a size of 500 megabytes. The defaults on the tablespace when created were set to what is shown above for the tablespace. This means if you create a table in this tablespace and do not specify storage parameters that the table will be created with these defaults. You can create a table specifying any size initial and next extents as long as you don't exceed the 500mg size limitation on the tablespace.

I beleive you are confusing tables with datafiles. They are two distinct things.

A table is a collection of rows of data contained in two or more blocks.

A tablespace is a designated area that the tables reside within it's boundaries.

A datafile is the disk file that provides the tablespace with space to hold tables. A tablespace may contain multiple datafiles.

Hope this clears up the confusion for you.

Dick Allie - Oklahoma City, OK - email = dallie_at_ionet.net Received on Mon Sep 23 1996 - 00:00:00 CEST