Generate script for Database securities : Compile Object on Oracle Databases 10g onward
Database security (Recompiling)
Each database product like Oracle has a particular security model and the DBA should be well aware in order to control access to data. This is part of authorization, Authorization is granting permission on various parts of the database.
| ***note***
You can use just UTLRP for all invalid objects If you are not sure which type of object the user wants to recompile or not just used all scripts as below. |
Recompiling All Invalid Objects
Oracle recommends you run the utlrp.sql script after you install, patch, or upgrade a database, to identify and recompile invalid objects.
| The utlrp.sql script recompiles all invalid objects. Run the script immediately after installation, to ensure that users do not encounter invalid objects.
Start SQL*Plus: # sqlplus “/ AS SYSDBA” Run the utlrp.sql script, where Oracle_home is the Oracle home path: SQL> @<Oracle_home>/rdbms/admin/utlrp.sql |
Recompiling manual (keep values before, You do the task)
1.
— GEN SCRIPT COMPILES ONLY OBJ ‘PROCEDURE’,’PACKAGE’,’FUNCTION’,’TRIGGER’,’PACKAGE BODY’
SELECT 'EXEC DBMS_DDL.ALTER_COMPILE('''||OBJECT_TYPE||''','''||OWNER||''','''||OBJECT_NAME||''');'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
AND OBJECT_TYPE IN ('PROCEDURE','PACKAGE','FUNCTION','TRIGER','PACKAGE BODY')
ORDER BY OWNER,OBJECT_TYPE;
–This method is limited to PL/SQL objects, so it is not applicable for views.
2.
— GEN SCRIPT COMPILES ONLY OBJ ‘VIEW’
SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
AND OBJECT_TYPE IN ('VIEW')
ORDER BY OWNER,OBJECT_TYPE;
3.
— GEN SCRIPT COMPILES ONLY JAVA OBJ
SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'."'||OBJECT_NAME||'" COMPILE;' FROM DBA_OBJECTS WHERE OBJECT_TYPE LIKE '%JAVA%' AND STATUS='INVALID' AND OWNER NOT IN ( 'DBSNMP', 'SYSMAN', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'FLOWS_FILES', 'MDSYS', 'ORDSYS', 'EXFSYS', 'SCOTT', 'WMSYS', 'ORACLE_OCM', 'APPQOSSYS', 'XS$NULL', 'APEX_030200', 'OWBSYS_AUDIT', 'MDDATA', 'ORDDATA', 'CTXSYS', 'ANONYMOUS', 'OUTLN', 'DIP', 'APEX_PUBLIC_USER', 'XDB', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'ORDPLUGINS', 'OWBSYS', 'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'PUBLIC','TSMSYS') ORDER BY OWNER,OBJECT_NAME;