hi
materialized views become invalid as soon as a complete or fast refresh of the mv is done. is this how it is supposed to work?
the status is invalid in dba_objects and is set to 'N' in dba_mview_analysis. i have been struggling to understand this. i read thru few docs on metalink
Can anyone confirm whether or not a materialized view in 8.1.7 EE can be
based upon a snapshot within the same schema?
SunOS 5.6 - 8.1.7.0.0 EE
_
Get a speedy connection with MSN Broadband. Join now!
http://resourcecenter.msn.co
Hi List,
I am having trouble replicating a schema onto another database. I have
looked at the Oracle documentation and am having a hard time deciding which
steps to take in accomplishing this task. The schema includes tables
indexes, packages, and triggers.
I have had success in creating a mat
Hi,
We need to create the materialized view on query like:
select * from table1
union all
select * from table2
union all
select * from tabl3
we r getting error set operation not allows.
Is there any other way to create the materialized views based on union all
queries???
Thanks
-Harvinder
I've been expirementing with MV for a couple of days. Interesting gotcha's for fast
refresh on
commit, but otherwise cool stuff. Now for the question.
If I have a base table of 1 millions rows of time series data and I want to produce
MVs for day,
month, and year aggregates, would it be bette
Sai,
You may want to check bug# 1188948; it sounds like your
case.
HTH.
Arup
- Original Message -
From:
Sai
Selvaganesan
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, August 26, 2003 9:04
PM
Subject: materialized views
hi
Gary - I seem to recall a restriction based on the refresh scheme. Something
like if the underlying materialized view/snapshot has a refresh log, then
the new materialized view can't be fast refreshed, or something like that.
Other than that, maybe the best approach is to just try it.
Dennis Willi
Dear list,
Have any of you every experienced MV's not refreshing
for no particular reason?
We have been using some simple MV's for several months
with no problem. Now after upgrading our app and database,
there seem to be problems.
Servers:
Master: Win2k SP2 Oracle 8.1.7.4.1
Slave: NT 4
Title: Materialized views in RBO
Our production database runs in RBO. We are evaluating the option of using Materialized views. Oracle manuals says that even if the parameter OPTIMIZER_MODE is set to RULE, the use of these features enables the CBO. Has anyone used this feature in RBO?
We
iginal Message -
From: Sai Selvaganesan
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, August 26, 2003 9:04 PM
Subject: materialized views
hi
materialized views become invalid as soon as a complete or fast refresh of the mv is done. is this how it is supposed to work?
the status is i
>Dear list,
>
>Have any of you every experienced MV's not
>refreshing
>for no particular reason?
>
>We have been using some simple MV's for several
>months
>with no problem. Now after upgrading our app and
>database,
>there seem to be problems.
>
>Servers:
>
>Master: Win2k SP2 Oracle 8.1.7.4.
Probably a little obvious, but what do you get when
you slap a trace on the refresh job (on both source
and target db's) - do you see any attempt to read rows
from the mview log at all? any updates being attempted
on the target?
hth
connor
--- Jared Still <[EMAIL PROTECTED]> wrote: >
> Dear lis
You're right, it is obvious, but I hadn't yet done it.
The thought had occurred to me to do this at both nodes,
but I think lack of sleep has dulled my senses a bit and I
forgot to do it. All the work involved in repeating myself
in the TAR maybe, I dunno.
I've dropped and recreated the offen
No errors, no trace files. Refreshing via refresh group or
directly via the snapshot both failed to update the MV.
They've since been recreated and are working at the moment.
I'll slap a big ole note on my forehead that says 'run a trace on
them stupid!' so I will remember to do so if these st
Is the job even firing at all? It is a common step in upgrading to set
job_queue_processes=0 and this will prevent materialized views from refreshing
automatically. Make sure this is greater than 0; I normally set it to 2.
>>> [EMAIL PROTECTED] 02/25/03 04:29AM >>>
>Dear
cc:
Sent by: rootSubject: Re: Materialized views not
re
Jared,
Does it work if you do a manual refresh?
Henry
-Original Message-
Sent: Tuesday, February 25, 2003 3:49 AM
To: Multiple recipients of list ORACLE-L
Dear list,
Have any of you every experienced MV's not refreshing
for no particular reason?
We have been using some simple MV's f
Didn't help to do it manually.
On Tuesday 25 February 2003 06:14, Henry Poras wrote:
> Jared,
> Does it work if you do a manual refresh?
>
> Henry
>
> -Original Message-
> Sent: Tuesday, February 25, 2003 3:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Dear list,
>
> Have any
Yes, the jobs were firing, verified by timestamps in both
the dba_jobs and dba_refresh views.
On Tuesday 25 February 2003 05:33, Darrell Landrum wrote:
> Is the job even firing at all? It is a common step in upgrading to set
> job_queue_processes=0 and this will prevent materialized view
Jared Still
>of list ORACLE-L <[EMAIL PROTECTED]> @cybcon.com> cc:
> Sent by: root Subject: Re: Materialized
> views not refreshing
>
>
> 02/25/2003 06:49
>
l? It is a common step in upgrading
> to set
> > job_queue_processes=0 and this will prevent materialized views from
> > refreshing automatically. Make sure this is greater than 0; I
> normally set
> > it to 2.
> >
> > >>> [EMAIL PROTECTED] 02/2
TED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: Materialized views not refreshing
Date: Tue, 25 Feb 2003 11:39:09 -0800
No, they were not being purged.
On Tuesday 25 February 2003 05:59, Thomas Day wrote:
> We had the problem wher
rading to set
> > job_queue_processes=0 and this will prevent materialized views from
> > refreshing automatically. Make sure this is greater than 0; I normally
set
> > it to 2.
> >
> > >>> [EMAIL PROTECTED] 02/25/03 04:29AM >>>
> > >
>
> cc:
Sent by: rootSubject: Re: Materialized views not
refresh
bject:Re: Materialized views not refreshing
Jared -
I assume you're attempting fast refreshes - Is it feasible to attempt a
full refresh?
I remember problems w/ snapshots (in the 8.0.4 days) where the snapshot
logs would just stop working. I had to perform a full refresh of the
sna
Subject: Re: Materialized views not refreshing
Jared,
I know this may sound stupid, but when you upgrade you change
job_queue_processes to zero. Did you reset your init parameter back after
the upgrade? I have seen that cause this problem before.
Hope it is this simple
a" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/25/2003 12:54 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re: Materialized views not refreshing
Jared,
I joined this thread late; s
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
We've noticed that our fast refresh takes longer than complete refresh - and
that's puzzling .We have MV logs based on the PK of the FACT table - and the
MV's are JOIN ONLY .
Any suggestions would be welcome !
vikas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas
Different names for the same thing. Snapshots is being phased out and the
new terminology is materialized views.
Todd Carlson
Isn't that just like Oracle... Shesh
Todd
-Original Message-
Kevin M
Sent: Thursday, March 14, 2002 10:34 AM
To: Multiple recipients of list ORACLE-L
materialized views are snapshots
snapshots are materialized views.
Any questions?
-Original Message-
Sent: Thursday, Mar
pients of list ORACLE-L
materialized views are snapshots
snapshots are materialized views.
Any questions?
-Original Message-
Sent: Thursday, March 14, 2002 10:24 AM
To: Multiple recipients of list ORACLE-L
Hello All,
Sun 5.8 Oracle 8.1.7.2
At a client site the developers are building a
It does explain why you can't find a good comparison though:-)
The package for materialized views is actually the snap shot
packages. Just a public synonym that makes it look different
(at least in 8i).
-Original Message-
Carlson
Sent: Thursday, March 14, 2002 9:25 AM
To: Mul
--- Amar Kumar Padhi <[EMAIL PROTECTED]> wrote:
>
> Our production database runs in RBO. We are
> evaluating the option of using
> Materialized views. Oracle manuals says that even if
> the parameter
> OPTIMIZER_MODE is set to RULE, the use of these
> features enables th
I have posed this question to Oracle support and have yet to receive an
understandable answer, so I thought it best to ask the experts.
I used to run under 8.1.5 with compatibility set to 8.0. Whenever I
created a snapshot I had an underlying table snap$_ that was the physical
table residing in
Hi,
I have just created a materialized view on 3 base tables involving only
join condition.
But oracle is not using this MV when resolving this query.
Schema is analyzed with compute.
query_rewrite_enable=true.
What can be the possible cause.
Thanks
-Harvinder
create MATERIALIZED view t_vw_
Has anyone created indexes on MV's ? Syntax? URLs ?
thanks
vikas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet
Substitute for Materialized Views/Snapshots?
>
>
> 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
Two thoughts.
1) buy the Enterprise edition
2) export the tables from the database and then load them in.
> -Original Message-
> From: Chris Rezek [mailto:[EMAIL PROTECTED]]
> Sent: Monday, August 13, 2001 5:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Sub
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.
econd. 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; S
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
I ordinarily wouldn't correct a misspelling, but in this case, it matters:
it's "CONSISTENT", not "CONSISTANT".
Paul Baumgartel
MortgageSight Holdings, LLC
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, August 14, 2001 3:54 PM
To: Multiple recipients of list ORACLE-L
Chris,
If y
Thanks Paul,
I remember better that I type. The error message show me to look up the correct
spelling.
ROR mª¿ªm
>>> [EMAIL PROTECTED] 08/14/01 04:06PM >>>
I ordinarily wouldn't correct a misspelling, but in this case, it matters:
it's "CONSISTENT", not "CONSISTANT".
Paul Baumgartel
MortgageSi
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 consider
e:
>
> 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
>
t; 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
I don't know what to say. When I ran the exact SQL just now it worked -
no errors at all. So it seems that MATERIALIZED VIEWs *are* available
in SE. Very good news.
Thank you especially to Anita who kept insisting that the statement
should work.
Chris
Chris Rezek wrote:
>
> T
It may be that your fast refresh is actually doing a lot of work. This
would happen if many rows in the source table were changed. If 99% of the
rows changed, then a complete refresh will be faster, because you don't have
the added overhead of reading the logs to determine which rows to refresh
How do I find out if that is the case .The no of new inserts/updates is much
lesser than the total no of rows in the tables.So why would the fast refresh
take longer .
Can we index the MV logs to speed up the process.
vikas
-Original Message-
Sent: Tuesday, August 28, 2001 4:11 PM
To: Mu
Sherrie,
I'm afraid not. You might want to look into creating
your snapshots off of prebuilt tables. When the
snapshots are dropped the table is left so you could
perform DML on it. I haven't played around with them
enough yet to know if you'll need to do a complete
refresh if you drop the sna
Title: RE: Base table of materialized views
I have a few questions that you can answer with by post of the snapshot creation command. Also, the refresh type and any other relevant info. (i.e. snapshot log, etc.)
Tony Aponte
-Original Message-
From: [EMAIL PROTECTED]
[mailto
Yeah.. I know I am late...just catching up.
If you are still looking for some
workaround...*TEMPORARY* workaround is to use
DBMS_SNAPSHOT.SET_I_AM_A_REFRESH (TRUE) and then you
can do DMLs. But bewareall replication triggers
would be disabled when you set it to true. After you
are done set it
You can but make sure they are non-unique, since
Oracle does not give any guarantees of uniqueness
during a refresh
hth
connor
--- Vikas Kawatra <[EMAIL PROTECTED]> wrote: >
Has anyone created indexes on MV's ? Syntax? URLs ?
>
> thanks
> vikas
> --
> Please see the official ORACLE-L FAQ:
> ht
That restriction was lifted in Oracle8 since
deferrable constraints were introduced. Referential
integrity and unique constraints are allowed on
snapshots as long as they are created as deferrable
since, as Connor mentioned, the uniqueness cannot be
guaranteed during the refresh. Non-unique inde
Speaking of weird replication things... Has anyone
tried doing an standard ANALYZE command on the
RUPD$... table. (On my install, 8.1.6+) it says that
it worked but always gives null stats...Not important
- but hm
Connor
--- "A. Bardeen" <[EMAIL PROTECTED]> wrote: > That
restriction was lif
Anybody using MV`s thru a db link.
I am trying to create a MV that is updated on commit. I can create this view if I get
I keep it in the same db. I can get a MV that never will be refreshed working link.
( What good is that ?)
The query is below
Platform Irix 6.5 Oracle 8.1.6
Tia
J
You can't create materialized views with ON COMMIT across a dblink, sorry. They
didn't write the trigger to do remote procedure calls. You can set up Multimaster
replication with the Synchronous
option, but good luck :-). It puts you out of business if the communications link
goes
Title: using stored procedures from pro*c
Okay
guys after feeling like a purist and that NULLS should not be stuffed with
values just because - because of course maybe slight performance
issue, also is it really the same meaning? and wanting easy way to
build in consi
Title: using stored procedures from pro*c
Can
you send an example?
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Thursday, August 01,
2002 3:23 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: dware/materialized views/nulls/defaults
Hi All
I have a very large data set for a DW and I created a materialized view on
it to give me a quick access to group by and summarization results, I want
some way to be able to update the MV with just the delta after I perform an
incremental load to the base fact or dimension tables on which t
There were other problems which were masquerading as a refresh problem.
Merry Christmas and Happy New Year to all.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego
Ferenc Mantfeld wrote:
>
> Hi All
>
> I have a very large data set for a DW and I created a materialized view on
> it to give me a quick access to group by and summarization results, I want
> some way to be able to update the MV with just the delta after I perform an
> incremental load to the ba
<>
FM : Stephane, thanks for replying, as I am still not coming up with any
good ideas myself on this. The whole snapshot idea has not crossed my mind
until you mentioned it, but even with the snapshot logs, I would still need
to calculate the summaries and aggregates on the entire set of tables
64 matches
Mail list logo
