PL/SQL :: Materialized View Log Used As Change Data Capture
Jan 31, 2013
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.
View 11 Replies
ADVERTISEMENT
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
[code]....
3) I can see deltas on MV logs.
UNQ_IDITRTN_NBSEQUENCE$$SNAPTIME$$DMLTYPE$$OLD_NEW$$CHANGE_VECTOR$$XID$$
{FDE35AAC-E749-4507-972F-F26F5982E046}121/1/4000UU28002.81485714140869E15
{FDE35AAC-E749-4507-972F-F26F5982E046}131/1/4000UN28002.81485714140869E15
{FDE35AAC-E749-4507-972F-F26F5982E046}181/1/4000DO00002.81491297598354E15
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.
View 11 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Mar 11, 2013
oracle 11.2.0.3 I have insert only tables that receive srecords from multiple processes at a rate of about 200/second. Each transaction can have up to 100 records. I have another set of processes that queries this table for the latest data. These processes run anywhere from once a minute to once an hour. Processes do not get all of the data. They get data based on a type field.
Both of these are from java middle tiers. The process that queries data (The subscriber) does so at the request of many remote servers (there will be vast numbers). I am not allowed to expose these downstream databases to the internet (they are not oracle DBs anyway) so I cannot use streams or golden gate
So basicallyInsert Process: multiple sessions that combined insert records up to 200/second. There will be between 1-100 records per commit.
Query Process: Downtream process makes a request to my middle tier. This middle tier runs a query to get the latest data and passes it back. This design is set and I cannot change it.
1. right now we capture the insert time of the record. However, at this rate of inserts some processes will commit faster than others. So I cant use a 'greater than my insert time' query.
2. streams/golden gate won't work. can't register these DBs.
3. don't want to serialize my inserts because since I am not sure I can keep up with the insert rate. I don't even know what the specs will be for the production hardware. I have to actually deliver this before its decided. So I am being conservative.
4. I really want to avoid updates on this table if possible. In part due to my limited ability to test.
5. due to the number of downstream processes it is possible that it will request data and for some reason fail to insert the data locally. So the downstream application will keep track of the latest data it received. This means that a subscriber may need to request the same data again.
Is there a way to set up change data capture with multiple subscribers to handle this? if my subscribers are just queries? All the queries come from the same servers(there will be several, but all the same thing). If so, when I performance test this are there any wait issues I should keep an eye on?
View 1 Replies
View Related
Apr 14, 2011
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.
So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
View 4 Replies
View Related
Nov 15, 2012
I have got a simple materialized view question.
I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.
My question is how can I use the materialized view and it's data from a query.
View 2 Replies
View Related
Nov 5, 2008
How I can create a Materialized View without having any data in it.
For e.g.
I create a Materialialized View based on a View.
CREATE MATERIALIZED VIEW test_mv
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_view
In the above case the data fetched by the view test_view gets stored in the Materialized View test_mv. Suppose I want materialized view test_mv to get created with all the columns of test_view but not the data. I will refresh the materialized view test_mv later for data as and when required.
What shall I do for immidiate formation of materialized view test_mv without data.
View 4 Replies
View Related
Feb 29, 2012
1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.
2.Will materialized views can be created in Fast mode for the above scenario?
View 4 Replies
View Related
Jun 12, 2013
I have created a materialized view and also a normal View, which has 3 tables used in both the views, when inserted new records it reflects in a normal view but when i select the materialized view i cant see the updated data.
here is the materialized view i created;
CREATE MATERIALIZED VIEW pct_sales_materialized
BUILD IMMEDIATE REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT A.DEP_NAME,B.EMP_ID,C.EMP_NAME
FROM department_head A,department_child B,emp_detail C
WHERE A.DEP_ID = B.DEP_ID
AND B.EMP_ID = C.EMP_ID
View 2 Replies
View Related
Apr 14, 2011
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
View 1 Replies
View Related
Feb 14, 2012
I have created a MV on Oracle 10gR2 using dblink. Source database is AS400 DB2.MV script:
CREATE MATERIALIZED VIEW MV_BU
TABLESPACE TB_XXX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
[code]...
Above query should be refreshing the data every minute but I realized that its not doing it.
View 1 Replies
View Related
Jul 9, 2012
I have two servers:
10g server 10.1.4.30
11g server 10.1.4.32
I have a table testing_mview on 10g which do not have any primary key. I have created MV log for it:
CREATE MATERIALIZED VIEW LOG ON testing_mview WITH ROWID;
Materialized view log created.
My requirement is
1) if there is an UPDATE/DELETE/INSERT on testing_mview, it should be writen to MATERIALIZED VIEW LOG (this has been achieved)
2) Materialized view testing_mview1 of 11g on server 10.1.4.32 should pull these changes on a scheduled basis ( I have created the database link ORCL10R2 here from 11g to 10 g)
On 11g:
SQL> create database link ORCL10R2 connect to omig identified by pswd using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ICS3)
)
)';
Database link created
SQL> create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2;
create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object SYS.DBMS_SNAPSHOT@ORCL10R2
ORA-00604: error occurred at recursive SQL level 2
ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [55916]
ORA-02063: preceding 2 lines from ORCL10R2
View 3 Replies
View Related
Sep 23, 2013
is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies
View Related
Nov 15, 2012
Is there any way I can capture on change event for the Select List (already created on the page) using java script and then refresh chart region?
View 2 Replies
View Related
Dec 11, 2012
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?
View 5 Replies
View Related
Jul 19, 2010
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.
View 19 Replies
View Related
May 2, 2012
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).
View 3 Replies
View Related
Apr 25, 2012
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
View 6 Replies
View Related
Apr 26, 2011
difference between view and materialized view?
View 1 Replies
View Related
Aug 12, 2013
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?
View 7 Replies
View Related
Jul 7, 2010
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.
View 1 Replies
View Related
Feb 7, 2013
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?
View 3 Replies
View Related
Jul 14, 2011
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
View 3 Replies
View Related
Sep 10, 2010
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.
View 6 Replies
View Related
Dec 11, 2008
I need redefine a Materialized View (add some columns). I try it with:
drop materialized view TCPIP preserve table;
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
and than create a new MV:
CREATE MATERIALIZED VIEW TCPIP
ON PREBUILT TABLE USING NO INDEX NEVER REFRESH ENABLE QUERY REWRITE as
SELECT ...
But with Oracle error: ORA-12060: shape of prebuilt table does not match definition query
I check the new MV definition, it is contain the 2 added columns.
View 6 Replies
View Related
Mar 16, 2011
Why we can create indexes only on materialized view and not on normal views?
View 8 Replies
View Related
Sep 9, 2010
Can u explain the syntax of materialized view??????
View 1 Replies
View Related
Oct 20, 2010
How can we find how much space is used by the materialized view.
View 1 Replies
View Related
Jul 5, 2011
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?
View 8 Replies
View Related
Oct 26, 2012
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?
View 4 Replies
View Related
