Hi again.
If anyone was interested in recreating the problem (it behaves the same
in 12.2 and 19):
CREATE TABLE objects AS SELECT ROWNUM AS object_id, x.* FROM ( SELECT
DISTINCT owner, object_name FROM all_objects WHERE subobject_name IS
NULL AND ROWNUM < 100 ) x;
SELECT COUNT( * ), COUNT( object_id ), COUNT( DISTINCT object_id ),
COUNT( owner || object_name ), COUNT( DISTINCT owner || object_name )
FROM objects;
CREATE UNIQUE INDEX ix_objects_unique ON objects( object_id );
CREATE UNIQUE INDEX ix_objects_nonuniq ON objects( owner, object_name );
CREATE OR REPLACE VIEW v_obj_id AS SELECT object_id, owner, object_name
FROM objects;
CREATE OR REPLACE VIEW v_obj_name AS SELECT owner, object_name FROM
objects;
CREATE OR REPLACE VIEW v_obj_name2 AS SELECT owner, object_name FROM
objects WHERE object_id != 42;
CREATE OR REPLACE PROCEDURE p_obj_id AS
v_owner VARCHAR2(100);
BEGIN
SELECT owner INTO v_owner FROM v_obj_id WHERE ROWNUM =1;
END;
/
CREATE OR REPLACE PROCEDURE p_obj_name AS
v_owner VARCHAR2(100);
BEGIN
SELECT owner INTO v_owner FROM v_obj_name WHERE ROWNUM =1;
END;
/
CREATE OR REPLACE PROCEDURE p_obj_name2 AS
v_owner VARCHAR2(100);
BEGIN
SELECT owner INTO v_owner FROM v_obj_name2 WHERE ROWNUM =1;
END;
/
SELECT status, created, last_ddl_time, timestamp, object_type,
object_name FROM user_objects WHERE object_name LIKE '%OBJ%' ORDER BY
object_type, object_name;
DROP INDEX ix_objects_unique;
SELECT status, created, last_ddl_time, timestamp, object_type,
object_name FROM user_objects WHERE object_name LIKE '%OBJ%' ORDER BY
object_type, object_name;
-- All the views that are using OBJECT_ID are now INVALID, and the
procedures (of course) that are dependent on these views
Take care /Jocke
2021-01-06 18:48 skrev Jocke Treugut:
Mighty knights and ladies.
After almost 30 years with Oracle databases, I came across something that I
had no clue about. Wow, I still learn :-) But maybe this is old knowledge for
you.
If someone had asked me before yesterday, will drop index invalidate views. I
would answer "no". So wrong I was.
Our production database 12.2 with compatible set to 12.1 encountered this
situation:
Developers uses continuous delivery to deploy to production almost whenever
they want.
Their script contained four statements: DROP INDEX <name> ONLINE;
After that we had huge problems for 13 minutes until one dba executed alter
system flush shared_pool.
One of the indexes were unique (created with create unique index, did not
have a unique constraint) and consisted of four columns.
The table that this unique index belonged to, had several views that included
this table and some/all of the columns in the unique index - none of them had
joins inside them, just some columns from a single table with some
where-conditions.
One view was referenced from a procedure inside a package.
All the views that included at least one column from this unique index, went
invalid, and so did the package body.
One of the procedures inside this package is called about 4000+ times every
second from 200-300 sessions. The procedure has an OUT-parameter, a ref
cursor. The select used for this ref-cursor has a stored function in the
select-list that comes from the same package.
One of these sessions discovered that the view was invalid and did an
automatic recompile and another session recompiled the package body some
milliseconds later. I guess that the first session probably wanted to
recompile the body also and started with the dependent view.
But the problem was not solved. 100+ sessions started to wait on "library
cache revalidation" and a lot of them received ORA-4023 object can not be
validated or authorized. I am quite sure that the developers have not added
any calls to dbms_session.reset_package (or is it dbms_utility), maybe they
should?
Flushing out the SQL that called the procedure with dbms_shared_pool didn't
help. Recompiling the body manually didn't help. Until one dba in frustration
issued flush shared_pool.
* Is getting invalid views when dropping a unique index, expected behaviour,
bug or documentation bug?
* Our CTO and our architects were quite upset that Oracle couldn't handle
this situation. 4000 executions per second were not an excuse. How do you
handle situations in a very active database when you need to replace a
package body? The non-Oracle developers want to deploy whenever during the
day, even when stock markets open and close. We don't have any maintenance
windows - never. The DBAs have no problems to do some critical changes
off-hours but the developers don't want to remember routines and have
restrictions.
* Does anyone have an idea what happened? There was no blocking sid, just a
lot of sessions waiting for lc revalidation for a very long time. A call to
the stored procedure takes about 0.5 milliseconds, so it is not a long
running query or so.
* If anyone wants to share how you do continuous delivery in a very active
database, I would be more than happy.
Afterwards I tried to understand why a view would be invalid if it contained
a column that also belonged to a unique index. I tried to have the view both
as editionable (found one bug on this) and noneditionable. Tried to set the
index both as unusable and invisible before dropping, trying to drop with
"deferred invalidation" (I think it was called), using online or not when
dropping, created the view with WITH READ ONLY and CHECK OPTION, but couldn't
come up with any workaround. It was a non-partitioned table and a normal
unique index.
Stay healthy and happy!
/Jocke
