sexta-feira, 8 de março de 2024

[ORACLE] Batch change EDITIONABLE property.

Hello everyone.

Hope you're doing well!

Today, I have a simple case.
 
A test database had many database objects with the EDITIONABLE property set to "N".

Why did this happen? I have no idea!

Of course, you don't have any possibility to making the change manually, object by object.

This isn't a normal thing to happen, so... let's get to the solution.

A simple script solves the problem. 😆

But always remember:  
  • This step-by-step worked for me, but it may not work for you.
  • It's a test environment. The real life will be different, for sure.
  • This post is for study and testing as well and has no concern for performance and security best practices.

1) First, count the objects that will be changed.

SELECT
    substr(OWNER,1,20) AS OWNER,
    count(1) AS TOTAL,
    EDITIONABLE
FROM
    DBA_OBJECTS
WHERE
OWNER IN (
         'AUDIT_DBAMV','DBAADV','DBACP','DBADW','DBAMV',          
         'DBAPORTAL','DBAPS','DBASGU','DBATUALIZA','EDITOR','IDCE', 
         'MPACS','MVAPI','MVBIKE','MVINTEGRA','MVMOBILE','SITEDS')
AND 
OBJECT_TYPE IN (
         'FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE',
         'PROCEDURE', 'TRIGGER','TYPE','TYPE BODY',
         'SYNONYM','VIEW')
AND 
EDITIONABLE = 'N'
GROUP BY OWNER, EDITIONABLE
ORDER BY OWNER, EDITIONABLE;



2) Generate the script for change.

SET SERVEROUTPUT ON;
SET PAGES 0;
SET VERIFY OFF;
SET FEEDBACK OFF;
SET HEADING  OFF;
     
SELECT 
   'ALTER '||object_type || ' ' || owner || '.' || object_name || ' EDITIONABLE;'
FROM
DBA_OBJECTS 
WHERE
OWNER IN (
         'AUDIT_DBAMV','DBAADV','DBACP','DBADW','DBAMV',          
         'DBAPORTAL','DBAPS','DBASGU','DBATUALIZA','EDITOR','IDCE', 
         'MPACS','MVAPI','MVBIKE','MVINTEGRA','MVMOBILE','SITEDS')
AND 
OBJECT_TYPE IN (
         'FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE',
         'PROCEDURE', 'TRIGGER','TYPE','TYPE BODY',
         'SYNONYM','VIEW')
AND 
EDITIONABLE = 'N'
ORDER BY OWNER;

As simple as that.

But be careful. Some objects may be in heavy use at the moment, and because of this we can generate Library Cache Pin or Library Cache Lock waits - as in the image below. 
Therefore, it's important to run the script at a time when the database workload is low, monitoring the objects in use and the wait events.  



You can check the objects in the library cache using this SQL. This won't give you 100% certainty that the object is currently in use, but it's a good indicator.

SELECT 
     substr(OWNER,1,15) AS OWNER,
     substr(NAME,1,30)  AS NAME, 
     count(1) 
FROM
     GV$DB_OBJECT_CACHE 
WHERE
     NAME IN (
      'TRG_NOTA_FISCAL_CONV_PART','TRG_NOTA_FISCAL_FNFI',
      'TRG_NOTA_FISCAL_VALIDA_CNPJ','TRG_NOTA_FISCAL_PROIBE_CANC_NF',
      'TRG_NOTA_FISCAL_PARTICULAR','TRG_NOTA_FISCAL_AJUSTES',
      'TRG_NOTA_FISCAL_DADOS_NFE','TRG_NOTA_FISCAL_SN_CONTABILIZA',
      'TRG_EVOLUCAO_NOTA_FISCAL','TRG_FNFI_NF_INTEGRA_BENNER',
      'TRG_ATUALIZA_HIST_SIT_NF','TRG_STATUS_NFE_NOTA_FISCAL',
      'TRG_IMVW_OUT_NF_SUNAT')
GROUP BY OWNER, NAME;


3) Once again, count the objects with EDITIONABLE = N.



4) And count the objects with EDITIONABLE = Y.


I hope this simple tip helps you.

Regards
Mario





Postagem em destaque

[ORACLE] Quick tips: Autonomous Health Framework (AHF) files cleanup.

Hello everyone.   How are you doing?   Today, I going to show you a quick tip about the AHF - Autonomous Health Framework.   In case your au...