SQL & PL/SQL :: Refresh Views And Function?
Aug 17, 2010Is there any function to refresh all views and functions in oracle.
View 4 Replies
ADVERTISEMENT
Refresh Materialized Views Based On Remote Views?
Aug 4, 2011I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
Convert Some Existing Materialized Views (fast Refresh) To Partition Materialized Views
Jul 7, 2010I 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?
How To Refresh Materialized Views
Aug 4, 2011I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
how the refersh happens or how do I specify the refresh clause.
REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
SQL & PL/SQL :: Refresh All Materialized Views?
Nov 28, 2011I have n number of materialized views in my schema ..I want to refresh all M_views at once by using cursor
View 3 Replies View RelatedOne Materialized Views In Two Refresh Groups
Sep 11, 2013I have 5 MViews that I want to refresh in two occasions, every Sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
Materialized Views Vs Refresh Groups?
Sep 29, 2010We recently migrated a database from 9i to 10g (overdue we know!!) and discovered that dbms_mview.refresh default behavior was turned upside down - meaning that 10g didn't first truncate the MV to refresh it. We're trying to unwind a lot of legacy issues, but it also turns out that we also have 100 REFRESH GROUPs and 100 MATERIALIZED VIEWs. That means a 1 to 1 relationship between RGs vs MVs. There is one MV defined to each RG.
These are my questions:
1) Does a 1 to 1 relationship between RGs and MVs make sense to anybody? The original implementors are gone and we can't fathom the reason for this.
2) Is there any reason why I shouldn't convert these 100 groups to plain and simple 100 MVs? I don't want the delete/insert refresh behavior of dbms_refresh.refresh and I do want the truncate behavior of dbms_mview.refresh ATOMIC=FALSE for refreshing a standard MVIEW
Replication :: Fast Refresh Of Materialized Views
Jun 19, 2008CREATE Materialized View log on t1 with rowid;
CREATE Materialized View log on t2 with rowid;
CREATE Materialized View log on t3 with rowid;
CREATE Materialized View MV_test
BUILD IMMEDIATE
FAST REFRESH ON COMMIT
[code].......
I have created a Materialized View by joining the above 3 tables. The Materialized View logs with ROWID has been created on each of the 3 base tables. One of the base tables has around 160 million rows and i expect the Materialized View to have the same number of rows.
The Materialized View has been built for FAST REFRESH on COMMIT. The SELECT list of the CREATE Materialized View statement includes all the ROWIDS from each base tables for doing the fast refresh.
The DBMS_MVIEW explain view shows that the view is capable of doing fast refresh on any DML. The Materialized View took 3 hrs to get built. I have also created indexes on all the three rowid columns (t1rowid,t2rowid,t3rowid) on the Materialized View.
Now, When i do any update on one of the base table and do a commit,
UPDATE t1 set value=500 where tx_id='1234';
The commit is taking around around 15 minutes. I have generated the trace and had a look at it, and it shows that, to refresh the Materialized View, ORACLE is first deleting that record from the Materialized View and then inserting the new record from the base table to the Materialized View.
This delete done by oracle is being done on the basis of the rowid of the updated record stored on the Materialized View log,
However its not using the index created on the t1rowid of the Materialized View and doing a full scan of the MV and hence the delete is taking a lot of time. The delete statement is using a hint NO_MERGE and HASH_SJ which is causing it to do the full table scan of the MV. why is ORACLE not using the index on the t1rowid column of the MV to do this delete?
Also to do the insert in the MV, its selecting the new changed value from the base tables t1,t2 and t2. Here again its doing a full table scan of the base table t1, rather then using the rowid directly or the index coulmn.which is taking a lot of time.
Oracle Materialized Views - Refresh Combinations
Dec 10, 2012I'd like to set up a new Oracle Materialzed View to automatically default to refresh FAST every hour. I have no trouble setting this up in the CREATE MATERIALIZED VIEW statement. However, we will occasionally be performing table maintenance on the table referenced by the MV. At that time I would like to use dbms_mview.REFRESH to execute a refresh ON COMMAND (either F or C depending on the operation weperformed on the table). I have read that this will override the automatic refresh every hour that was set up for the MV when it was created. However - wil the override be permanent? Or will the MV continue to automaticaly FAST refresh every hour after the ON COMMAND refresh is completed?
View 1 Replies View RelatedSQL & PL/SQL :: Materialized View On Views - Fast Refresh?
Jun 17, 2010We are planning to create a materialized view on 5 tables and 2 views. After first complete refresh we want to do Fast refresh going forward and planning to create view log for base tables. Since the SQL uses two views, we can create view-log for its underlying tables. But will that work ? In case of fast refresh ? or it will still go for complete refresh ?
View 9 Replies View RelatedReplication :: Materialized Views Refresh In Snapshot-group?
Mar 24, 2010How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?
Here below is the scenario.
MATVW_A
CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;
MATVW_B
CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;
MATVW_C
CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;
I created a snapshotgroup SNGRP with all the above materialized views.
DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');
The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after MATVW_B,MATVW_C.
As they are dependent views.Is there anyway where we can mention the order of refreshing materialized views in snapshot group.
Replication :: Setup A Refresh Group For Nested Materialized Views
Mar 25, 2009Suppose I have materialized view A,B,C. Now I have a new materialized view D which is created from A,B,C. Can I put D into same refresh group?
I am not sure whether D will be refreshed first before A,B,C, resulting that D do not get any update If that is the case, the result will be wrong
If we cannot use refresh group to update nested materialized view, what should we do?
Replication :: ORA-12013 / Updatable Materialized VIEWS Must Be Simple Enough TO DO Fast Refresh
Aug 20, 2009CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.
DROP MATERIALIZED VIEW LOG ON ABC;
DROP MATERIALIZED VIEW MV_ABC;
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh
Oracle 6i Refresh Function
May 18, 2009when i press the save button in my application the record will be saved but the value in my "display item" wont be refreshed. when i minimize the form designer and maximize it again, the "display item" is refreshed.
so i am searching for a "refresh" function
SYNCHRONIZE;
redisplay;
these functions doesn't work.
Server Utilities :: Export Procedure Views Function And Packages In Database
Sep 29, 2010How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies View RelatedServer Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?
Nov 5, 2012is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?
in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
PL/SQL :: Get List Of Materialized Views / Views Using Column Of Table
Oct 17, 2013I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
View 3 Replies View RelatedReplication :: Cannot Fast Refresh Truncated MV Even After Complete Refresh
Aug 14, 2010I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.
DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.
ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" 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
Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
how to again make the MVs fast refreshable?
PL/SQL :: To Create Function Based Index For Group Function Columns
Jun 15, 2012Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function
Feb 4, 2013I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?
Mar 11, 2010What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View RelatedWhy To Need Materialized Views
Aug 26, 2010Why do we need materialized views when the normal views also serve the same purpose?
View 2 Replies View RelatedHit All Views One After Other In Database?
Aug 26, 2003 I am writing a checker that should hit all the views one after other in database and report if there is an SQL error ( Page: Data cannot be retrived from the view XYZ).
The query I am running is
"select * from $view fetch first 1 rows only".
better query which runs faster as I have to query all the views in db in the script ( script runs every 10 mins on cron).
Materialized Views Not Getting Refreshed?
Dec 29, 2010I have a problem Mat view refresh...I have to take a snapshot of the table before nsert or update to the table....below is the code written to achieve this..
create materialized view my_view refresh complete as select * from t2;
create or replace trigger TRG_T2
before insert or update on T2
declare
X number;
[code]....
However this is not getting refreshed... Also i noticed that it works for the very first time (after first insert or update) and then for successive ones it doesnt.. ..
Select All Views From Schema
Apr 27, 2010I want to list out all the views in a schema. What table can I query?
View 2 Replies View RelatedCreating Trigger For Views
Sep 21, 2008I have a table tblcustomer and a view called vworder. I need to create a trigger such that any data being added into vworder first checks if the field customerid has the data in customerid of tblcustomer.. all it has to do is spit out a error "Customer ID not found"
So I created a view such as
Quote: create or replace TRIGGER trig_order
BEFORE INSERT OR UPDATE ON vworder
FOR EACH ROW
DECLARE
cust_id VARCHAR2(20);
BEGIN
SELECT customerid INTO cust_id FROM tblcustomer WHERE customerid = :new.customerid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20000, 'Customer ID Does Not Exist');
END;
But it comes with error
Quote: ORA-25001: cannot create this trigger type on this type of view 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.
I am not an expert at all and I need to finish this today itself.
SQL & PL/SQL :: Usage Of Hints In The Views
Oct 11, 2011I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows
create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);
my question is how to use hints on this table if your are using view to access the data from internal table.
Normal structure is if i don't wrong:-
index( <<view name|view alia name>> <<table name|table alias name>> name of index)
Consider my case
select * from view_t130 where <index_column> --not picking up index
i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.
select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>
how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.
SQL & PL/SQL :: How To Create Dynamic Views
Jun 26, 2010How to create dynamic views? I need literature on dynamic views.
View 5 Replies View RelatedSQL & PL/SQL :: Bind Variable In Views?
Dec 27, 2010how to avoid the bind variable in view.
The query is correct but it contains bind variable Based on this query, View has to be created for report
like example select * from emp where deptno = :deptno
How to get the correct result by avoiding bind variable because view does not accept bind variable.
Replication :: Views Or Streaming
Oct 13, 2008I need to know that what is best approach for replication. Materialized View or Streaming. We have to replicate a subset of database at different nodes.
View 3 Replies View Related