Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Substitute for Materialized Views/Snapshots?

Re: Substitute for Materialized Views/Snapshots?

From: Chris Rezek <crezek_at_dotclick.com>
Date: Mon, 20 Aug 2001 15:06:36 -0700
Message-ID: <F001.00371A87.20010820152030@fatcity.com>

The error is:

SQL> create materialized view test_age as select * from age;

ORA-12028: snapshot type is not supported by master site

Chris Rezek wrote:
>
> Anita,
>
> I would be a very happy man today if snapshots existed in SE. However,
> I have verified the non-existence of snapshots in SE by:
>
> (1) running basic create statements and having them fail
>
> and
>
> (2) filing an iTAR about the existence of snapshots/MVs in SE and
> receiving an answer of 'no' and I replied with 'are you sure not even
> read-only MVs?' and received a negative answer again.
>
> I wish things were otherwise. I am going to use the DBMS_JOBS
> scheduling to mimic MV behavior. I'll let y'all know now it turns out.
>
> Chris
>
> "A. Bardeen" wrote:
> >
> > Chris,
> >
> > I'm confused by your statement "there is no
> > materialized view functionality for SE" as this is
> > completely incorrect.
> >
> > Read-only snapshots (called materialized views in 8i)
> > have ALWAYS been available with Standard Edition
> > (well, at least since 7.3) as they're considered Basic
> > Replication. Note: 112591.1 clearly states that Basic
> > Replication (which now includes updatable MV's) is
> > available with SE.
> >
> > Per note: 112591 "Oracle8i server fully supports
> > bi-directional replication with automated conflict
> > detection and resolution. Supported configurations
> > include a single updatable master site with multiple
> > updatable or read-only snapshot sites."
> >
> > The only materialized view functionality that is not
> > supported in standard edition is the advanced data
> > warehousing features as I mentioned before (e.g.
> > summary management).
> >
> > I've set up updatable-MV environments in 8i using only
> > SE for both the master and MV sites, so I know that it
> > is possible and supported.
> >
> > I don't know of any way to have mulitiple DDL
> > statements or transactions maintain read consistency
> > to the same point-in-time as the first statement. The
> > "set transaction isolation level serializable" command
> > doesn't apply to DDL and is released as soon as a
> > commit or a rollback is issued. So the implicit
> > commit in a CTAS wouldn't work.
> >
> > I suppose you could mimic this by deleting all the
> > rows in the 5 tables and then doing an insert...select
> > statement to populate them, but depending on the size
> > of the tables you'd need a fairly large RBS on the
> > target site and the RBS's on the source site would
> > need to be properly sized to avoid ORA-1555 "snapshot
> > too old" errors.
> >
> > I still don't see why you think read-only
> > snapshots/MV's are not an option. Have you tried to
> > create one and gotten an error? If so, what's the
> > error message? Perhaps it has nothing to do with the
> > fact that you're running SE.
> >
> > HTH,
> >
> > -- Anita
> >
> > --- Chris Rezek <crezek_at_dotclick.com> wrote:
> > > Anita - I checked 112591.1 and also filed an iTAR to
> > > confirm with Oracle
> > > - there is no materialized view functionality for
> > > SE.
> > >
> > > Chris - Alas, transportable tablespaces are not
> > > available for SE either.
> > >
> > > I'm still looking for suggestions. The tables need
> > > to be
> > > read-consistent down to the second. Is there a way
> > > I can do a CREATE AS
> > > SELECT or COPY for all five tables in a way that
> > > ensures this?
> > >
> > > Chris
> > >
> > >
> > > "A. Bardeen" wrote:
> > > >
> > > > Chris,
> > > >
> > > > Read-only and updatable snapshots/materialized
> > > views
> > > > are available with the Standard Edition starting
> > > with
> > > > 8.1.5; SE can also be used for the master site for
> > > > snapshots/MV's. EE is required only for
> > > multi-master
> > > > replication and the data warehousing features of
> > > MV's
> > > > (e.g. summary management, etc...)
> > > >
> > > > The following metalink doc has the differences:
> > > >
> > > > Note: 112591.1 "Differences Between Standard
> > > Edition
> > > > and Enterprise Edition in 8i"
> > > >
> > > > HTH,
> > > >
> > > > -- Anita
> > > >
> > > > --- Chris Rezek <crezek_at_dotclick.com> wrote:
> > > > > We are running 8.1.6 Standard Edition on Solaris
> > > > > hardware. We are
> > > > > building a reporting system and would have liked
> > > to
> > > > > use materialized
> > > > > views (snapshots) but this feature is not
> > > available
> > > > > in Standard
> > > > > Edition. We need to create read-consistent
> > > copies
> > > > > of several
> > > > > million-row tables each night at midnight. How
> > > can
> > > > > we do this without
> > > > > Oracle's auto-updating materialized views?
> > > > >
> > > > > Chris Rezek
> > > > > Oracle DBA
> > > > > http://www.dmn.com
> > > > > --
> > > > > Please see the official ORACLE-L FAQ:
> > > > > http://www.orafaq.com
> > > > > --
> > > > > Author: Chris Rezek
> > > > > INET: crezek_at_dotclick.com
> > > > >
> > > > > Fat City Network Services -- (858) 538-5051
> > > FAX:
> > > > > (858) 538-5051
> > > > > San Diego, California -- Public Internet
> > > > > access / Mailing Lists
> > > > >
> > > >
> > >
> > --------------------------------------------------------------------
> > > > > 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).
> > > >
> > > > __________________________________________________
> > > > Do You Yahoo!?
> > > > Make international calls for as low as $.04/minute
> > > with Yahoo! Messenger
> > > > http://phonecard.yahoo.com/
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Chris Rezek
> > > INET: crezek_at_dotclick.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> > --------------------------------------------------------------------
> > > 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).
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Make international calls for as low as $.04/minute with Yahoo! Messenger
> > http://phonecard.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: A. Bardeen
> > INET: abardeen1_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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.com
> --
> Author: Chris Rezek
> INET: crezek_at_dotclick.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Chris Rezek
  INET: crezek_at_dotclick.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Mon Aug 20 2001 - 17:06:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US