Re: tablespace

From: Bob Swisshelm <swisshelm_at_lilly.com>
Date: 1995/05/11
Message-ID: <1995May11.140025.4801_at_inet.d48.lilly.com>#1/1


In article <3ontap$7ie_at_senator-bedfellow.MIT.EDU> Jenny Khuon, jkhuon_at_athena.mit.edu writes:
>Does anyone know how to solve the problem as below:
>
>SQL> select emp_no, ename from ogb.employee;
>ERROR:
>ORA-01630: max # extents (121) reached in temp segment in tablespace TEMP
>
>no rows selected

As others have said, your select is attempting to create a temporary segment with more that 121 extents. My guess is that ogd.employee is a view that has an ORDER BY clause, or some other ORACLE operation that requires a temporary segment.

In any event, these are the procedures that we use for temporary segments.

  1. Create a special tablespace just for temporary segments. Don't give any users quota on that tablespace, but make sure that their TEMPORARY TABLESPACE is set to that tablespace. They don't need quota to be able to create temporary segments.
  2. Set the DEFAULT STORAGE parameters for the temporary tablespace. We generally set INITIAL to 4*SORT_AREA_SIZE, or 256K. We set NEXT to 1M, and PCTINCREASE to 0. Setting PCTINCREASE to 0 reduces fragmentation in the temporary tablespaces. Using these numbers, you would be able to create a temporary segment of 120M + 256K before you reached the maximum number of extents. Adjust those numbers based on your expected need.

     In my opinion, you don't have to mess with PCTFREE. ORACLE uses this parameter to

     determine when to place a block back on the free list as you delete records. You don't

     delete records from temporary segments. Actually, this is moot, because you can't specify

     a default PCTFREE for a tablespace, because it actually isn't a parameter of the

    STORAGE clause.

I hope this is clear enough, and helps..

Bob Swisshelm | swisshelm_at_Lilly.com | 317 276 5472 Eli Lilly and Company | Lilly Corporate Center | Indianapolis, IN 46285 Received on Thu May 11 1995 - 00:00:00 CEST

Original text of this message