Re: Sort tablespace usage

On Mon, 18 Jun 2001 11:14:20 -0400, "Vincent Ventrone" <vav_at_brandeis.edu> wrote:

>
>> Don't know, I haven't looked at that yet. I'd probably check CREATE
>> TABLESPACE in the SQL Reference, and related material in Oracle 8i
>> Concepts.
>
>Look for "CREATE TEMPORARY TABLESPACE..." (8i syntax). Note: you will need
>to use temporary files & you should (IMHO) make it a locally-managed
>tablespace. You probably also want to use uniform extent sizes.
>
>Here's an example to get you started:
>
>CREATE TEMPORARY TABLESPACE TEMP
>
>TEMPFILE '/r01/oradata/catdev/temp_01.dbf' SIZE 100M
>
>AUTOEXTEND ON NEXT 25M MAXSIZE 500M
>
>EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
>
>
>

So, let me get this straight.

1)
>A single sort allocates its own temporary segment, in the temporary
>tablespace of the user issuing the statement, and then returns the
>extents to the tablespace.

This is a tablespace created with a normal CREATE TABLESPACE statement. It is only "temporary" because it is assigned to a user as such.

2)
>Multiple sorts, however, can use sort segments in a temporary
>tablespace designated exclusively for sorts. These sort segments are
>allocated only once for the instance, and they are not returned after
>the sort but remain available for other multiple sorts.

This is a temporary tablespace created with the CREATE TEMPORARY TABLESPACE statement (or the CREATE TABLESPACE statement with the TEMPORARY clause). It does not need to be assigned to any user.

Are both those statements correct?

Brian Received on Mon Jun 18 2001 - 15:06:58 CDT