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##RMANADMIN
INTERPRETED
C##UWCLASS NATIVE
CTXSYS INTERPRETED
DBSFWUSER INTERPRETED
DBSNMP INTERPRETED
DVF INTERPRETED
DVSYS INTERPRETED
GGSHAREDCAP
INTERPRETED
GSMADMIN_INTERNAL INTERPRETED
LBACSYS INTERPRETED
MDSYS INTERPRETED
OLAPSYS INTERPRETED
OUTLN INTERPRETED
REMOTE_SCHEDULER_AGENT INTERPRETED
SYS INTERPRETED
SYS NATIVE
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##RMANADMIN
FALSE
C##UWCLASS FALSE
CTXSYS FALSE
DBSFWUSER FALSE
DBSNMP FALSE
DVF FALSE
DVSYS FALSE
GGSHAREDCAP
FALSE
GSMADMIN_INTERNAL FALSE
LBACSYS FALSE
MDSYS FALSE
OLAPSYS FALSE
OUTLN FALSE
REMOTE_SCHEDULER_AGENT FALSE
SYS 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##RMANADMIN
ENABLE:INFORMATIONAL,DISABLE:PERFORMANCE,ENABLE:SEVERE,DISABLE:
5004.DISABLE: 5005,
DISABLE: 5018,DISABLE 5021,DISABLE 6002,DISABLE:
6006,DISABLE: 6010
C##RMANADMIN
ENABLE:INFORMATIONAL,DISABLE:PERFORMANCE,ENABLE:SEVERE,DISABLE:
5004.DISABLE: 5005,
DISABLE: 5018,DISABLE 5021,DISABLE 6002,DISABLE:
6006,DISABLE: 6010
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
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##RMANADMIN
BYTE
C##UWCLASS
BYTE
CTXSYS
BYTE
DBSFWUSER
BYTE
DBSNMP
BYTE
DVF
BYTE
DVSYS
BYTE
GSMADMIN_INTERNAL BYTE
LBACSYS
BYTE
MDSYS
BYTE
OLAPSYS
BYTE
OUTLN
BYTE
REMOTE_SCHEDULER_AGENT BYTE
SYS
BYTE
WMSYS
BYTE
XDB
BYTE |