Is there a better way to...
to get the Latest Data rather than Using MV's??
Hi,
Ours being an OLTP 8i Database we are using Mv's to refresh the data, but one particular MV doesnt get refreshed from shell script at 5.00 am everyday. This particular MV is a Resultset of 3 Mv's which get refreshed within the same script without any problem.
The Total Number of Rows to be Processed by the Troubled process is 790368.
The Notable part here is the MV gets created when i try doing the Same from Sql Prompt but i will be doing this at a much later time say 8.00/9.00 am.
so what i did was Instead of Mv i Created it as a Table - but i will be Dropping the Table before Recreating it everytime (i am using CTAS ie. create table t1 as select ...)- But unfortunately this was also failing and i found that there was a TX level lock on OBJ$ BY SYS. This particular process will never go through.
i tried putting the process to Sleep for 4 minutes before recreating but it also didnt help so as a last try i Truncated the Table and Tried INserting - It Worked but it Still needed a sleep time of 4 minutes(??).
At the end of All this It looks like Recreating Tables are far more Better than Refreshing MV's - Is it True or am i missing something??
Also is there a better and Faster way to do Data Refresh?
Also why does it need a Sleep Time???
Also i dont see any active process going on around 5.00 am
Please advice
