10g Materialized View Using A Database Link

May 20, 2009

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).

View 6 Replies


ADVERTISEMENT

PL/SQL :: Materialized View Link With All Options?

Dec 28, 2012

materialized view link with all options?

View 3 Replies View Related

Error In Creating Materialized View With DB Link?

Aug 7, 2013

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?

View 1 Replies View Related

Database Link To Fetch Values From View?

Dec 8, 2008

Here is the information about database I need to connect from my view, as the underlying table for my view is in this database

Database instance : orcl
Machine name : contentm (not sure, if this needs to be used)

so in my schema, I tried the following

CREATE DATABASE LINK product_lnk2 USING 'orcl'
select * from cont20.V_FB_PRODUCT_NM@product_lnk2

where cont20 is the schema on orcl

But it gives me this error ORA-12154: TNS:could not resolve the connect identifier specified

View 4 Replies View Related

Security :: View / Obtain Password Once Database Link Has Been Created?

Dec 24, 2011

Is there a way to view/obtain the password once a database link has been created. Some sys dba table?

View 7 Replies View Related

Materialized View Refresh Between 2 Database?

Jul 12, 2012

DB_VERSION=10.2.0.4
OS_VERSION=Windows 2008 R2
RAM_SIZE=32 GB

We have 2 production (OLTP) database( DB1 (18GB) and DB2 (200GB) ) on a server out of which DB2 database has almost 20 Materalized Views all (REFRESH FORCE ..ON DEMAND).

I have already informed the client that this is not the OLAP database where you are going to make use of the M Views for reporting purpose. However currently the client does not have any alternative.

Breif details :-
Out of 20 M Views 3 refresh once a week and where as the rest refresh every night daily.

We are currently testing a new website for which we had to re-schedule all M Views from their Old timings to New Timings i.e. Daily Basis.

But post re-scheduling the database performance was badly degarded as the database was too slow also the number of archives increasted to 300GB which was earlier 100 GB.To resolve the issue , we had to go back to the old timings and the database is currently working fine.

Requirement :-Client is looking out for an alternative solution as he needs to once again change the M View refresh time i.e. Daily Refresh.We already have 2 Oracle Instance on the server

Questions :-

1. Can i create another database ( sayTESTDB2 ) in no archivelog mode and create a db link to reduce the load on the server ?
2. If Yes .. Then is there any relavent link /example or Metalink Note ID for the same??
3. What all parameters do i need to consider while creating a DB Link ?
3. Out of 32 GB RAM , I have given 4GB and 8GB sga_max to DB1 and DB2 Respectively . Also the cpu utilization is normally 50-%60%.

Since currently we do not have any Licence for other server .. Can i make use of the same server ( i.e. creating 3 rd Oracle Instance )?

Since the main intention of this activity is reduce the load from the DB2 onto TESTDB when the daily refresh has set once again

View 1 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

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

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

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

Replication :: Setup Database Replication With Updatable Materialized View?

Sep 4, 2009

i need to set up a central server with all the master tables and two other local database which will hold the updatable materialized view of the master table...the databases must be synchronized with central server..and user will work on the materialized view database...

View 10 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

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

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

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

SQL & PL/SQL :: View To Find DB Link Type

Feb 21, 2012

Is there any view to find out the Types (Public or Private) of the DB LINK already created?

View 6 Replies View Related

SQL & PL/SQL :: Materialized View

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

Creating Materialized View Log

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

STALE Materialized View?

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

Materialized View Refresh?

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

SQL & PL/SQL :: Materialized View Redefinition

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

SQL & PL/SQL :: Indexes On Materialized View

Mar 16, 2011

Why we can create indexes only on materialized view and not on normal views?

View 8 Replies View Related

SQL & PL/SQL :: Syntax Of Materialized View?

Sep 9, 2010

Can u explain the syntax of materialized view??????

View 1 Replies View Related

SQL & PL/SQL :: How Much Space Is Used By The Materialized View

Oct 20, 2010

How can we find how much space is used by the materialized view.

View 1 Replies View Related

SQL & PL/SQL :: Oracle Materialized View

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

Analyzing Materialized View

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

PL/SQL :: Materialized View Creation?

Jun 15, 2013

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)

[code]....

View 7 Replies View Related

SQL & PL/SQL :: Materialized View Recreate

Oct 2, 2012

we have a materialized view (in Oracle 11g), usually this MV we easily drop and recreate it.

But last few days, i am trying to drop a MV, but running a long time but no result and it keeps running. not dropped also.

Bcos, db performance is good since we tried to a view using script, it is fine. Problem seems only with Materialized view.

where and how to check what is the issue?

View 1 Replies View Related

Invalid Materialized View?

Aug 20, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Materialized View Over DBLink

May 7, 2010

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.

View 4 Replies View Related

Materialized View Refresh Time?

May 24, 2011

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,

[code]....

View 1 Replies View Related

Pipe Function And Materialized View

Feb 27, 2011

Pipe Function and Materialized View.

There is a materialized view:

CODECREATE MATERIALIZED VIEW MY_MAT_VIEW NOLOGGING
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE + 5/24/60
AS
SELECT F1,
F2,
F3
from SOMETAB;

And there is a pipelined function:

CODEDROP TYPE MY_MAT_VIEW_table_type;
DROP TYPE MY_MAT_VIEW_row_type;

CREATE TYPE MY_MAT_VIEW_row_type AS OBJECT
(
F1 number,
F2 varchar2(100),
F3 date
[code].......

I've noticed that when materialized view is near to be refreshed (every 5 minutes) there is some "delay" in getting result sets using this query:

SELECT *
FROM TABLE(FN_GET_MY_MAT_VIEW(100)) a;

And when the refresh is finished the result set returns immediately.

Question.

Is there a way to avoid such "delay" while materialized view is refreshing?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved