Rem
Rem $Header: opatchauto/oms/admin_recompile_invalid.sql /st_opatchauto_pt-24.1devplt/1 2024/07/19 07:46:42 sallam Exp $
Rem
Rem admin_nzdt_recompile.sql
Rem
Rem Copyright (c) 2017, 2024, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem   
Rem    NAME
Rem      admin_recompile_invalid.sql - <one-line expansion of the name>
Rem   
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    
Rem    MODIFIED   (MM/DD/YY)
Rem    sreddy      05/28/24 - recompile SYSMAN_ZDTUSER invalid synonyms
Rem    spramani    09/20/22 - PDBA deployment
Rem    spramani    08/16/22 - compilation fix for ADMIN
Rem    spramani    04/22/22 - re-create em-ad-objects
Rem    spramani    06/24/21 - nzdt compilation
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    spramani    05/06/09 - rcu compliance
Rem    spramani    05/05/09 - rcu complaince
Rem    jashukla    04/29/09 - Build break
Rem    smalathe    04/14/09 - Bug 8322782: Modify invalid objects query
Rem    chyu        03/09/09 - rcu compliance
Rem    edemembe    11/18/08 - Backport edemembe_bug-7477260 from main
Rem    aptrived    02/17/08 - Backport aptrived_bug-6820595 from main
Rem    aptrived    02/14/08 - Bug#6820595, should be able to take argument in
Rem                           lower case
Rem    edemembe    07/11/07 - Bug 6157164
Rem    edemembe    07/17/07 - Backport edemembe_bug-6157164 from main
Rem    jsadras     06/11/07 - Backport jsadras_bug-6120807 from
Rem                           st_emcore_10.2.0.3.1db11
Rem    jsadras     06/08/07 - Bug:6120807, reset package set to avoid running into 4061
Rem    jsadras     09/02/05 - Bug:4588918, recompile invalid objects 
Rem    chyu        04/20/05 - removing the logging 
Rem    chyu        04/05/05 - shortening the db logging to avoid buffer 
Rem                           overflow issues 
Rem    chyu        03/22/05 - commenting out the raise exception 
Rem    rpinnama    02/27/03 - Parameterize the user name
Rem    rpinnama    09/26/02 - Remove exit at the end
Rem    rpinnama    05/16/02 - rpinnama_reorg_rep_scripts_2
Rem    rpinnama    05/16/02 - Created
Rem   

Rem   
Rem This SQL script requires the following arguments
Rem  1. Name of the repository user
Rem
DEFINE RECOMPILE_REPOS_USER = "&1"

DECLARE
l_max_recompile_attempts NUMBER := 5;
l_recompile_attempts NUMBER := 0;
l_invalid_count NUMBER := 0;
l_nzdt_compile_needed NUMBER := 0;
l_privs_present NUMBER := 0;
l_em_ad_obj_present NUMBER :=0;
l_cursor_prop          SYS_REFCURSOR;
l_em_ad_obj_exists NUMBER := 0;
l_old_compile_needed NUMBER := 0;
uname VARCHAR2(255);

pre_count number;
curr_count number;

all_pre_count number;
all_curr_count number;

ado_pre_count number;
ado_curr_count number;

retry_count number;

compile1 number;
compile2 number;
compile3 number; 
l_code NUMBER;
l_errm VARCHAR2(128);

BEGIN 
    insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', '======== new compile session=====', sysdate);

    select sys_context( 'userenv', 'session_user' ) into uname from dual;
    insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'to compile schema: ' || '&RECOMPILE_REPOS_USER'||', session user: ' || uname, sysdate);
    IF ( uname != 'SYSMAN') THEN 
        BEGIN
           -- User is SYS or ADMIN in ADBs or LCM ADMIN `
        insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', uname||' user', sysdate);
        select count(*) into l_nzdt_compile_needed from DBA_OBJECTS where object_name='DBA_EDITION_INHERITED_OBJECTS' and owner ='SYS';
        IF l_nzdt_compile_needed = 1 THEN
            BEGIN
                -- do all prereqs 
                -- DB patch is available 
                -- new compilation 
                -- check EM_AD_OBJECTS and grants 
                insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'found DB patch for DBA_EDITION_INHERITED_OBJECTS', sysdate); 
                select count(*) into l_privs_present from dba_tab_privs where grantee = 'SYSMAN' and table_name='DBA_EDITION_INHERITED_OBJECTS' and privilege = 'SELECT';
                IF l_privs_present = 0 THEN
                    BEGIN
                        insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', ' missing grants on  DBA_EDITION_INHERITED_OBJECTS', sysdate);
                       execute immediate 'Grant select on SYS.DBA_EDITION_INHERITED_OBJECTS to SYSMAN';
                       execute immediate 'grant select on SYS.DBA_OBJECTS_AE to SYSMAN'; 
                     EXCEPTION 
                          WHEN OTHERS THEN  
                             RAISE;
                     END;
                END IF;

                select count(*) into l_em_ad_obj_present from DBA_OBJECTS where object_name='EM_AD_OBJECTS' and owner ='SYSMAN';
                IF l_em_ad_obj_present = 0 THEN 
                     BEGIN
                       execute immediate 'create or replace view SYSMAN.em_ad_objects (OWNER, OBJECT_NAME, OBJECT_TYPE, ACTUAL, STATUS, CTIME, MTIME, STIME) as select substrb(owner, 1, 30) owner , object_name , object_type , '|| chr(39)||'Y'||chr(39)||' , status , CREATED , LAST_DDL_TIME , to_date(TIMESTAMP, '|| chr(39) || 'YYYY-MM-DD:HH24:MI:SS' ||chr(39) ||') from SYS.DBA_OBJECTS_AE where (edition_name = sys_context('|| chr(39) || 'userenv' ||chr(39)||', '|| chr(39) || 'current_edition_name'||chr(39)||') or edition_name is null) and object_type <> ' || chr(39) || 'DATABASE LINK' || chr(39) || ' and object_type <> ' || chr(39) || 'NON-EXISTENT' || chr(39) || ' union all select substrb(owner, 1, 30) owner , object_name , object_type , '|| chr(39) || 'N' || chr(39) || ' , stub_status , CREATED , LAST_DDL_TIME , to_date(TIMESTAMP, ' || chr(39) || 'YYYY-MM-DD:HH24:MI:SS' || chr(39) || ') from SYS.DBA_EDITION_INHERITED_OBJECTS';
                       insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'created SYSMAN.em_ad_objects', sysdate);
                       execute immediate 'grant execute on SYS.UTL_RECOMP to SYSMAN';
                       execute immediate 'grant select on SYS.UTL_RECOMP_INVALID_ALL to SYSMAN';
                       insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', ' SYSMAN is granted to SYS.UTL_RECOMP_INVALID_ALL, SYS.DBA_EDITION_INHERITED_OBJECTS, SYS.DBA_OBJECTS_AE, created SYSMAN.em_ad_objects', sysdate);
                     EXCEPTION 
                          WHEN OTHERS THEN  
                             RAISE;
                     END;
                END IF;
            END; -- done prereqs
            BEGIN  -- begin new compile 
                        -- DB patch is there
                        -- grants and EM_AD_OBJECTS there 
                        -- do new compilation 
                        insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'executing:  ' ||  ' nzdt compile for ' || '&RECOMPILE_REPOS_USER', sysdate);
                        sys.utl_recomp.recomp_parallel(schema => '&RECOMPILE_REPOS_USER');
                        retry_count := 0;
     

                        pre_count := 999999999;
                        all_pre_count := 99999999;
                        ado_pre_count := 99999999;
                        compile1 := 0;
                        compile2 := 0;
                        compile3 :=0;
                        loop
      
                            begin
                                retry_count := retry_count + 1;
                                -- select count(*) into curr_count from SYS.UTL_RECOMP_INVALID_ALL where status='5';
                                OPEN l_cursor_prop FOR '
                                   select count(*) from SYS.UTL_RECOMP_INVALID_ALL where status=' || chr(39) || '5' ||chr(39)||' ';             
                                FETCH l_cursor_prop INTO curr_count;
                                CLOSE l_cursor_prop;

                                -- select count(*) into ado_curr_count from AD_OBJECTS where status='INVALID';
                                OPEN l_cursor_prop FOR '
                                   select count(*) from SYSMAN.EM_AD_OBJECTS where status=' || chr(39) || 'INVALID' ||chr(39)||' ';             
                                FETCH l_cursor_prop INTO ado_curr_count;
                                CLOSE l_cursor_prop;
                                select count(*) into all_curr_count from ALL_OBJECTS where status='INVALID' and owner='&RECOMPILE_REPOS_USER';

                                if ((curr_count = 0) or (curr_count = pre_count)) then 
                                    compile1 := 1;
                                end if;
                                if ((all_curr_count = 0) or (all_curr_count = all_pre_count)) then 
                                    compile2 := 1;
                                end if;
                                if ((ado_curr_count = 0) or (ado_curr_count = ado_pre_count)) then 
                                    compile3 := 1;
                                end if;

                                exit when ((compile1 = 1) and (compile2 = 1) and (compile3 =1));


                                sys.utl_recomp.recomp_parallel(schema => '&RECOMPILE_REPOS_USER');
                                pre_count := curr_count;
                                all_pre_count := all_curr_count;
                                ado_pre_count := ado_curr_count;
                                insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'compile retry count :  ' ||to_char(retry_count) || ' in schema: &RECOMPILE_REPOS_USER', sysdate);
           
                             exception 
                                 when others
                                 then 
                                     l_code := SQLCODE;
                                     l_errm := SUBSTR(SQLERRM, 1 , 128);
                                     insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'exception in nzdt compilation: ' || to_char(l_code)||' -ERROR- '||l_errm, sysdate);
                             end;

                         end loop;
                         insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', ' In dba_objects INVALID: ' || all_curr_count, sysdate);
                         insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'Internal  INVALID: ' || curr_count, sysdate);
                         insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'EM_AD_OBJESTS  INVALID: ' || ado_curr_count, sysdate);
                         insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'Total retry: ' || retry_count, sysdate);
            END; --end new compile
        ELSE  
                 insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'DB patch is not there', sysdate);
                 l_old_compile_needed := 1;
        END IF; -- privilege to SYS.DBA_EDITION_INHERITED_OBJECTS
        END;
    ELSE 
         insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'NOT SYS user ', sysdate);
         l_old_compile_needed := 1;
    END IF; -- sys user  
  IF  l_old_compile_needed = 1 THEN  
  insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', 'DO old compilation', sysdate); 
  -- Check if there are any invalid objects.
  SELECT COUNT(*)
    INTO l_invalid_count
    FROM dba_objects
   WHERE status = 'INVALID'
     AND owner = upper('&RECOMPILE_REPOS_USER')
     AND (object_type IN ('FUNCTION', 'PACKAGE',
                          'PACKAGE BODY', 'PROCEDURE', 
                          'TRIGGER', 'VIEW',
                          'TYPE BODY', 'MATERIALIZED VIEW')
          OR (object_type = 'TYPE' AND subobject_name IS NULL));

  IF l_invalid_count = 0 THEN
    RETURN;
  END IF;

  WHILE (l_invalid_count > 0 AND 
            l_recompile_attempts < l_max_recompile_attempts)
    LOOP
      -- Use the built in RDBMS recompile first.
      dbms_utility.compile_schema(upper('&RECOMPILE_REPOS_USER'),FALSE);

      -- If there are any invalid objects left, try to recompile them.
      FOR crec IN (SELECT object_name, object_type,
                   DECODE(object_type, 'TYPE',              1,
                                       'FUNCTION',          2,
                                       'PROCEDURE',         3,
                                       'TRIGGER',           4,
                                       'VIEW',              5,
                                       'PACKAGE',           6,
                                       'TYPE BODY',         7,
                                       'PACKAGE BODY',      8,
                                       'MATERIALIZED VIEW', 9,
                                                           10) object_type_seq
                   FROM dba_objects
                  WHERE owner = upper('&RECOMPILE_REPOS_USER')
                    AND status ='INVALID'
                    AND (object_type IN ('FUNCTION', 'PACKAGE',
                                         'PACKAGE BODY', 'PROCEDURE',
                                         'TRIGGER', 'VIEW', 'TYPE BODY', 
                                         'MATERIALIZED VIEW')
                         OR (object_type = 'TYPE' AND subobject_name IS NULL))
                  ORDER BY object_type_seq, object_type, created)
      LOOP
        BEGIN
          IF (crec.object_type = 'PACKAGE BODY') THEN
            -- If package body is invalid, just compile the body and not
            -- the specification
            EXECUTE IMMEDIATE 'ALTER PACKAGE "' ||
                            crec.object_name || '" compile body';
          ELSIF (crec.object_type = 'TYPE')
           THEN
           -- If type spec is invalid, just compile the specification and not
           -- the body
           EXECUTE IMMEDIATE 'ALTER TYPE "' ||
                          crec.object_name || '" compile specification';
          ELSIF (crec.object_type = 'TYPE BODY')
           THEN
           -- If type body is invalid, just compile the body and not
           -- the specification
           EXECUTE IMMEDIATE 'ALTER TYPE "' ||
                          crec.object_name || '" compile body';
          ELSE
           EXECUTE IMMEDIATE 'ALTER ' || crec.object_type || ' "' ||
                           crec.object_name || '" compile';
          END IF;
        EXCEPTION
           WHEN OTHERS THEN
             NULL; 
        END;
      END LOOP;  
      -- For loop recompile

      -- Check if there are any invalid objects.
      SELECT COUNT(*)
         INTO l_invalid_count
         FROM dba_objects
       WHERE status = 'INVALID'
         AND owner = upper('&RECOMPILE_REPOS_USER')
         AND (object_type IN ('FUNCTION', 'PACKAGE',
                              'PACKAGE BODY', 'PROCEDURE', 
                              'TRIGGER', 'VIEW',
                              'TYPE BODY', 'MATERIALIZED VIEW')
              OR (object_type = 'TYPE' AND subobject_name IS NULL));

      l_recompile_attempts := l_recompile_attempts + 1;
    END LOOP;  
    -- While loop
END IF;

-- drop and recreate invalid private synonyms pointing to repository owner
-- to make them valid.
FOR rec IN ( SELECT ds.synonym_name,do.owner,ds.table_owner,ds.table_name
               FROM dba_objects do,
                    dba_synonyms ds
              WHERE do.status       = 'INVALID' 
                AND do.owner        in ('SYSMAN_RO','MGMT_VIEW', 'SYSMAN_ZDTUSER') 
                AND do.object_type  = 'SYNONYM'
                AND ds.owner        in ('SYSMAN_RO','MGMT_VIEW', 'SYSMAN_ZDTUSER') 
                AND ds.table_owner  = upper('&RECOMPILE_REPOS_USER')
                AND ds.synonym_name = do.object_name)
LOOP
  BEGIN
    EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '||rec.owner||'.'||rec.synonym_name 
                       ||' FOR '|| rec.table_owner||'.'||rec.synonym_name ;
  EXCEPTION 
    WHEN OTHERS THEN NULL ;
  END ;
END LOOP ;

-- Reset package state to avoid running into 4061/4068 errors
   DBMS_SESSION.RESET_PACKAGE; 
    insert into SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'nzdt_compile', 'nzdt_compile_id', '========  end compile session=====', sysdate);

END;
/

