Refreshing Logically Correlated Snapshots Data?
Nov 29, 2010
Simplifying the data structure that my problem concern, let's say there are two materialized views between whose data there's a one-to-many relationship [the relationship can be logical without a need of creating any foreing keys].
The data should be as actual as possible respecting the content of the master tables, let's say it shoul be refreshed every 5 minutes.
As far as I know, the jobs related to each snapshot, even if they have a START WITH and NEXT parameter set to the same value, work independently...So: what would be the best manner to synchronize the jobs so as to make sure the data of both snapshots are coherent?
View 3 Replies
ADVERTISEMENT
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
Oct 9, 2012
i just noticed on my 11g database - no AWR snapshots are generated.
View 4 Replies
View Related
Oct 20, 2010
The sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)
View 3 Replies
View Related
Dec 4, 2007
I have a table MYTABLE in database mydb1 duplicated via materialized view and materialized view log and refresh_snapshot commands to a MYTABLE on mydb2 database.
I like to duplicate this table MYTABLE to a third database mydb2, using the same method (materialized view and refresh_snapshot command).
Is it possible ? What's hapend to the materialized view log where I launch a refresh_snapshots on mydb2 ? How is this materialized view log truncated ?
View 5 Replies
View Related
Sep 13, 2010
I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
View 15 Replies
View Related
Mar 5, 2013
How can check database performance in a non-sequential parts of a day using AWR tool. For example, suppose we divide a day to 2 parts: low-traffic time and high-traffic time with the following time interval:
low-traffic time : 7:00am - 10:00am and 7:00pm - 11:00pm
high-traffic time : 10:00am - 01:00pm and 4:00pm - 06:00pm
I want to examine performance using snapshots gathered by AWR. If I get 2 AWR reports for low-traffic time of day (one for 7:00am - 10:00am, another for 7:00pm - 11:00pm ) and compute average of values in 2 reports, could I called it database performance in low-traffic part of day or not?
View 9 Replies
View Related
Aug 11, 2010
If I create an AWR Report via OEM or awrrpt.sql for an interval between two snapshots, in the report there are statements which were not executed in this interval.
Are there any known bugs for that problem or have you got an explanation for this behavior?
View 2 Replies
View Related
Sep 1, 2010
select rtrim(xmlagg(xmlelement(e, table_name||',')).extract('//text()'),',')
from (
select distinct table_name from tr_products tr join all_instruments v
on tr.PROD_COA_ID = v.PROD_COA_ID
where tr.LEVEL_06_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_05_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_04_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_03_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_02_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_01_VALUE like t1.COLUMN_VALUE||'%')
This is an excerpt of a code of a huge query, I didn't want to write it all here because there is no point in doing that since this is the part of the query that I'm actually having problem with, and I think you will be able to understand my question just by looking at this piece of code.
Anyway, this here is actually a subquery in a select-clause of a main query, and apparently it has a (nested) subquery of its own (as seen in the code). I want to reach some values ("COLUMN_VALUE") that are correlated to the parent query (t1 is alias of a table in the from-clause of the main query), and as you can see, I'm trying to reach it from this level 2 subquery. As I have recently found out, Oracle does not allow this, it only tollerates correlation down to level 1.
Here is a scheme of my query:
select something1,
(select something2 from
(select something3 from some_table2 t2
where t1.value = t2.value))
from some_table t1
can this be overridden somehow, is there a workaround for this limitation?
View 3 Replies
View Related
Apr 9, 2010
which one is better in performance point of view and why between inline view and correlated query.
View 5 Replies
View Related
Jan 21, 2013
Can I update inline view like below?
update
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a,
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b
where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))
set b.answer = a.answer;
My second question is: The following query is giving error. What can I do?
update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;
SQL Error: ORA-00904: "ANS"."ANSWER_ID":
View 8 Replies
View Related
Nov 22, 2010
I have some questions about Oracle + EMC shared storage. I have Oracle 11gR1 RAC (2nodes) + ASM environment with shared shorage EMC Clariion AX4.
The database is running no archivelog mode. I'd like to implement point-in-time recovery using Snap View snapshots.
Currently my AX4 platform has the following LUNS:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
Using source LUNs in consistent session will take sync snapshots of all the LUNs working against my database. Once something happens with database, the LUNs can be returned to specific point in time when snapshot consistent session was taken and I'm expecting the database will up and continue to work.
Questions:
1. Is my approach correct at all? (The database is running noarchivelog mode, not dealing with hot backups. The recovery point in time implemented purely via EMC snapshot consistent sessions.)
2. The AX4 platform has a limit 8 source LUNs in session. If I understand it correctly, I can't place more than 8 LUNs in session. What will be the workaround if my database will occupy more than 8 LUNs, I'd like to take their consistent snapshot; for example:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
LUN8 - ASM - DISKGROUP DATA DISK DATA03 (actual db datafiles)
LUN9 - ASM - DISKGROUP DATA DISK DATA04 (actual db datafiles)
View 2 Replies
View Related
Jul 18, 2013
concept of Exists, With and Correlated Subquery in Sql plus 10g from begining, what are they how do they work with step by step example from basic.
View -1 Replies
View Related
Jul 6, 2012
Is the identifier functions UID always correlated with the column dba_users.user_id ?
View 4 Replies
View Related
Jun 11, 2010
I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.
Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:
Left outer join. Works but its incredibly slow:
SELECT
distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM",
count(distinct ule.id) as "User Logins",
count(distinct ule.user_id) as "Users logged on",
count(distinct p2.id) as "Existing Users",
count(distinct p1.id) as "New Users",
count(distinct r1.id) as "Resources created"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.
select
distinct ule.loginday as "Month",
count(distinct ule.id) as "Logins",
count(distinct ule.user_id) as "Users logged in",
count(distinct p1.user_id) as "Users created",
count(distinct p2.user_id) as "Existing users1"
[code]....
performance is OK with 2 tables but the example above takes forever to execute.
Tried an approach with union but this creates new rows for each table
SELECT DISTINCT p1.MONTH AS "Month",
COUNT(DISTINCT p1.user_id) AS "Users created",
NULL AS "Logins",
NULL AS "Users Logged in",
NULL AS "Resource views",
NULL AS "Resources viewed"
FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,
[code]....
View 4 Replies
View Related
Jun 4, 2012
I have a mview based on the database link. Mview created without the error and first time refrehment has been done properly in time, but after the first time it is not refreshing.
CREATE MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMSCHAT"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
[code].....
I have set the refreshment time for mview for 15 min with the following command
alter MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 REFRESH FAST START WITH sysdate+0 NEXT sysdate+(15/86400*60);
But now this mview is not refreshing and no jobs is showing in dba_jobs_running.
18:10:32 SQL> select LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN from dba_jobs where job=234;
LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-------------------- -------- -------------------- -------- -------------------- -------- ---------- -
04-jun-2012 17:47:13 17:47:13 04-jun-2012 18:02:13 18:02:13 34 N
View 11 Replies
View Related
Jul 10, 2012
I am often tasked with refreshing schema's from one DB to another.The first thing I need to check is the space the objects take up in the source DB. SQL statement that prints the size of the following objects
('SEQUENCE','SYNONYM','TABLE','INDEX','PACKAGE','PACKAGE BODY','DATABASE LINK','VIEW','PROCEDURE')
which is totaled by owner, TS.. Or the SQL can just take in an owner. I am looking for an output similiar to this:
TS name, owner, object_name, object_type MB SIZE
users xxx t1 table 100
users xxx t2 table 200
sum of tables 300
[code]....
View 3 Replies
View Related
Jan 25, 2011
I am confused to see one materialized view is not refreshing automatically, whereas the same thing I can do manually.
View 2 Replies
View Related
May 3, 2010
Am getting an error while trying to refresh a Writeable Materialized view using the following statement -
exec dbms_mview.refresh('schema_name.MV_name','C')
Error:-
ORA-02448: constraint does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 1
The Primary Key Constraint on the base table/MV is deferrable, the base table and the MV are in different schemas and the user trying to refresh the MV has an explicit grant on "ALTER ANY MATERILIZED VIEW" and "SELECT ANY TABLE" through a role.
Its refreshing successfully when I make the PK constraint (Base Table, MV) NOT DEFERRABLE (or) when I explicitly grant "SELECT ANY TABLE" to the user trying to refresh the MV.
View 5 Replies
View Related
Apr 6, 2010
I have a database schema with a Materialized View (MV) that updates a products table shown on a website. This schema was recently imported to an 11g database from 8i, but since this import I have been unable to refresh my schema's Materialized View.
The MV_PRODUCT_MASTER Materialized View attaches price data to product data that is stored in a normal WEB_PRODUCT table, and create an MV table as a result. The output MV table is what the website pulls data from to display to the site users. Since it is an MV table, this table cannot be edited directly. In order to change, say, a product description, I need to alter the description field in WEB_PRODUCT and manually refresh the MV_PRODUCT_MASTER table. Only through the refresh will the upstream edits appear in the MV_PRODUCT_MASTER table and be visible on the website.
In my old 7.6.0.11 copy of TOAD, I could manually refresh these MVs easily, by opening the "Snapshots" tab, right clicking on the MV I wanted to refresh and selecting the "Refresh" option. Since the schema was imported to 11g, I have been using the Oracle SQLDeveloper tool to manage the schema. SQLDeveloper doesn't have a clear method for manually refreshing an MV, or else the method I am using isn't working.
If I right click on the MV_PRODUCT_MASTER Materialized View object, and choose "Other Actions", I see the following choices:
Shrink Materialized View
Compile Materialized View
Force Materialized View Refresh
Rebuild Materialized View
I assumed that -Force Materialized View Refresh- was the right choice, and chose that. This option displays the SQL:
alter materialized view "WEBADMIN"."MV_PRODUCT_MASTER" consider fresh
When I apply this, I get the message: Materialized view - MV_PRODUCT_MASTER - has been set torefreshed. However, no changes appear in the MV output table. i.e. if I make a specific change to a row in the WEB_PRODUCT table, the change is not being carried into the MV_PRODUCT_MASTER table, so that indicated that the refresh is not actually happening. The MV table appears to believe it is being refreshed:
REWRITE_CAPABILITY GENERAL
REFRESH_MODE DEMAND
REFRESH_METHOD COMPLETE
BUILD_MODE IMMEDIATE
FAST_REFRESHABLE NO
LAST_REFRESH_TYPE COMPLETE
LAST_REFRESH_DATE 06-APR-10
STALENESS UNKNOWN
But it isn't showing any changes.What am I doing wrong? Is there a plain SQL statement I can run in order to run these refreshes, instead of using the SQLDeveloper GUI?
View 4 Replies
View Related
Nov 7, 2013
I support an APEX app that has one workspace for Development and one for the live Production application. When I originally created the Dev instance I was able to copy tables and data by granting select privileges between the two workspaces (I did this one table at a time).
The development workspace data needs to be refreshed from production since it's been a long time since the initial refresh and is very out of date. I would like to do a couple things:
1) execute SQL to compare the tables in the two workspaces to ensure they're structurally identical
2) execute SQL to truncate Dev tables and refresh from Prod Is there a way to do this?
View 3 Replies
View Related
May 29, 2013
I would like to create a process that allows me to move from record to record without refreshing a whole page. Specifically, I'd like to use a tree object that allows the user to select items and then have the details for that item show up on the right side of the page. Is there a way to do this without doing a complete page refresh?
So far I've tried two different on-demand processes:
- first I created an "Automatic Row Fetch", but I read on the board that this actually won't work as an on-demand process;
- I also tried a PL/SQL process that sets the various variables (e.g. ":P7_NAME") based on a query. This updated the session state, but it didn't refresh the fields on the page. Is there a way to trigger this refresh?
View 0 Replies
View Related
Nov 27, 2012
I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.
I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.
View 3 Replies
View Related
Oct 15, 2011
We have some materialized views in our environment which refreshes after every 1 hour. In the same environment, we have some big processes and we do not want the performance of these big processes to get affected because of refreshing these materialized views.
if there is a way to pause the refreshing of materialized views before running the big processes and later after the big processes are completed, then resume the refreshing of the materialized views?
One way I can think of is to drop the materialized views and create the materialized views back.
View 1 Replies
View Related
Apr 21, 2010
the importance of refreshing the stale statistics in oracle database and also how often we should do this?
View 3 Replies
View Related
Dec 17, 2012
I have a Multi layer Mviews and Views which i use to load a table. There are three base level Mview which is accessed by 4 views ... which are in turn accessed by a view which is used to load a table.
Before the insert called for the table .. the base mviews are refreshed.
I get the "ORA-01410 Invalid Rowid" error while inserting in to the table and while refreshing the mviews. They don't occur every time and not together as well.
The Mviews contains table joins and the top level views which accessing these mviews does aggregation. Below is the mview query for one of the mview.
SELECT DISTINCT c.fiscal_time_id,
a.product_group_id,
a.allocation_driver_id,
a.hyp_entity_id,
d.business_division_alt_id
[code].....
The Refresh method for the mview is Force.
View 3 Replies
View Related
Sep 14, 2011
I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.
drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
[code]....
I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.
View 33 Replies
View Related
Jan 24, 2013
I am trying to refresh a report with dynamic action. And getting the following errors:
{"dialog":{"uv":true,"row":[{"V":"Widget Failure
ORA-20876: Stop APEX Engine,
classic_report"}]}}
I have identified the problematic part. I have a multi-select select list and my query goes like this:
select o.order_id, s.salesrep_name
from ml_orders o, ml_order_salesrep_mapping m
where m.order_id(+) = o.order_Id
and case when :P22_SALESREP is null then 1
[code]...
View 4 Replies
View Related
Nov 14, 2013
I am not able to refresh tabular form region on close of the modal page.
View 6 Replies
View Related
Aug 17, 2012
I need to refresh my main page after closing the popup page displayed by skillbuilder plugin
apex 4.1.0/Oracle 11g Ent.
View 9 Replies
View Related
