Risk of dropping a view in Oracle database

Question

We have a legacy system running on a Oracle 19c database. I want to modify an existing view in Oracle database in the way that I want to change the ranking/ordering of something in the inner select query. The view name, output column names, etc will stay the same. I intend to keep everything the same except changing the ordering of some output data. I plan to do the change at a quiet time when no known systems are needing anything from this view.

As far as I know, the problem with modifying a view in Oracle is that there is no way of "alter"-ing a view. I will have to drop the view and then do a "create view", or do a "create or replace view" (if that makes a difference?)

Before I do that... my question is - what is the risk of doing so? If I drop a view and then recreate it with the same name and structure, will I be losing any dependencies of any kind, like something in the scheduled jobs, something in the packages, for examples? The project is huge and I can't go through everything when I don't know where to look.

As far as I can check on Oracle SQL Developer, I see that the current view has no triggers. I have noted down the current grants of the view and I can recreate the grants afterwards. I have noted down the list under the "Dependencies" tab - mainly package bodies and views. If I re-compiling those objects after I do the drop and create, will these objects be working again? Is there anything else I need to check before dropping and recreating the view?

Am I being stupid to drop and recreate the view? There must be a safer way to do a view modification, right?

Answer 1

It's no problem at all.

When you create or replace view, you will invalidate any cursors already loaded in the shared pool which will simply reload with a hard parse on the next execution - you won't even notice it. That sort of thing happens all the time and is invisible to the end user.

Dependent stored PL/SQL programs that have a hard dependency on the view will not go invalid as long as the column structure hasn't changed. So once again, no impact.

If, on the other hand, you drop the view then recreate it in a second step, dependent PL/SQL programs will be invalidated and have to be recompiled. But, they do so automatically on the first load by a session, so this too tends to be invisible.

Sessions currently querying the view also won't be impacted - the view is simply stored SQL which is read at query parse time. It isn't doing anything during the actual execution of a cursor that referenced it so you can freely modify it without interrupting or locking on currently ongoing queries.

So, I wouldn't sweat it - if the output column structure isn't changing and it returns the same data already expected, programs won't be affected unless they are row-order sensitive (and very few programs should be). Views get changed on the fly all the time.

Answer 2

I will have to drop the view and then do a "create view", or do a "create or replace view" (if that makes a difference?)

It does make a difference. From the documentation:

Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

It also talks about side-effect for triggers, but you said you don't have any; and materialized views, which you haven't mentioned.

The main benefit is that you don't have to recreate the grants.

Whether you drop and create, or create or replace, other dependant objects like packages and views will be invalidated - unless the view projection stays exactly the same, which seems to be the case here; in that case create or replace will not invalidate them. (Usually anyway, not sure if that's guaranteed).

But even if dependant objects are invalidated, they will automatically be recompiled when they are next referenced, making them valid again (assuming you haven't changed anything that breaks those references, like changing a column name) without you having to do anything.

You can see a same-projection create or replace leaving dependant objects valid, and a changed-projection create or replace invalidating them, and them becoming valid again when referenced, in this fiddle.

However, I would still only do this during a planned maintenance window, and would generally explicitly recompile anything invalid anyway to avoid surprises later, possibly using the compile_schema procedure.

---

I intend to keep everything the same except changing the ordering of some output data.

If you mean you have an order by clause on the main view query and that is what you plan to change, that might not be as reliable as you think; while you can do that and querying the view might usually return data in that order, it isn't guaranteed; you need an order-by on the query against the view really, not as part of the view definition. But that might not be what you mean, of course...