It's not blatantly obvious, but if you've created the whole thing in the standard way Oracle will be creating an index on the table that holds the materialized view data - and that index will go into your default tablespace.
Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 January 2003 16:06 >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' >-------------------------------------------- > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
