SQL & PL/SQL :: Materialized View - How To Capture / Convert MV Log Deltas To Real Rows
Jun 27, 2013
We have a requirement where we need to publish only the delta/changes made to an existing materialized view. This MV is fast refreshed over a db link. Since, MV will have all the data, we won't
1] Created MV Log
CREATE MATERIALIZED VIEW LOG ON SHRTS_FLNG_PRCSD
WITH PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES;
2] Create MV
CREATE MATERIALIZED VIEW MV_SHRTS_FLNG_PRCSD (ITRTN_NB,UNQ_ID,XCHNG_ORG_ID,FLNG_ST,MAX_PRCSD_ITRTN_NB,SBMTD_SCRTS_AM,SBMTD_PSTN_AM)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
4) I have created a temp table to capture the diff between MV and newly inserted Deltas. I have added SEQ_NB column using Oracle sequence to capture the diff
CREATE TABLE MKT_CMPLC.TMP_SHRTS_FLNG_PRCSD
(
ITRTN_NB NUMBER(2) NOT NULL,
UNQ_ID CHAR(38 BYTE) NOT NULL,
XCHNG_ORG_ID NUMBER(8) NOT NULL,
FLNG_ST VARCHAR2(9 BYTE),
[code]....
5) I have refreshed the MV
exec dbms_mview.refresh('MV_SHRTS_FLNG_PRCSD');
6) In order to capture the new inserts, I am using following DML
INSERT INTO tmp_SHRTS_FLNG_PRCSD SELECT ITRTN_NB,
UNQ_ID,
XCHNG_ORG_ID,
FLNG_ST,
MAX_PRCSD_ITRTN_NB,
SBMTD_SCRTS_AM,
[code]....
We need only delta records. Is there any way to extract actual rows from MV logs.
I have big source tables to load in a data warehouse. We are in a full Oracle environment. So I need to extract only delta since the last extract.
I need to capture even deleted rows from the source table.
I have tested the following solution:
- declare a materialized view log on the source table - load the content on this view log in my ODS - empty this view log - load my DWH with the captured delta
It is very simple and seems to work perfectly. I am just confused by the fact that nobody seems to have implemented such a solution.
I have to convert some existing materialized views (fast refresh) to partition materialized views.
Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.
1) stop replication 1) create interim tables with similar structure as the materialized views 2) transfer all data from the materialized views to the interim tables 4) script out the materialized views structure and add in on prebuilt table option in the scripts 5) drop the materialized views 6) rename the interim tables with the same name as the materialized views 7) run the scripts to create the materialized views with on prebuilt table option 8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option
But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.
Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?
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?
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).
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
I have 11gR2 GI installed on two nodes. I am trying to convert a 10g single instance (uses ASM) database to RAC and getting this error. I am trying using 12c as well as manually by using rconfig.
[main] [21:19:15:145] [ASMInstance.initialize:135] First record =[Ljava.lang.String;@958bb8 java.lang.NullPointerException at oracle.sysman.assistants.rconfig.engine.ASMInstance.initialize(ASMInstance.java:153) at oracle.sysman.assistants.rconfig.engine.Step.execute(Step.java:283)
I have requirement to create materialized views. The design states to use Complete refresh. Now I am using WITH ROW_ID clause.
1. Question - what are the criteria to decide between WITH PRIMARY KEY & WITH ROW_ID clauses? I referred oracle doc, but couldn't exactly get this.
2. Background - The oracle doc mentions that - Primary key materialized views are the default type of materialized view. The master table must contain an enabled primary key constraint, and the defining query of the materialized view must specify all of the primary key columns directly.
Question - I saw some existing materialized views in my project using WITH PRIMARY KEY clause, but all of the primary key columns are not part of the Select query, but still these views are working fine, how is this possible?
3. Background - The oracle doc mentions that - Rowid materialized views must be based on a single table and cannot contain any of the following:
■Distinct or aggregate functions ■GROUP BY or CONNECT BY clauses ■Subqueries ■Joins ■Set operations
Question - But I have created a Rowid materialized view selecting data from more than one table & defining query involving outer joins. This materialized view got created & getting refreshed (complete refresh). But again this is contradicting with Oracle' statement.
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 have created FAST refresh materialized view.it is eligible for FAST refresh because when i executed SELECT * FROM USER_MVIEWS i could see FAST_REFRESHABLE=DIRLOAD_DML and STALENESS=STALE and it is not getting refreshed(through DBA_JOBS). Why it is in STALE status and how to resolve it. and one of my materialized views is in NEEDS_COMPILE status
A scheduler job is there to refresh some materialized views. All the views are run under single job name. Some mviews are not refreshing and those are not refreshing in the next run too.if we run those mviews manually then they are working fine from then . after some days those views which i told before are not refreshing and this happens frequently and i check the job_run_details views also and i am not seeing any errors in that.
I have created a Materialized View (MV) and within the MV DDL have included a statement as per below. However, the MV does not seem to be refreshing on a daily basis. I can see the job in dba_jobs table. So the question, is there some system setting i need to execute or create in order for Oracle to know that it needs to run the Job defined in the dba_jobs table on a daily Basis?
I have created materialized view which hold few million records.Should i have to analyze the view and compute the statistics after i create the materialized view?
Also, just in case i need further indexing, should i have to take the statistics for the table again?
I am running into an issue and trying to ascertain issue. Scenario:I have 2 MV creation scripts. The MV is supposed to get populated by connection from schema to another schema USING DB Links.Basically, SAME HOST, SAME RAC DATABASE, just separate schemas. The MV creations are just hanging. I see NO alert log mentions. I ran a SQL trace and yes,
I see: call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 0 0.00 0.00 0 0 0 0Execute 1011 1.40 73.80 0 0 0 0Fetch 1010 1.08 317.57 0 0 0 1010------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2021 2.49 391.38 0 0 0 1010 Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 109 (recursive depth: 2) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to dblink 2022 0.00 0.00 SQL*Net message from dblink 2021 0.46 242.58
Understandable, but when I do a selective query, the results come back pretty much within 5 seconds.DB version is 11.2.0.3. Is there a BUG that I should know about?
Here is a snippet:
CREATE MATERIALIZED VIEW "SCHEMA"."MV_NAME_MV" USING INDEX REFRESH COMPLETE ON DEMAND AS (SELECT distinct mapguide_persons(pl.location_code) "FULL_NAME_COSTCENTER",mapguide_jobemp(pl.location_code) "TRUNCNAME_JOB",mapguide_empnum(pl.location_code) "FULLNAME_EMPNUMBER_PHONE",mapguide_english(pl.location_code)
some of MVs become invalid (yes, we did app upgrade over the weekend). I looked on every object reference in the MV, all permissions - can't find anything.I dropped MVs and created them no errors. Status changed to VALID. I decided to re-COMPILE them (because I saw public synonyms for MVs as INVALID). After compilation status changed back to INVALID.Oracle 11.2, Solaris 10.
I have got a materialized view which is created over a dblink as below:
CREATE MATERIALIZED VIEW CRIMEREPORTODSV TABLESPACE ODS_DATA BUILD IMMEDIATE REFRESH FAST AS (SELECT * FROM CRIME_INT@CRISP);
This is all fine and works apart from any changes to the source table (CRIME_INT) isn't reflected unless I perform a refresh. Whereas I want any changes to be reflected straight away. I have had to use a Materialized view in this case as one of the columns in the source table is a CLOB and Oracle won't allow creation of a view with a CLOB field over DBLINK.
I created a materialized view which takes around 6 hrs to refresh. way to reduce the refresh time.The query is:
create materialized view ERC_DAILY_DISBURSMENT_MIS_VIEW refresh complete on demand as with disb_qur as (select e.account_no, e.journal_dt, e.instr_amt disbursed_amt,
In Oracle 10gR2.If a materialized view uses a database link for the query in order to create a snapshot of data on a remote instance, does the name of the database link have to be an entry in the tnsnames.ora file?
The following link suggests not, but is not version specific: Materialized View - Oracle Wiki FAQ
However, the following 11g documentation suggests that the database link name must be the same as the global name of the target database.URL.. I can't any info specific to 10gR2.
We have three instances. Our application metadata is stored in a schema in B.METADATA. There is a shell schema (B.METADATASHLL) that provides access to the tables to remote applications.
On instance A, we have a shell schema (A.METADATASHLL) that creates materialized views as follows: CREATE DATABASE LINK METADATA_PRIME CONNECT TO metadatashll IDENTIFIED BY password USING 'B';
Our materialized view is created using the query SELECT * FROM METADATA.APPLICATIONS@METADATA_PRIME WHERE Application = 'A';
The query works, but the materialized view does not.I'm being told that the database link has to be named B as follows
CREATE DATABASE LINK B CONNECT TO metadatashll IDENTIFIED BY password USING 'B';
Which means that I can only have one public link to instance B, and I'm in a pickle if I create a second application (which I have) using the same model).
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?
I am new to Oracle and I inherited an existing database. I have a materialized view on a table that uses a materialized view log. I have been asked to see if it is possible to categories the rows by change type (inserted, updated or deleted) and populate another materialized view or table accordingly. If this is possible then how can it be achieved.
i have created a fast refresh materialized view but it is not performing fast refresh or even complete refresh.Then i have checked my materialized view capabilities then in mv_capabilities_table i got a message that materialized view log is newer than old .