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

You were right.

Tracing the operation did show at the end that there's a CREATE UNIQUE INDEX

statement with no tablespace specified. Checking the manuals a bit more carefully shows that Oracle will create at least one internal table and at least one index for the mat view.

It also states that the matview must have sufficient quota in the the target tablespace to store the master table and index.

The obvious question then is: how I do specify a target tablespace for this index? I've tried various parameters but can't seem to get it to work.

-----Original Message-----
Sent: Wednesday, January 08, 2003 2:21 PM To: Multiple recipients of list ORACLE-L

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 <ORACLE-L_at_fatcity.com> 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: jeff.thomas_at_thomson.net
>
>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: jonathan_at_jlcomp.demon.co.uk

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).

-- 
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).