-
MVIEW on commit refresh condition
Hi,
I need to have an up to date copy of a table in another environment. In the master database I created a materialized view log on the master table:
CREATE MATERIALIZED VIEW LOG ON TIPS.POSITIONS WITH ROWID INCLUDING NEW VALUES;
Actually all applications use a DBLINK to access the positions table in the remote database. I tried to create a snapshot to have better performance:
CREATE MATERIALIZED VIEW MV_POSITIONS
REFRESH FAST ON COMMIT
AS
SELECT *
FROM POSITIONS
An I got the error:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
What are those conditions?
I read in the oracle documentation about on commit refresh:
Restriction: This clause is supported only for materialized join views and single-table materialized aggregate views
Does this mean I can't do a on commit fast refresh on a single table??
Thanks
DONTBSHY
-
Did you create a materialized view log for the underlying table?
Did you try REFRESH FORCE ?
MH
I remember when this place was cool.
-
Hi,
Yes I tried REFRESH FORCE and I got the same error. By :
Originally posted by Mr.Hanky
Did you create a materialized view log for the underlying table?
Did you try REFRESH FORCE ?
MH
You're talking about which table? I created a log on the master table and I'm trying to create an on commit refresh snapshot for this table.
Thanks
DONTBSHY
-
I'm not sure but do you need to specify...
CREATE MATERIALIZED VIEW MV_POSITIONS
REFRESH FAST ON COMMIT
AS
SELECT *
FROM POSITIONS
BUILD IMMEDIATE;
after the from positions?
MH
I remember when this place was cool.
-
This doesn't work.. The documentaion doesn't mention it...
I'm still looking for those condition!
DONTBSHY
-
Hi,
I got the answer for my problem:
8.1.6 Data Warehousing Guide (8-20) : ON COMMIT : Can be used with
materialized views on single table aggregates and materialized views
containing joins only.
9.0.1 Data Warehousing Guide (8-26) : ON COMMIT : Can be specified as long as the materialized view is fast refreshable (in other words not complex).
The query you listed is a single table non-aggregate and should thus
be able to be ON COMMIT refreshed in 9.x but not in 8.1.x.
I'm using 8.1.7.4... Dammit !
DONTBSHY
-
Oh well, glad you found it.
MH
I remember when this place was cool.
-
if uses view materialized is necessary to set something in the configuration archive it Oracle?
Márcio de Souza Almeida
DBA Oracle / SQLServer / PostgreSQL
Rua Cupa, 139 Apto 85 A
Penha - São Paulo - SP
03640-000 - Brasil
http://www.directory.com.br
-
SQL> CREATE MATERIALIZED VIEW vw_teste
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 as
5 SELECT codigo, nome
6 FROM marcio.teste@pd00
7 ;
SELECT codigo, nome
*
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
SQL> CREATE MATERIALIZED VIEW vw_teste
2 PCTFREE 0 TABLESPACE usr
3 BUILD IMMEDIATE
4 REFRESH COMPLETE ON DEMAND
5 AS
6 SELECT codigo, nome
7 FROM marcio.teste@pd00
8 ;
Materialized view created.
BUT do not refresh
Last edited by marcio.68; 05-12-2003 at 12:53 PM.
Márcio de Souza Almeida
DBA Oracle / SQLServer / PostgreSQL
Rua Cupa, 139 Apto 85 A
Penha - São Paulo - SP
03640-000 - Brasil
http://www.directory.com.br
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|