we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
we have the following requirement for creating materialized view.
In database "DB1", a table "abc" is there in "user1" schema. In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1: select * from user1.abc@DBL1; we have created a public synonym for "user1.abc@DBL1" as "sabc".
In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".
when i try to create a materialized view as below: create materialized view abc_mv as select * from sabc@DBL2;
But got the error as "synonym translation invalid". When i access the SQL statement separately as below select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.
Is there any option available for this type of creating materialized view?
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
I am trying to create a commit on refresh materialized view so that whenever I perform DML followed by commit on Master table it should get quickly reflect on my Materialized view. I have given SELECT on table privilege to user where this view will be created On Master:
GRANT SELECT ON HR.EMPLOYEES TO SCOTT. From Scott:CREATE MATERIALIZED VIEW scott.hremp REFRESH FAST ON COMMITAS SELECT * FROM HR.EMPLOYEES; ERROR at line 4:ORA-01031: insufficient privileges Then I Grant all on EMPLOYEES table to SCOTT. GRANT ALL ON hr.EMPLOYEES TO SCOTT; Then got following error while creating materilized view:SQL> CREATE MATERIALIZED VIEW scott.hremp 2 REFRESH FAST ON COMMIT 3 AS 4 SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-23413: table "HR"."EMPLOYEES" does not have a materialized view log I create materialized view log on Master:SQL> CREATE MATERIALIZED VIEW LOG on HR.EMPLOYEES;Materialized view log created.
Even after this I am not able to create materialized view and now getting Table not Exist error:
SQL> CREATE MATERIALIZED VIEW scott.hremp 2 REFRESH FAST ON COMMIT 3 AS 4 SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-12018: following error encountered during code generation for "SCOTT"."HREMP"ORA-00942: table or view does not exist. Also I want to know why I needed to GRANT ALL to HR for creating this materialized View.
I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.
I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.
i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:
CREATE OR REPLACE TRIGGER a after update on A@dirfep.us.oracle.com FOR EACH ROW BEGIN
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
* Error at line 1: ORA-12008: error in materialized view refresh path ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
Error:- ORA-02448: constraint does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429 ORA-06512: at line 1
The Primary Key Constraint on the base table/MV is deferrable, the base table and the MV are in different schemas and the user trying to refresh the MV has an explicit grant on "ALTER ANY MATERILIZED VIEW" and "SELECT ANY TABLE" through a role.
Its refreshing successfully when I make the PK constraint (Base Table, MV) NOT DEFERRABLE (or) when I explicitly grant "SELECT ANY TABLE" to the user trying to refresh the MV.
I have created a Table with one column in user 'Test' Database 'Test'. Then i have created a Materialized view based on the Table in 'TestMV' in same database 'Test'.working fine. But after adding one column in the table, if i go to refresh the Materialized view the following Error has occured.
ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-00913: too many values ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431 ORA-06512: at line 2
***************************************************************************** ORA-12008: error in materialized view refresh path ORA-14400: inserted partition key does not map to any partition ORA-06512: at "SYS.DBMS_REDEFINITION", line 50 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343 ORA-06512: at line 2 ********************************************************************************
I am unable to UNDERSTAND, why this error is appearing? I have created new tablespace, new user, new table, then, from where this MATERIALIZED view error is coming?
i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get
SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning
CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS SELECT b.column1, a.column2, c.column3
H want to Create a view which reside in one instance for example xxx Instance . I want to create the same view which reside in xxx instance to another instance for example YYY Instance How to acheive this ?I tried desc the View take the view script from XXX instance and copied the view script to YYY instance when I compiled Iam getting an Error
For Example My view in XXX Instance CREATE OR REPLACE FORCE VIEW xxx.sample ( First_name, last_name ) as select "F_NAME", "L_NAME" FROM EMP;
When try to copy and compile the same view in my YYY Instance i am getting error
We are facing serious problem while refreshing materialized views using fast refresh option in ORACLE..For the very first time we are performing Complete refresh of data from DB1 to DB2 for few tables.Ongoing we are performing Fast Refresh.Sometimes the fast refresh works fine without any error and sometimes it fails with the below error.
ERROR at line 1: ORA-32320: REFRESH FAST of "CIR"."C_BO_COMM" unsupported after cointainer table PMOPs ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841 ORA-06512: at line 1
let me know when will this error occur during Fast Refresh.
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
CREATE MATERIALIZED VIEW MV_NESTED_DATA NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE USING NO INDEX REFRESH COMPLETE ON DEMAND START WITH ROUND(SYSDATE) NEXT ROUND(SYSDATE) + 1 WITH ROWID AS select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
We are in the process of migrating our databases to a hosting provider (10g Sun -> 11g LINUX.) A (former) data architect at our business had this (nightmare) situation implemented:
- have materialized views created - after creation of mat views(and associated tables), add additional audit columns to the table that are populated by triggers, not by the MV. All data is important.
In order to get all of the data to the hosting provider, we data pump export the full schemas (which include the associated MV tables), have the provider DBA's import (all the materialized views failed on import, but the associated MV tables were created/populated), and I'm now attempting to fix the MV code to get them recreated. Over 100 MV's, most of which have these extra audit columns, and a number of the remote master/source tables do not have primary keys so using the by rowid option. An edited example (object names changed to protect the innocent) - using CAST to include the audit columns:
CREATE MATERIALIZED VIEW SCHEMA1.ACCOUNT ON PREBUILT TABLE USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 512K NEXT 512K PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE S_TS_01_INDX REFRESH FAST ON DEMAND WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE AS SELECT ACCOUNT.GROUP_ID GROUP_ID ... ... cast(null as date) CREATE_PROCESS_DATE, cast(null as varchar2(20 CHAR)) CREATE_PROCESS_ID FROM SCHEMA2.ACCOUNT@REMINSTANCE.WORLD ACCOUNT /
ORA-12058: materialized view cannot use prebuilt table
IF remove "with rowid", get error that cannot create because no primary key on source table.
Online options seem to be (1) do not use prebuilt table (in which case we'd lose the additional audit data) or (2) add a primary key on the master table (we're not in a timeline to make & test changes to various production source tables.)
Other thoughts on how to get this data migrated/populated? This needs to be a lift and drop as much as possible - any type of rewrite/restructuring is out of the question.
I have a view that includes sub queries in its select.
I am creating a MV over that view. and i want to fast refresh it when ever a commit is placed on the table (that is used in the from clause for the simple view)
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
When i try to create a trigger , i ended up with error.
SQL> create or replace 2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE 3 BEGIN 4 INSERT 5 INTO 6 user_audit_log 7 SELECT 8 user#,
[code]....
Warning: Trigger created with compilation errors.
SQL> SQL> show error Errors for TRIGGER LOGON_TRG: LINE/COL ERROR -------- ----------------------------------------------------------- 2/5 PL/SQL: SQL Statement ignored 17/17 PL/SQL: ORA-00942: table or view does not exist
The command used to resolve the error is
GRANT SELECT ON v_$session TO jack;
Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?
I am having problems with my procedure which is refreshing materialized views. This is the error i am getting : -12008: ORA-12008: error in materialized view refresh path ORA-01555: snapshot too old: rollback segment number 14...
I have two database with the same procedure and the same objects. They run at the same time, however, there are times when i get the error on the other database while the other database runs the procedure without any error.
When i checked the net, they say that this error is caused by data that is old, so the solution is to make the source a predecessor of my procedure/job. But what i am thinking is that how was it possible for the other database to run it completely even if it is not yet linked as the successor of the source?
