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

[Thomas Jeff]

Title: Create Mat View uses DEFAULT TABLESPACE for temp segments?

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-----
From: Freeman Robert - IL [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?

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-----
From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 8:04 AM
To: Multiple recipients of list ORACLE-L
Subject: Create Mat View uses DEFAULT TABLESPACE for temp segments?

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: [EMAIL PROTECTED]

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