Oracle PL/SQL Object Settings
Version 21c

Demo
conn / as sysdba

desc dba_plsql_object_settings

set linesize 121

-- objects types where warnings can be accessed by owner
SELECT DISTINCT type
FROM dba_plsql_object_settings
ORDER BY 1;

TYPE
------------
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY


SELECT DISTINCT owner, type
FROM dba_plsql_object_settings
ORDER BY 1,2;

-- available optimizer levels ... requires 2
SELECT DISTINCT owner, plsql_optimize_level
FROM dba_plsql_object_settings
ORDER BY 2,1;

-- current optimizer level for the instance
SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_dynamic_sampling';

NAME                        VALUE
--------------------------- ------
optimizer_dynamic_sampling  2


-- compilation can be INTERPRETED or NATIVE
col plsql_code_type format a30

SELECT DISTINCT owner, plsql_code_type
FROM dba_plsql_object_settings
ORDER BY 1,2;

OWNER                   PLSQL_CODE_TYPE
----------------------- --------------------
AUDSYS                  INTERPRETED
C##UWCLASS              NATIVE
CTXSYS                  INTERPRETED
DBSFWUSER               INTERPRETED
DBSNMP                  INTERPRETED
DVF                     INTERPRETED
DVSYS                   INTERPRETED
GSMADMIN_INTERNAL       INTERPRETED
LBACSYS                 INTERPRETED
MDSYS                   INTERPRETED
OLAPSYS                 INTERPRETED
ORACLE_OCM              INTERPRETED
ORDPLUGINS              INTERPRETED
ORDSYS                  INTERPRETED
OUTLN                   INTERPRETED
REMOTE_SCHEDULER_AGENT  INTERPRETED
SYS                     INTERPRETED
SYS                     NATIVE
SYSTEM                  INTERPRETED
WMSYS                   INTERPRETED
XDB                     INTERPRETED


-- were objects compiled in debug mode?
col plsql_debug format a30

SELECT DISTINCT owner, plsql_debug
FROM dba_plsql_object_settings
ORDER BY 1;

OWNER                   PLSQL_DEBUG
----------------------- --------------------
AUDSYS                  FALSE
C##UWCLASS              FALSE
CTXSYS                  FALSE
DBSFWUSER               FALSE
DBSNMP                  FALSE
DVF                     FALSE
DVSYS                   FALSE
GSMADMIN_INTERNAL       FALSE
LBACSYS                 FALSE
MDSYS                   FALSE
OLAPSYS                 FALSE
ORACLE_OCM              FALSE
ORDPLUGINS              FALSE
ORDSYS                  FALSE
OUTLN                   FALSE
REMOTE_SCHEDULER_AGENT  FALSE
SYS                     FALSE
SYSTEM                  FALSE
WMSYS                   FALSE
XDB                     FALSE


-- what warnings are in place
col plsql_warnings format a50

SELECT DISTINCT owner, plsql_warnings
FROM dba_plsql_object_settings
ORDER BY 1;

OWNER                   PLSQL_WARNINGS
----------------------- -------------------------
AUDSYS
                 DISABLE:ALL
C##UWCLASS
             DISABLE:ALL,ENABLE: 6009
C##UWCLASS              ENABLE:ALL
CTXSYS
                 DISABLE:ALL
DBSFWUSER
              DISABLE:ALL
DBSNMP
                 DISABLE:ALL
DVF
                     DISABLE:ALL
DVSYS
                  DISABLE:ALL
GSMADMIN_INTERNAL       DISABLE:ALL
LBACSYS
                 DISABLE:ALL
MDSYS
                  DISABLE:ALL
OLAPSYS
                DISABLE:ALL
ORACLE_OCM
             DISABLE:ALL
ORDPLUGINS
             DISABLE:ALL
ORDSYS
                  DISABLE:ALL
OUTLN
                   DISABLE:ALL
REMOTE_SCHEDULER_AGENT  DISABLE:ALL
SYS         
           DISABLE:ALL
SYS
                     ENABLE:ALL
SYSTEM
                  DISABLE:ALL
WMSYS
                   DISABLE:ALL
XDB
                    DISABLE:ALL

-- length semantics for objects
col nls_length_semantics format a30

SELECT DISTINCT owner, nls_length_semantics
FROM dba_plsql_object_settings
ORDER BY 1;

OWNER                   NLS_LENGTH_SEMANTICS
----------------------- --------------------
AUDSYS                  BYTE
C##UWCLASS              BYTE
CTXSYS                  BYTE
DBSFWUSER               BYTE
DBSNMP                  BYTE
DVF                     BYTE
DVSYS                   BYTE
GSMADMIN_INTERNAL       BYTE
LBACSYS                 BYTE
MDSYS                   BYTE
OLAPSYS                 BYTE
ORACLE_OCM              BYTE
ORDPLUGINS              BYTE
ORDSYS                  BYTE
OUTLN                   BYTE
REMOTE_SCHEDULER_AGENT  BYTE
SYS                     BYTE
SYSTEM                  BYTE
WMSYS                   BYTE
XDB                     BYTE

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_WARNING
PL/SQL Warnings
Stored Procedures
Table Triggers
Types
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx