Re: Substitute for Materialized Views/Snapshots?

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