| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Create Mat View uses DEFAULT TABLESPACE for temp segments?
<sigh> It's always fun to come out looking a bit dumb in front of
such a prestigious group.
Thanks Jared.
My problem turned out to be the placement of the BUILD clause. Yes, BUILD IMMEDIATE is the default, but my personal preference is to display all default parameters within reason within all DDL.
-----Original Message-----
Sent: Wednesday, January 08, 2003 5:44 PM
To: Multiple recipients of list ORACLE-L
create materialized view MY_MV
pctfree 10
pctused 80
tablespace &&medium_table_tbs
using index pctfree 5 tablespace &&medium_index_tbs
refresh fast
start with sysdate
-- every 30 minutes
next sysdate + 30 / 1440
as
select *
....
All is explained in the fine manual.
Jared
Thomas Jeff <ThomasJe_at_tce.com>
Sent by: root_at_fatcity.com
01/08/2003 02:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: 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: INET: Jared.Still_at_radisys.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). -- 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 Thu Jan 09 2003 - 07:43:46 CST
 |
 |