From abardeen1@yahoo.com Sat, 18 Aug 2001 06:24:31 -0700 From: "A. Bardeen" Date: Sat, 18 Aug 2001 06:24:31 -0700 Subject: Re: Substitute for Materialized Views/Snapshots? Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 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 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@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@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@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@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@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@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).