Oracle-L: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Wed, 08 Jan 2003 09:35:21 -0800
Message-ID: <F001.0052A457.20030108093521@fatcity.com>

Yes, but these operations should be using the TARGET tablespace, and not the DEFAULT
tablespace to create the TEMP segments, correct? I know when I rebuild indexes
at least, that's the case.

-----Original Message-----
Sent: Wednesday, January 08, 2003 10:15 AM To: Multiple recipients of list ORACLE-L

Perfectly normal. Temporary objects are used for more than just sorting. They are used when creating indexes, tables, mviews and certain other objects and operations (such as some parallel operations). The temp segment in this case will become the permanent segment once the MView is created.

Robert

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com

-----Original Message-----
Sent: Wednesday, January 08, 2003 8:04 AM To: Multiple recipients of list ORACLE-L

This is 8.1.7.4 on AIX 4.3.3. When I try to create a materialized view in
the specified tablespace, I hit the following error. Is this a behavior of
the create materialized view statement, to create temp segments in the user's
default tablespace, and not use the target tablespace? I've double-checked

all ts quotas and appropriate privs. Changing the user's default tablespace

to the target tablespace works of course. Or am I missing something blatantly obvious here?

SQL> CREATE MATERIALIZED VIEW tcs_ord_hist_mv   2 TABLESPACE TCS_MD_DT01
  3 BUILD IMMEDIATE
  4 REFRESH ON DEMAND
  5 ENABLE QUERY REWRITE
  6 AS

  7     SELECT customer_number, 
  8            product_number, 
  9            SUM(order_quantity)  history_ordered_qty 
 10       FROM tcs.tcs_order_history 
 11      GROUP BY customer_number, product_number 
 12  / 
     FROM tcs.tcs_order_history 
              *

ERROR at line 10:
ORA-01630: max # extents (505) reached in temp segment in tablespace USERS

Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.

Email: jeff.thomas_at_thomson.net

Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba <http://gkmqp.tce.com/tis_dba> Select 'Indy DBA' then 'DBA Web Pages'

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Thomas Jeff
INET: ThomasJe_at_tce.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jan 08 2003 - 11:35:21 CST