adop cutover

[Linda]

Hi Erman!
Happy New Year :) !

I have problem : developer has created "bad material.view" that eats up all resources..
Then he droppeed it. Now I m running cutover phase and oracle trying to re-create it every time running ADZDWRKR.sql
Any idea how I will stop it from re-creating as it can be dropped ?

Thanks,Linda

[ErmanArslansOracleBlog]

Hi Linda,

Thank you . Happy New Year to you too:)

1)How did you drop the MV? Which command? I m asking this because Online Patching enabled MVs are built on top of the following;

an ordinary view called MV_NAME||'#'.
an MV implementation using  AD_ZD_MVIEW.UPGRADE

So, after you drop the MV, did you check with "sqlplus <apps_user> @ADZDSHOWMV <MV_NAME>"?

2)When did you drop the MV?

Because there is warning about the cleanup phase ...  Here;
Do not drop an obsolete materialized view until the cleanup phase of patch execution.

Additional Info:
 I looked at the AD_ZD_MVIEW.DROP_MV and saw the following comment there;

 DROP_MV                                                        |
  |                                                                |
  |   - Drops the given materialized view and its logical view     |
  |     If Running in PATCH edition                                |
  |         Stores deferred DDL to drop MV in next CLEANUP         |
  |     If running in RUN edition                                  |
  |         Drops MV immediately                                   |
  |                  

[Linda]

Hi Erman!
Thanks for help:) Setting job_queue_processes=0 and  all dropping all related objects including the MV# as well helped.
One more question: we have those custom MV on the EBS tables and refresh 1h..
Developers needed to change those as MV restrictions on Online patching -
no functions in mv views and NOT having subqueries in mv view Select part..
Those modifications causing in some cases big perf degradation as plsql functoins were helping in perf...

So, what do you think if we dont for some views implement OLP requirements on own risk.
That means during online patching, I will put ONHOLD the refresh  and in test I will see if the changes coming to those underlying MV EBS tables and if need to change MV itself after patching.
Anything I missing here ?
the patching after we are on 12.2.6+all needed patches should be done for next 1-2 years ( optimistic here) :)

Br,Linda

[ErmanArslansOracleBlog]

Complying with the online patching standards is a hard thing.
Not all the EBS customers do their developments and deployments according to the Online patching.

So you are talking about not to implement online patching standard for your custom MV s?
This is totally acceptable. (if you are asking this :))

[ErmanArslansOracleBlog]

Bytheway,

setting job_queue_processes to 0 in such a situation is a cool move..
By this move, you are not letting MV refreshes to happen and this was your concern for the problematic MV.
However, job_queue_processes is used other places as well.
So, setting it to 0 is applicable for this specific problem, but care must be taken while setting it.

[Mark Chapell]

Hi,

have a look at

https://docs.oracle.com/database/121/ADFNS/adfns_editions.htm

section 24.1.1.2.1 Materialized Views

if your on database 12C you can reference PLSQL form MVIEWS....

Mark