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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/08/2003 02:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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 <[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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
