Re: tablespace

In article <3ontap$7ie_at_senator-bedfellow.MIT.EDU>, jkhuon_at_athena.mit.edu (Jenny Khuon) writes:
::From: jkhuon_at_athena.mit.edu (Jenny Khuon)
::Newsgroups: comp.databases.oracle
::Subject: tablespace
::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

-- 
 Jenny,

 The problem is you have exceeded the maximum number of extents on a 
 temporary table. Oracle is creating a table on your behalf for reasons
 which are unclear from the information you have provided.

 Is ogb.employee a view ? What volumme of records will be returned ?

 Anyway, your problem lies in tablespace TEMP. You can change the default
 size of each object created in that tablespace (see ALTER TABLESPACE)
 but I suspect the root cause is the size of information returned from
 the query. Whenever I have encountered this in the past it was usually 
 something elsewhere that was causing the problem (a view which joined
 tables with a missing join field for instance).

 
 Ian

+--------------------------+-------------------------+-----------------------+
| Ian Bainbridge           | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
| British Gas ERS          | Newcastle Upon Tyne, UK |            x2790      |
+--------------------------+-------------------------+-----------------------+