SQL & PL/SQL :: Store Data From Multiple Places In Materialized View?
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:
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.
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.
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 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
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
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?
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 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.
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?
Need a trigger in view with select statement that means
CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE; CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1 INSTEAD OF DELETE ON TEST_VIEW DECLARE BEGIN Dbms_Output.Put_Line('STATEMENT TRIGGER.'); END;
i wanted to use select statement instead of delete.How can i get that
create or replace PROCEDURE INSERT_TESTTABLE ( PrimaryKey IN NUMBER ,One IN VARCHAR2 ,Two IN VARCHAR2 ,Three IN VARCHAR2 ,Four IN VARCHAR2 [code].......
And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist
I am having many different files and i want to use pure plsql to get these files stored as blob from a table then compress them into 1 files and store that into another table. I did some search and its like possible but didnt get conclusive solution
i am trying to update multiple records using store procedure but failed to achieve
for example my source is
emp_name sal abhi 2000 arti 1500 priya 1700
i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only
i have tried following code
create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor) is begin open p_cursor for select sal into p_sal from emp; if sal<2000 then update emp set sal= sal+200; end i;f end;
and i have called the procedure using following codes
set serveroutput on declare p_sal emp.sal%type; v_cursor sys_refcursor; begin upt_sal(p_sal,v_cursor); fetch v_cursor into p_sal; dbms_output.put_line(p_sal); end;
the program is executing but i should get o/p like this after updating
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?