JOB
Bharath, September 26, 2002 - 5:29 pm UTC
I want run the JOB on demand if i run the command dbms_job.submit(I didnt specify any time interval ..dependent on JOB_QUEUE_INTERVAL) found the entry in user_jobs.My question is will those entry will be wiped off once the JOB is done
September 26, 2002 - 6:28 pm UTC
to run a job on demand -- just execute the "WHAT" -- it makes no sense to use a job if you are going to just call dbms_job.run, just execute whatever "what" was.
snapshot refresh
sm, November 01, 2002 - 11:19 am UTC
O.K.
Hava a table based on a snapshot (some columns in table has to be updateable..so that's why making a table based on a snapshot)
The snapshot will be refreshed multiple times during the day...
I was thinking about
having a trigger on the snapshot table..so as it refreshes..it will compare the table with the snapshot table...and make changes...
I don't know how this is going to play..have never dealt with snapshots that have to be refreshed hourly...
An application is going to be using this table (table based on the snapshot)...so as snapshot is being refreshed..and throwing changes at the table...won't we get locks?
What's the best way to do this?
November 01, 2002 - 3:50 pm UTC
not following you here. sorry. don't understand.
DBMS_SNAPSHOT DBMS_REFRESH
Sachin, April 17, 2003 - 3:27 pm UTC
Tom,
We have about 30 snapshots for getting data from two different systems.
They are all complete refreshes.
Most of them are the kind that have time inside the snapshot..like "start with sysdate next...." statement inside the create snapshot script...
all these will create a job automatically which will have
"what" as "dbms_refresh.refresh" and a refresh group.
Lately we wanted a snapshot to put some data into another table right after it refresh....so I created that snapshot as "On Demand"...and created a procedure that use
DBMS_SNAPSHOT.REFRESH.....
and insert data into another table after it refreshes..
Questions:
1. Currently all of our snapshots have a separate job...
I want to create a procedure and list all of them one after
the other....like
DBMS_SNAPSHOT.REFRESH(SNAPSHOT_1);
DBMS_SNAPSHOT.REFRESH(SNAPSHOT_2);
....
and then submit just one job...and track their success
failure through dba_snapshots.
Of course...I will have to make them on demand first...
is this approach O.K.
2. What is the difference between DBMS_REFRESH AND DBMS_SNAPSHOT
Thanks...
April 17, 2003 - 5:30 pm UTC
1) as long as it is OK with you to have the refresh serially, one after other, sure -- its oK
2) dbms_refresh is all about creating and manipulating refresh GROUPS. dbms_snapshot/mview is about individual snapshots/mv's
snapshot refresh
Florin, September 04, 2003 - 6:15 am UTC
Hi,
I'm using Oracle 8.1.7.4.
Lately I've created 4 snapshots setting the inerval (next) to 10 min.
The snapshots are refreshing without any problem however the parameters in my init.ora are:
job_queue_interval=60
job_queue_processes=1
How do you expalain this? I thought that by having job_queue_processes=1 no snapshot will be refreshed. The snapshot is refreshed every 10 min as I defined it (alter snapshot billing_account_snap refresh fast start with sysdate next sysdate + 1/144 ;) so it means that the parameter job_queue_interval=60 is ignored?
Many many thanks in advance.
Sincerely,
Florin
September 04, 2003 - 9:00 am UTC
job_queue_processes=0 would mean no auto refresh
with it at 1, they are all refreshed one after the other.
if you set it to 4, they would be refreshed at the same time in parallel.
the interval is not ignored??? i don't know why you think that. that just means the job queues will wake up every 60 seconds and say "anything for me to do?" once out of every ten times -- the answer is "yes, there are 4 snapshots to refresh"
snapshot refresh group
atul, September 18, 2003 - 5:02 am UTC
Hi,
1)What view we can use to see snapshot refresh group and
which snapshots are included in refresh group??
2)Can we add updatable snapshots in refresh groups?
3)My snapshot is not refreshing properly where should i see
the errors??
Thanks.
atul
September 18, 2003 - 10:15 am UTC
Broken Job
atul, September 19, 2003 - 12:12 am UTC
Hi,
Thanks for your help..
I am getting output of the query like this
select RNAME,REFGROUP,JOB,broken from dba_refresh;
RNAME REFGROUP JOB B
------------------------------ ---------- ---------- - -
UPD_EQMGROUP 3 23 Y
So i can see my job is broken..
what method i can use to reshedule it???
Or what i have to do next??
Thanks.
atul
September 20, 2003 - 4:55 pm UTC
Query
atul, September 22, 2003 - 8:06 am UTC
Hi,
I have gone through the link..
Just i want to confirm
should i use,
DBMS_JOB.RUN(job#)
To reshedule the broken job..
Thanks.
atul
September 22, 2003 - 8:23 am UTC
well, you can "unbreak" it as well using dbms_job.broken
if you use run, you will have to sit there and wait for it to run to completion. it is not asyncronous
Snapshot Refresh
A Reader, May 14, 2004 - 8:47 am UTC
Some of my Materialized views take a long time to refresh.
If I take out the select statements associated with them and run them seperately, they only take seconds. What could be the possible reason?
May 15, 2004 - 10:27 am UTC
insufficient data.
do you do incremental (aka 'fast') refresh. perhaps that is the cause. there are many cases where 'fast' which really should be called "changes" or "incremental" is not as fast as just truncate+insert /*+ append */ the defining query.
do you account for the time it takes to write the data to disk and maintain the indexes on the MV or are you just comparing "query" to "something you cannot compare a query to"?
is the network involved in one test, not the other.
is the mv part of a large mv group...
etc....
Snapshots
A Reader, May 16, 2004 - 6:07 pm UTC
Hi
I have created snapshots with FORCE option to capture updates from master tables.
The general structure of these snapshots is as follows:
CREATE MATERIALIZED VIEW test_mv
NOPARALLEL
NOLOGGING
NOCACHE
BUILD IMMEDIATE
REFRESH FORCE
DISABLE QUERY REWRITE AS
SELECT /*+ INDEX(a ind1) */
col1,
col2
col3
FROM table1@dblink a,
table2@dblink c
WHERE EXISTS
( SELECT /*+ INDEX(b ind2) */
col1
FROM table2@dblink b
WHERE b.col1=a.col4 -- Foreign Key join
AND b.col2 > LAST_PROCESS_DATE
)
AND a.col1=c.col1
As per your suggestion if I replace these snapshots with staging tables
and perform TRUNCATE and INSERT /*+ APPEND */ it is pretty quick.
My original question - If I take out the SELECT statement and perform an
explain plan, it uses all the indexes in the hints and takes about 10 seconds
to complete. The total number of records retrieved is 150000(apprx).
But if I exceute the statement as a whole, EXPLAIN plan shows serial retrieval
of data. The time taken to create the snapshot is 28 minutes.
The example that I have given you is simple, other snapshots involve complex queries.
Should I adopt TRUNCATE & INSERT/*+ APPEND */. approach for all these snapshots?
Thanks for your help
May 17, 2004 - 7:12 am UTC
FORCE Clause Specify FORCE to indicate that when a refresh occurs, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
if you use refresh COMPLETE, the database will perform a truncate + insert /*+ APPEND */ -- that was what I was saying, not that you would do it that way, but rather that the database would do it that way for you.
A reader, June 10, 2004 - 10:18 am UTC
Hello Tom,
If any user trying to access the read only MV while the MV is being refreshed, will there be any problem? I think Oracle should place a lock on the MV and the user session would wait for the refresh process to finish, is it right?
On the other hand, if the user is accessing the MV and the MV refresh schedule comes, the user session should keep a snapshot of the data in UNDO and the refresh process can go on as scheduled without waiting for the user session to finish?
thanks,
June 10, 2004 - 5:03 pm UTC
if the MV is
a) not refreshed complete
b) refreshed complete but part of a refresh group
then the refresh is done via normal insert/update/delete and you have total concurrency.
if the mv is refreshed via "complete" and is not in a group, then the refresh is a truncate+insert /*+ append */ and there will be a period during which the data "isn't there"
mismatch
Raju, June 24, 2005 - 3:05 am UTC
Hello,
Our database is Oracle 7.1 running on Solaris. We have a snapshot for a master table at our Hqrswhich was created as below and put in the job queue for auto refresh.
create snapshot snap1 ..........
using index tablespace........
refresh fast
start with sysdate
next (sysdate+(1/24))
as select * from ops$mstc.mast1@site_hq
Now this is observed that the master table has 344052 rows
whereas the snapshot table has 335445 rows. Even if the snapshot is fast refreshed using
execute dbms_snapshot.refresh ....,
the no. of rows are not matching.
What could be the reason for this and how to get all the
rows of the master table at our branch.
with thanks
Raju
June 24, 2005 - 6:51 am UTC
sorry, I hesitate to even tell you to contact support in this case due to the really old version.
I suppose the easiest thing to do would be to recreate the snapshot. The snapshot is just a table in reality - could be that someone actually inserted into it.
mismatch2
raju, June 29, 2005 - 1:12 am UTC
Mr.Tom,
Thanks for the reply. Instead of recreating the snapshot I tried by doing complete refresh as below.
execute dbms_snapshot.refresh('snap1,'c','rs_large').
Then an error 'ORA-01650: unable to extend rollback segment R02 by 64 in tablespace RBS' is thrown.
We have several small rollback segments R01,R02,R03...
and a large Rollback segment rs_large in our DB.
In the above statement, oracle should use rs_large segment for complete refresh. Then, why this error is coming on R02 segment.
with thanks
Narasimha Raju
June 29, 2005 - 8:39 am UTC
(won't even give you my opinion of having multiple sized RBS's but...)
feel free to contact support if what you say is true.
funny thing is - a complete refresh should generate almost NO ROLLBACK since the complete refresh will be a truncate + insert /*+ APPEND */, so unless you have lots of indexes (which you probably want to disable and rebuild after the refresh), it should not matter which rbs you use
heterogeneous snapshot
rajiv, July 08, 2005 - 12:34 am UTC
hai,
is it possible to create a snapshot from non-oracle master table?
do you have any guide or tutorial about it?
thanks in advance.
July 08, 2005 - 7:27 am UTC
it is just a complete refresh materialized view. period.
create materialized view X
as
select * from t@remote;
Monitor Query Rewrite
A reader, July 11, 2005 - 6:05 pm UTC
Hi Tom,
How do I monitor query rewrites? Which MV has been used for query rewrite? How often?
I am using Oracle 10G EE.
Thanks,
July 11, 2005 - 7:09 pm UTC
real quick - first thing that pops into mind
v$sql_plan, object# -- it would show you plans that reference the mv
re: Monitor Query Rewrite
A reader, July 12, 2005 - 6:24 pm UTC
What if it's a production environment which has many materialized views? I want to monitor which MVs were used in query rewrites and which ones were not.
Is therw a way that I can configure the DB server to log a message whenever a query rewrite occurs? Or at least how many query rewrites occurred to each MV...
Thanks,
July 13, 2005 - 10:54 am UTC
not that I'm aware of, production systems would have the v$ views too.
Refresh in EoD Execution
Guneet, September 06, 2005 - 3:30 am UTC
Hi Tom,
I am in a bank and my requirement is, every time i execute the End of Day process after the succesful completion of EoD processes refresh of my materialized views should execute . Presently I am doing this activity manually using "EXECUTE DBMS_REFRESH.REFRESH('F1_MV_REF_GRP');". can I plug this in my EoD procedure and if the refresh fails I can log the errors in my error log tables .
September 06, 2005 - 8:19 am UTC
sure, it is just a stored procedure call.
Can we avoid truncate in complete refresh
Parvez, September 13, 2005 - 10:41 pm UTC
I have a situation where I am trying to create a snapshot based on two tables, the associated query will always return a unique records since a time filter is used in the query, it looks something like below
CREATE SNAPSHOT selling_detail REFRESH COMPLETE START WITH SYSDATE
NEXT TRUNC(SYSDATE + 1) + 10/24
AS SELECT a.deal
,b.desc
,a.created_date
FROM deals a
,link b
WHERE a. created_date BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.deal_type = 'NBV'
AND a.deal_type = b.deal_type
Since this query is getting an incremental data (previous day) which is unique (deal primary key) I dont want to use Force to avoid overhead but if I use Complete it gets the data but before inserting it truncates the selling_detail table, which I dont want. If you need more information please let me know.
Thanks
September 14, 2005 - 8:04 am UTC
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1389964969205
use a snapshot refresh group.
In 10gR1 and up, the method has changed
if you:
create table emp as select * from scott.emp;
alter table emp add constraint emp_pk primary key(empno);
create materialized view mv as select * from emp;
alter session set sql_trace=true;
exec dbms_mview.refresh( 'MV' );
the trace file will have:
delete from "OPS$TKYTE"."MV"
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "OPS$TKYTE"."MV"("EMPNO","ENAME",
"JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") SELECT "EMP"."EMPNO",
"EMP"."ENAME","EMP"."JOB","EMP"."MGR","EMP"."HIREDATE","EMP"."SAL",
"EMP"."COMM","EMP"."DEPTNO" FROM "EMP" "EMP"
whereas in 9ir2 and before, you'll see:
truncate table "OPS$TKYTE"."MV" purge snapshot log
INSERT /*+ APPEND */ INTO "OPS$TKYTE"."MV"("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") SELECT "EMP"."EMPNO","EMP"."ENAME",
"EMP"."JOB","EMP"."MGR","EMP"."HIREDATE","EMP"."SAL","EMP"."COMM",
"EMP"."DEPTNO" FROM "EMP" "EMP"
Note: for 10g upgrades - think about that change. Instead of truncate+directpath load (no undo - in nologging or noarchive log mode, no redo) you'll have a delete + insert (undo and redo)
Can we avoid truncate in complete refresh
Parvez, September 13, 2005 - 11:06 pm UTC
Oracle version 8.0.6.0.0
Two thoughts:
Pete_S, September 14, 2005 - 8:57 am UTC
Do you really need a materialized view here? - you could use a "conventional" table and populated it by daily inserts.
There is also the option to refresh a materialized view using your own code and then using 'Consider Fresh' to tell Oracle that the view can be used.
September 14, 2005 - 9:05 am UTC
but the MV removes code, code we'd need to maintain, write, debug, etc -- I myself would prefer to use the MV rather than write the refresh.
As long as the builtin functionality can do it, I use it.
In general, yes
pete_s, September 14, 2005 - 9:54 am UTC
Normally, the less code I write the better ;), but if the functionality of the in-built code does not cut it...
In this case, the user is trying to use refresh to update a moving window on a larger ranged MV - this is similar the case where the MV holds history no longer present in source table, ie the defining query does not match the information stored.
But I could have got the original poster's point completly wrong!
September 14, 2005 - 10:11 am UTC
right, but with this:
CREATE SNAPSHOT selling_detail REFRESH COMPLETE START WITH SYSDATE
NEXT TRUNC(SYSDATE + 1) + 10/24
AS SELECT a.deal
,b.desc
,a.created_date
FROM deals a
,link b
WHERE a. created_date BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.deal_type = 'NBV'
AND a.deal_type = b.deal_type
they are always getting "yesterdays" data - all of the new records from yesterday. The MV is very good at getting that, the only "glitch" is the truncate that takes place (the data appears to disappear and then come back) and the refresh group will fix that right up.
otherwise, it will be the same amount of 'database work' to refresh..
But my reading was:
Pete_S, September 14, 2005 - 10:19 am UTC
I'm with you on the query getting yesterday's transactions. But I took the view that also needed to keep the day before, and the day before that...
perhaps I mis-read the post.
September 14, 2005 - 10:40 am UTC
ahh, I see, yes, you could read it that way.
I thought they just wanted to get rid of the truncate and turn it into a delete, but yes - they might just want to get rid of the truncate/delete and only get the changes and add them.
In which case - they need to clarify, a materialized view is not appropriart for that.
Some clarifications
Parvez, September 14, 2005 - 10:06 pm UTC
Sorry to reply late, the snapshot selling_detail, which I am trying to create will hold the history for about 2 years, I have to generate some reports based on this. Before I was directly querying deals & link table to generate reports but since the reports is based on around 2 years of data it takes lot of time to complete.
So what I am trying to do now is to create an intermediate table with records of my interest this way the reports execution time will be reduced.
The reason of using snapshot over writing PL/SQL was to avoid writing code and then maintain it.
It might not be able to use the feature of MV as pointed out "this is similar the case where the MV holds history no longer present in source table, ie the defining query does not match the information
stored", since I will take records which are created and not take updates on those records (in normal operations no delete possible on source tables). Which may lead selling_detail having records, which was updated in source table.
In my current version there is no MV but I will still like to know if I include updates also in my snapshot query then is it possible for higher version of Oracle to use query re-write at least till the time I keep the data in snapshot, remember I will like to ageout data more than two years old.
September 15, 2005 - 7:14 am UTC
I'm confused -- we seem to be in agreement that you cannot use a materialized view because of the "MV holding records not in the source"?
If so, then query rewrite isn't available.
Now, if the MV contains just two years extracted from the source table (source table is a superset of the MV), then query rewrite is something that is possible.
what about avoiding truncate and delete
Parvez, September 15, 2005 - 7:49 pm UTC
I understood the point about MV, but I still have one question as how to avoid truncate and also delete in case of complete refresh, since I am getting incremental data using complete refresh. I dont intened to use MV feature, I want data for my reports.
Is it possible through snapshot or should I write my own routine
September 15, 2005 - 8:42 pm UTC
Great it works !! Thanks
Parvez, September 16, 2005 - 3:28 am UTC
By reading the link somehow I got the idea that if I use refresh group instead on truncating it will delete, but now I tried and it works. I need to read more Oracle doc on this somewhere I miss the trick.
Snapshot Refresh
Jameel, October 25, 2005 - 7:57 am UTC
Hi Tom,
I created snapshots and scheduled the refresh jobs to run every 15 minutes. It worked for one month. From 10th of October the job is broken and the next_date is showing as 1/1/4000 and total_time 14696.
The script is given below
CREATE MATERIALIZED VIEW A_T
TABLESPACE D_DATA
NOCACHE
LOGGING
NOPARALLEL
USING INDEX
TABLESPACE D_INDEX
REFRESH FAST
START WITH TO_DATE('15-Oct-2005 09:15:00','dd-mon-yyyy hh24:mi:ss')
NEXT trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/900)+1)*15/24/60
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT * FROM A_T@MY.WORLD;
CREATE UNIQUE INDEX PK_A_T ON A_T
(A_T_ID)
LOGGING
TABLESPACE D_INDEX
NOPARALLEL;
My questions are
1.What went wrong to set the next_date to 1/1/4000?
2. How to identify the cause for the broken job (any dictionary views)?
October 26, 2005 - 7:09 am UTC
1) when the job fails 16 times in a row - it "breaks" itself and sets the next date to way in the future to stop it from failing.
2) see your alert log, a message is recorded there everytime the job failed.
Fast Refresh is not happening..
Avi, February 06, 2007 - 6:38 am UTC
Hi Tom,
I have created a MV on 98GB table with prebuilt table option and then did Fast refresh. Now problem is, after doing fast refresh count is not matching. On master site there are 140 Million recoreds but on MV site there are 130 million records. What could be the reason for this? It not returning any error also. Do I need to do complete refresh, which will take too much time.
Thanks & Regards,
Avi
Mumbai
February 06, 2007 - 11:18 am UTC
insufficient data to answer - please utilize support, they'll gather everything they need to diagnose this. They will need the precise set of steps you undertook.