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
