Hi,
not at all. I think it can be worth it. I know that updatable join views
exist and that you can insert/update/delete rows from a table through
the view, but because I never do it. I was totally unaware that a view
is dependent on the index but it is not shown in dba_dependencies. So I
am probably not the only one.
/Jocke
2021-01-13 15:37 skrev Connor McDonald:
Jocke
Do you mind if I blog about just the unique index / view validation part ?
Cheers,
Connor
Connor McDonald
===========================
email: connor_mcdonald@xxxxxxxxx
blog: connor-mcdonald.com
twitter: connor_mc_d
On Monday, 11 January 2021, 04:57:45 pm AWST, Jocke Treugut
<jocke@xxxxxxxxxxxxxxxxxx> wrote:
Hi Connor.
Hope everything is fine with you. :-)
Yes, I was hoping to get around it by creating the view "with read only" then
I get NO för inserts, updates and deletes in user_updatable_columns. I even
joined the base table with dual. But I buy it.
The strange thing is that I have a anonymous pl/sql block that is looping a
number of calls to procedure inside a package, the procedure reads from this
view.
I start the loop. Drop the index. I see that the view, the package body and
the sql inside the package body become invalid, on the next iteration, the
sql gets a new plan_hash_value (fts instead of index unique scan) and becomes
valid, and also the view, but not the package body, the loop calls the
procedure inside the invalid package body, some more times without problems.
If I let the loop finish, the package body is still invalid and will be valid
on the next call. But if the loop is running and I start the same loop from
another session, it hangs on a library cache pin (on the package body)
because it wants to revalidate it, and it hangs there until the first loop
finish.
Maybe I am just curious why the view can become valid but not the package
body. There are no global variables, use of database types or anything inside
the package.
Take care!
/jocke
2021-01-10 23:54 skrev Connor McDonald:
From a colleague with code access
The invalidation is a catch all to ensure that updatable join views are
re-examined, because the existence/dropping of the unique index may alter
its "updateable-ness"
Cheers,
Connor
Connor McDonald
===========================
email: connor_mcdonald@xxxxxxxxx
blog: connor-mcdonald.com
twitter: connor_mc_d
On Thursday, 7 January 2021, 09:23:48 pm AWST, Jocke Treugut
<jocke@xxxxxxxxxxxxxxxxxx> wrote:
Hi Table,
for many many years ago, I read all the important Oracle manuals every year
(it was probably around six-eight on that reading list). I have probably not
done that so much since Oracle 11.
In the excellent Development Guide is the answers to many of my questions.
Yes, dropping a unique index makes views invalid if they reference a column
that existed in that index (is that necessary when the view is created with
read only, so no dml can happen through the view) and after that chapter
comes Using Edition-Based Redefinition.
The answers are out there :-)
Stay safe everyone and be happy!
/Jocke
