Oracle - Automatically Find and Compile Invalid Objects

Опубликовано: 23 Июль 2023
на канале: Ken Kim

1,159

Invalid Objects caused an outage when alter table caused dependent view to become invalid. Issue was not known until application users started calling the DBA.

In this session, I discuss workflow to manage invalid objects in PRODUCTION databases.

1. Automatically find invalid objects and send alert email to the DBA team in near-real-time (ie every 5 minutes)
2. Then stop sending duplicate emails by using the exclusion list table
3. Schedule recurring job to automatically compile the invalid object (especially if fix has been applied)
4. Lastly automatically delete the invalid object record from the exclusion list table

--exclusion list table
create table SYS.dba_invalid_object_exclusion_list(EXCLUSION_ID NUMBER(10)
GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER NOKEEP ) NOT NULL,
OWNER VARCHAR2(100 BYTE) NOT NULL,
OBJECT_NAME VARCHAR2(100 BYTE) NOT NULL,
OBJECT_TYPE VARCHAR2(100 BYTE) NOT NULL,
UPDATE_DT DATE DEFAULT SYSDATE);

alter table SYS.dba_invalid_object_exclusion_list add IGNORE CHAR(1) DEFAULT 'N' NOT NULL ;
alter table SYS.dba_invalid_object_exclusion_list MODIFY (IGNORE DEFAULT 'N') ADD CONSTRAINT CHECK_IGNORE_Y_N CHECK (IGNORE IN ('Y', 'N')); --Y/N
CREATE UNIQUE INDEX UQ_dba_invalid_object_exclusion_list_IDX ON SYS.dba_invalid_object_exclusion_list(owner, object_name, object_type); --prevent DUPLICATES

--insert invalid objects list that alert email will filter out
INSERT INTO SYS.dba_invalid_object_exclusion_list(owner, object_name, object_type)
--query for alert email notification
select owner, object_name, object_type
from dba_objects
where status = 'INVALID'
--and object_name = 'SP_NEVERFIXED'
and owner in ('SCOTT','PSTRIIM')
MINUS
select owner, object_name, object_type
from SYS.dba_invalid_object_exclusion_list;

update SYS.dba_invalid_object_exclusion_list set ignore = 'Y';
commit;

--script to compile invalid objects
BEGIN
FOR cur_rec IN (SELECT owner, object_name, object_type, DECODE(object_type, 'PACKAGE', 1,'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE status != 'VALID'
AND owner IN ('SCOTT','PSTRIIM')
AND object_name NOT IN (SELECT object_name FROM SYS.dba_invalid_object_exclusion_list WHERE IGNORE = 'Y')
ORDER BY owner, object_name, recompile_order)
LOOP
BEGIN
IF cur_rec.object_type NOT IN ('PACKAGE BODY') THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ELSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;

--IF compiled successfully DELETE FROM SYS.dba_invalid_object_exclusion_list
DELETE FROM SYS.dba_invalid_object_exclusion_list
WHERE owner = cur_rec.owner AND object_name = cur_rec.object_name AND object_type = cur_rec.object_type
AND 'VALID' = (select status from dba_objects obj where obj.owner = cur_rec.owner AND obj.OBJECT_NAME = cur_rec.object_name AND obj.OBJECT_TYPE = cur_rec.object_type AND obj.status = 'VALID');
COMMIT;

EXCEPTION
WHEN OTHERS THEN
CONTINUE;
END;
END LOOP;
END;
/

Смотрите видео Oracle - Automatically Find and Compile Invalid Objects онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Ken Kim 23 Июль 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,159 раз и оно понравилось 6 людям.

833