Oracle DBMS_PREUP
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Procedures and functions used to perform checks on a database which is getting ready to be upgrade.
Note Constants and subprograms listed here are only those that are publicly accessible. Additional constants and subprograms are private to the package body.
A list of all CHECK_NAME values can be found in the INIT_PREUPCHECKS procedure local to the package body (see source code).

The demos below were run against a 18.1.0.0.
AUTHID DEFINER
Constants
Name Data Type Value
c_build NUMBER '1'
c_success NUMBER 1
c_failure NUMBER 2
debug BOOLEAN FALSE
pDBGSizeResources BOOLEAN FALSE
Fixup Script Name Constants
C_FIXUP_SCRIPT_NAME_PRE_BASE VARCHAR2(30) 'preupgrade_fixups'
C_FIXUP_SCRIPT_NAME_POST_BASE VARCHAR2(30) 'postupgrade_fixups';
Fixup Script Name Variables
C_FIXUP_SCRIPT_NAME_PRE VARCHAR2(256); NULL
C_FIXUP_SCRIPT_NAME_POST VARCHAR2(256) NULL
Flashback Log Size
C_MIN_FLASHBACK_KB_PER_PDB NUMBER 333 * 1024
Indexes By Pool Name
cs_idx V$PARAMETER.NAME%TYPE 'db_cache_size'
jv_idx V$PARAMETER.NAME%TYPE 'java_pool_size'
lp_idx V$PARAMETER.NAME%TYPE 'large_pool_size'
mt_idx V$PARAMETER.NAME%TYPE 'memory_target'
pt_idx V$PARAMETER.NAME%TYPE 'pga_aggregate_target'
st_idx V$PARAMETER.NAME%TYPE 'sga_target'
sp_idx V$PARAMETER.NAME%TYPE 'shared_pool_size'
sr_idx V$PARAMETER.NAME%TYPE 'streams_pool_size'
Severity Check Level
c_check_level_success NUMBER 1
c_check_level_warning NUMBER 2
c_check_level_info NUMBER 3
c_check_level_error NUMBER 4
c_check_level_recommend NUMBER 5
Severity Check Type
c_param_type_version NUMBER -1
c_param_type_other NUMBER 0
c_param_type_string NUMBER 2
c_param_type_number NUMBER 3
c_param_type_number_alt NUMBER 6
Substitution Delimiter
C_SUBSTITUTION_DELIMITER_OPEN CHAR(1) '{'
C_SUBSTITUTION_DELIMITER_CLOSE CHAR(1) '}'
Data Types TYPE string_array_t IS TABLE OF VARCHAR2(32767);
TYPE string_array_collection_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TYPE detail_t IS RECORD (
detail      VARCHAR2(4000),
detail_type VARCHAR2(30));

TYPE number_array_t IS TABLE OF NUMBER;

TYPE messagevalue_t is RECORD (
position NUMBER,
value CLOB);

TYPE messagevalues_t IS TABLE OF messagevalue_t INDEX BY BINARY_INTEGER;
TYPE message_t is RECORD (
-- msg_text VARCHAR2(4000), -- aka the RULE
-- cause VARCHAR2(4000), -- aka the BROKEN_RULE
-- action VARCHAR2(4000),
-- detail detail_t
id            VARCHAR2(4000),
messagevalues messagevalues_t);

TYPE fixup_t IS RECORD (
fixup_type VARCHAR2(30),
fixAtStage VARCHAR2(30));

TYPE component_t IS RECORD (
cid     VARCHAR2(30), -- component id
cname   VARCHAR2(45), -- component name
script  VARCHAR2(128), -- upgrade script name
version VARCHAR2(30), -- version
status  VARCHAR2(15), -- component status
install BOOLEAN);

TYPE components_t IS TABLE OF component_t INDEX BY BINARY_INTEGER;

TYPE tablespace_t IS RECORD (
name            VARCHAR2(128),
additional_size INTEGER,
min             INTEGER,
alloc           INTEGER,
inc_by INTEGER,
fauto BOOLEAN,
contents SYS.dba_tablespaces.contents%type);

TYPE tablespaces_t IS TABLE OF tablespace_t INDEX BY BINARY_INTEGER;

TYPE archivelogs_t IS RECORD (
name            VARCHAR2(128),
additional_size INTEGER);

TYPE flashbacklogs_t IS RECORD (
name            VARCHAR2(128),
additional_size INTEGER);

TYPE rollback_segment_t IS RECORD (
name     VARCHAR2(128),
tablespc VARCHAR2(128),
status   VARCHAR2(31),
auto     INTEGER,
inuse    INTEGER,
next     INTEGER,
max_ext  INTEGER);

TYPE rollback_segments_t IS TABLE OF rollback_segment_t
INDEX BY BINARY_INTEGER;

TYPE flashback_info_t IS RECORD (
name         VARCHAR2(513), -- name
limit        INTEGER, -- space limit
used         INTEGER, -- Used
dsize        INTEGER, -- db_recovery_file_dest_size
reclaimable  INTEGER,
files        INTEGER, -- number of files
min_fra_size INTEGER);

TYPE fra_info_t IS RECORD (
name V$RECOVERY_FILE_DEST.NAME%TYPE, -- name/path
limit              INTEGER, -- db_recovery_file_dest_size (bytes)
used               INTEGER, -- Used (bytes)
dsize              INTEGER, -- destination size
reclaimable        INTEGER, -- bytes reclaimable
files              INTEGER, -- number of files
avail              INTEGER, -- bytes available in FRA
min_archive_gen    INTEGER, -- minimum archive logs (bytes) estimated
-- to be generate during upgrade
min_flashback_gen  INTEGER, -- rough minimum flashback logs (bytes)
-- to be generated during upgrade
min_fra_size       INTEGER, -- new db_recovery_file_dest_size to set
min_freespace_reqd INTEGER, -- min free space needed for logs
-- to be generated during upgrade
additional_size    INTEGER); -- additional size + limit = min_fra_size

-- TYPE ARCHive DESTination RECORD Type
-- stores info from v$archive_dest if there's at least 1 non-fra destination
TYPE archiveDest_info_t IS RECORD (
dest_name       V$ARCHIVE_DEST.DEST_NAME%TYPE, -- log_archive_dest_<N>
destination     V$ARCHIVE_DEST.DESTINATION%TYPE, -- destination <path>
status          V$ARCHIVE_DEST.STATUS%TYPE, -- e.g., VALID/INACTIVE
min_archive_gen NUMBER); -- min free space needed for archivelogs
-- to be generated during upgrade

TYPE systemresource_t IS RECORD (
tablespaces       tablespaces_t,
archivelogs       archivelogs_t,
flashbacklogs     flashbacklogs_t,
rollback_segments rollback_segments_t,
flashback_info    flashback_info_t,
archivedest_info  archiveDest_info_t);

-- @@Datatype

TYPE parameter_xml_record_t IS RECORD (
name            V$PARAMETER.NAME%TYPE,
value           V$PARAMETER.VALUE%TYPE, -- used only when a parameter is being renamed.
type            V$PARAMETER.TYPE%TYPE,
isdefault       V$PARAMETER.ISDEFAULT%TYPE,
is_obsoleted    BOOLEAN,
is_deprecated   BOOLEAN,
renamed_to_name VARCHAR2(80),
new_value       VARCHAR2(80),
min_value       INTEGER,
min_char_value  VARCHAR2(20)); -- used for COMPATIBLE whose value is a pseudo number

TYPE parameters_t IS TABLE OF parameter_xml_record_t INDEX BY BINARY_INTEGER;

TYPE initparams_t IS RECORD (
update_params     parameters_t,
nonhandled_params parameters_t, -- at 12.2, will have no params. maintained for XML compatibility only.
rename_params     parameters_t,
remove_params     parameters_t);

TYPE preupgradecheck_t IS RECORD (
id          VARCHAR2(30), -- the CHECK name
severity    NUMBER, -- "status" attribute in xml
-- message message_t,
rule        message_t,
broken_rule message_t,
action      message_t,
detail      detail_t,
fixup       fixup_t);

TYPE preupgradechecks_t IS TABLE OF preupgradecheck_t
INDEX BY BINARY_INTEGER;

TYPE rdbmsup_t IS RECORD (
xmlns               VARCHAR2(1000),
version             VARCHAR2(30),
upgradable_versions VARCHAR2(1000));

TYPE database_t IS RECORD (
name          VARCHAR2(256),
containerName VARCHAR2(256),
containerId   NUMBER,
version       VARCHAR2(30),
compatibility VARCHAR2(30),
blocksize     INTEGER,
platform      VARCHAR2(100),
timezoneVer   NUMBER,
log_mode      VARCHAR2(30),
readonly      BOOLEAN,
edition_val   VARCHAR2(30)); -- SYS.REGISTRY$.EDITION%TYPE - except it is not avail on 10.2

-- this table holds computation info for the memory pools we are
-- making sizing recommendations for
TYPE memparameter_record_t IS RECORD (
name V$PARAMETER.NAME%TYPE,
old_value NUMBER, -- current value
min_value NUMBER, -- minimum value for upgrade
new_value NUMBER, -- new/recommended value for upgrade
dif_value NUMBER,                     -- diff of old_value - min_value
isdefault V$PARAMETER.ISDEFAULT%TYPE, -- is the value defaulted?
display   BOOLEAN);                   -- display recommended value? T/F

TYPE memparameter_table_t IS TABLE of memparameter_record_t
INDEX BY V$PARAMETER.NAME%TYPE;

mem_parameters memparameter_table_t; -- MEMory PARAMETERS table

db_is_cdb           BOOLEAN; -- is db a cdb? T/F
db_is_root          BOOLEAN; -- is db a ROOT container database? T/F
db_n_pdbs           NUMBER; -- total Number of PDBs as queried from v$pdbs
is_show_mem_sizes   BOOLEAN := FALSE; -- SHOW/display minimum MEMory SIZES?
-- init to FALSE
is_archivelog_in_fra BOOLEAN := FALSE; -- are archive logs in FRA? T/F

-- Provide mappings from a c_check_level_* constant to its string evuivalent used
-- for XML output only. These strings have specific meanings to DBUA. Do not
-- change without corresponding change in the upgrade.xsd and DBUA buy-in.
-- IMPORTANT: The order of these strings must correspond to the constants C_CHECK_LEVEL_*

check_level_strings string_array_t := new string_array_t('SUCCESS','WARNING','INFO','ERROR','RECOMMEND');
TYPE check_level_ints_t IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
check_level_ints check_level_ints_t;
Dependencies
DBA_TABLESPACES V$PARAMETER V$RECOVERY_FILE_DEST
V$ARCHIVE_DEST    
Documented No
Exceptions
Error Code Reason
ORA-904 e_noColumnFound
ORA-955 nameAlreadyExists
ORA-1013 e_userCancel
ORA-2003 e_noParamFound
ORA-3113 e_noOraConnect1
ORA-3114 e_noOraConnect2
ORA-6550 e_undefinedFunction
ORA-29283 invalidFileOperation
ORA-29292 invalidFileRename
ORA-29553 classInUse
ORA-44003 stringNotSimpleSQLName
ORA-20000 Pre-Upgrade Package <resource> does not exist
ORA-20000 Functions must be run AS SYSDBA
First Available 12.1.0
Security Model Owned by SYS with no privileges granted
Source {$ORACLE_HOME}/rdbms/admin/preupgrade_package.sql
Subprograms
AMD_EXISTS_CHECK INIT_MEM_SIZES PENDING_DST_SESSION_CHECK
APEX_MANUAL_UPGRADE_CHECK INVALID_LAF_CHECK PGA_AGGREGATE_LIMIT_CHECK
AUDIT_RECORDS_CHECK INVALID_OBJECTS POST_DICTIONARY_CHECK
AUDTAB_ENC_TS_CHECK INVALID_OBJECTS_EXIST_CHECK POST_DICTIONARY_FIXUP
AWR_DBIDS_PRESENT_CHECK INVALID_SYS_TABLEDATA_CHECK POST_FIXED_OBJECTS_CHECK
CASE_INSENSITIVE_AUTH_CHECK INVALID_SYS_TABLEDATA_FIXUP POST_JVM_MITIGAT_PATCH_CHECK
COMPATIBLE_NOT_SET_CHECK INVALID_USR_TABLEDATA_CHECK PRE_FIXED_OBJECTS_CHECK
COMPATIBLE_PARAMETER_CHECK INVALID_USR_TABLEDATA_FIXUP PRE_FIXED_OBJECTS_FIXUP
CONC_RES_MGR_CHECK IS_CON_ROOT PURGE_RECYCLEBIN_CHECK
CONC_RES_MGR_FIXUP IS_SIZE_THIS_MEMPARAM PURGE_RECYCLEBIN_FIXUP
CYCLE_NUMBER_CHECK JAVAVM_STATUS_CHECK PVALUE_TO_NUMBER
DATA_MINING_OBJECT_CHECK JOB_QUEUE_PROCESS_0_CHECK REMOTE_REDO_CHECK
DBMS_STATS_METHOD_OPT_CHECK JVM_MITIGATION_PATCH_CHECK REPCAT_SETUP_CHECK
DEFAULT_RESOURCE_LIMIT_CHECK JVM_MITIGATION_PATCH_FIXUP RLP_PARAM_CHECK
DEPEND_USR_TABLES_CHECK MIN_ARCHIVE_DEST_SIZE_CHECK ROLLBACK_SEGMENTS_CHECK
DEPEND_USR_TABLES_FIXUP MIN_RECOVERY_AREA_SIZE_CHECK RUN_ALL_CHECKS
DICTIONARY_STATS_CHECK MV_REFRESH_CHECK RUN_CHECK
DICTIONARY_STATS_FIXUP NETWORK_ACL_PRIV_CHECK RUN_FIXUP (2)
DV_SIMULATION_CHECK NEW_TIME_ZONES_EXIST_CHECK RUN_FIXUP_AND_REPORT
EM_PRESENT_CHECK NUM_PDB_BATCHES_UPG RUN_FIXUP_ONLY
ENABLED_INDEXES_TBL_CHECK NUM_PDBS_UPG_IN_PARALLEL RUN_INT_PROC
ENABLED_INDEXES_TBL_FIXUP OLAP_PAGE_POOL_SIZE_CHECK RUN_PREUPGRADE
EXCLUSIVE_MODE_AUTH_CHECK OLD_TIME_ZONES_EXIST_CHECK SYNC_STANDBY_DB_CHECK
EXF_RUL_EXISTS_CHECK OLS_SYS_MOVE_CHECK SYS_DEFAULT_TABLESPACE_CHECK
FILES_BACKUP_MODE_CHECK OLS_VERSION_CHECK SYS_DEFAULT_TABLESPACE_FIXUP
FILES_NEED_RECOVERY_CHECK ORACLE_RESERVED_USERS_CHECK TABLESPACES_CHECK
FIND_ALL_PDB_ARCHIVE_SIZE ORDIMAGEINDEX_CHECK TABLESPACES_INFO_CHECK
FIND_ARCHIVE_DEST_INFO OVERLAP_NETWORK_ACL_CHECK TEMPTS_NOTEMPFILE_CHECK
FIND_MEM_SIZES PARAMETER_DEPRECATED_CHECK TRGOWNER_NO_ADMNDBTRG_CHECK
FIND_PROCESSES_VALUE PARAMETER_MIN_VAL_CHECK TWO_PC_TXN_EXIST_CHECK
FIND_RECOVERY_AREA_INFO PARAMETER_MIN_VAL_FIXUP ULTRASEARCH_DATA_CHECK
FIND_SGA_MEM_VALUES PARAMETER_NEW_NAME_VAL_CHECK UNDERSCORE_EVENTS_CHECK
GET_CON_ID PARAMETER_NEW_NAME_VAL_FIXUP UNIAUD_TAB_CHECK
GET_CON_NAME PARAMETER_OBSOLETE_CHECK UPG_BY_STD_UPGRD_CHECK
GET_FAILED_CHECK_XML PARAMETER_OBSOLETE_FIXUP XBRL_VERSION_CHECK
GET_INVALID_OBJECTS PARAMETER_RENAME_CHECK XDB_RESOURCE_TYPE_CHECK
GET_NPDBS PARAMETER_RENAME_FIXUP XML_TO_TEXT
HIDDEN_PARAMS_CHECK PA_PROFILE_CHECK  
 
AMD_EXISTS_CHECK
Undocumented dbms_preup.amd_exists_check(result_txt OUT CLOB)
RETURN NUMBER;
SQL> DECLARE
  2   outVal CLOB;
  3   retVal NUMBER;
  4  BEGIN
  5    retVal := amd_exists_check(outVal);
  6    dbms_output.put_line('Return value: ' || TO_CHAR(retVal));
  7    dbms_output.put_line(outVal);
  8* END;
  9  /
Return value: 1

PL/SQL procedure successfully completed.
 
APEX_MANUAL_UPGRADE_CHECK
APEX check dbms_preup.apex_manual_upgrade_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.apex_manual_upgrade_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
AUDIT_RECORDS_CHECK
Audit Records check dbms_preup.audit_records_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB(1024);
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.audit_records_check(OutVal);
  dbms_output.put_line(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal));
END;
/
 
AWR_DBIDS_PRESENT_CHECK
Checks to determine if inactive DBIDs are present in the AWR dbms_preup.awr_dbids_present_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.awr_dbids_present_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
AUDTAB_ENC_TS_CHECK
Audit Table check dbms_preup.audtab_enc_ts_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OUTVal  CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.audtab_enc_ts_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
CASE_INSENSITIVE_AUTH_CHECK
Case insensitive check dbms_preup.case_insensitive_auth_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.case_insensitive_auth_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
COMPATIBLE_NOT_SET_CHECK
Compatible parameter check dbms_preup.compatible_not_set_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.compatible_not_set_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
COMPATIBLE_PARAMETER_CHECK
Compatible Parameter check dbms_preup.compatible_parameter_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.compatible_parameter_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
0: ERROR: --> Compatible set too low "compatible" currently set at and must be set to at least 11.0.0 prior to upgrading the database. Update your init.ora or spfile to make this change.
 
CONC_RES_MGR_CHECK
Resource Manager check dbms_preup.conc_res_mgr_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.conc_res_mgr_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
CONC_RES_MGR_FIXUP
If version 11.2 sets dbms_stats.global_prefs CONCURRENT to FALSE, otherwise to OFF dbms_preup.conc_res_mgr_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.conc_res_mgr_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
CYCLE_NUMBER_CHECK
Undocumented dbms_preup.cycle_number_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.cycle_number_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
DATA_MINING_OBJECT_CHECK
Data Mining check dbms_preup.data_mining_object_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.data_mining_object_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
DBMS_STATS_METHOD_OPT_CHECK
dbms_stats METHOD_OPT check dbms_preup.dbms_stats_method_opt_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.dbms_stats_mehtod_opt_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
DEFAULT_RESOURCE_LIMIT_CHECK
Default Resource Limit check dbms_preup.default_resource_limit_check(result_txt OUT CLOB2)
RETURN NUMBER;
DECLARE
 OutVal CLOB(1024);
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.default_resource_limit_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
DEPEND_USR_TABLES_CHECK
Undocumented depend_usr_tables_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB(1024);
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.depend_usr_tables_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
DEPEND_USR_TABLES_FIXUP
Performs the following EXECUTE IMMEDIATE 'ALTER TABLE ' || t_full_name || ' UPGRADE INCLUDING DATA'; in a cursor loop based on criteria in the cursor's WHERE clause dbms_preup.depend_usr_tables_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.depend_usr_tables_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
DICTIONARY_STATS_CHECK
Data Dictionary Stats check dbms_preup.dictionary_stats_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.dictionary_stats_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
DICTIONARY_STATS_FIXUP
Runs dbms_stats gather_dicitonary_stats dbms_preup.dictionary_stats_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.dictionary_stats_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
DV_SIMULATION_CHECK
Database Vault Check dbms_preup.dv_simulation_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.dv_simulation_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
EM_PRESENT_CHECK
Enterprise Manager check dbms_preup.em_present_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.em_present_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
ENABLED_INDEXES_TBL_CHECK
Checks to see if the table sys.enabled$indexes exists dbms_preup.enabled_indexes_tbl_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.enabled_indexes_tbl_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
ENABLED_INDEXES_TBL_FIXUP
Drops the table sys.enabled$indexes if it exists dbms_preup.enabled_indexes_tbl_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.enabled_indexes_tbl_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
EXCLUSIVE_MODE_AUTH_CHECK
Exclusive Mode check dbms_preup.exclusive_mode_auth_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.exclusive_mode_auth_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
EXF_RUL_EXISTS_CHECK
Expression Filter Rules check dbms_preup.exf_rul_exists_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.exf_rul_exists_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
FILES_BACKUP_MODE_CHECK
Backup check dbms_preup.files_backup_mode_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.files_backup_mode_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
FILES_NEED_RECOVERY_CHECK
Files needing recovery check dbms_preup.files_need_recovery_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 OutVal CLOB;
 RetVal NUMBER;
BEGIN
  RetVal := dbms_preup.files_need_recovery_check(OutVal);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || OutVal);
END;
/
 
FIND_ALL_PDB_ARCHIVE_SIZE
Returns the estimated minimum amount of archivelog size to be generated by all PDBs dbms_preup.find_all_pdb_archive_size RETURN NUMBER;
SELECT dbms_preup.find_all_pdb_archive_size
FROM dual;
 
FIND_ARCHIVE_DEST_INFO
Outputs archive redo log destination info using DBMS_OUTPUT dbms_preup.find_archive_dest_info;
SQL> set serveroutput on
SQL> dbms_preup.find_archive_dest_info;
 
FIND_MEM_SIZES
Finds the minimum sizes for memory parameters needed for upgrades dbms_preup.find_mem_sizes(
memvp                 IN OUT MEMPARAMETER_TABLE_T,
display_min_mem_sizes IN OUT BOOLEAN);
TBD
 
FIND_PROCESSES_VALUE
Returns the minimum process value using DBMS_OUTPUT dbms_preup.find_processes_value;
SQL> set serveroutput on
SQL> dbms_preup.find_processes_value;
 
FIND_RECOVERY_AREA_INFO
Returns recovery area info using DBMS_OUTPUT dbms_preup.find_recovery_area_info;
SQL> set serveroutput on
SQL> dbms_preup.find_recovery_area_info;
 
FIND_SGA_MEM_VALUES
Outputs information about sga memory affecting parameters dbms_preup.find_sga_mem_values(memvp IN OUT MEMPARAMETER_TABLE_T);
TBD
 
GET_CON_ID
Returns the container identifier dbms_preup.get_con_id RETURN NUMBER;
SELECT dbms_preup.get_con_id
FROM dual;
 
GET_CON_NAME
Returns the container name dbms_preup.get_con_name RETURN VARCHAR2;
SELECT dbms_preup.get_con_name
FROM dual;
 
GET_FAILED_CHECK_XML
Returns failed check information as XMLin a CLOB dbms_preup.get_failed_check_xml(
check_name                    IN VARCHAR2,
substitution_parameter_values IN string_array_t,
detail_type                   IN VARCHAR2,
detail_info                   IN VARCHAR2)
RETURN CLOB;
TDB
 
GET_INVALID_OBJECTS
Returns a list of invalid objects as a weakly typed ref cursor dbms_preup.get_invalid_objects(fromsys IN VARCHAR2)
RETURN SYS_REFCURSOR;
TBD
 
GET_NPDBS
Returns the count from V$PDBS dbms_preup.get_npdbs RETURN NUMBER;
SELECT dbms_preup.get_npdbs
FROM dual;
 
HIDDEN_PARAMS_CHECK
Returns from.v$parameter underscore where ismodified != 'MODIFIED dbms_preup.hidden_params_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.hidden_params_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
INIT_MEM_SIZES
Initializes minimum memory sizes for upgrade dbms_preup.init_mem_sizes(memvp IN OUT MEMPARAMETER_TABLE_T);
TBD
 
INVALID_LAF_CHECK
Invalid Log Archive Format check dbms_preup.invalid_laf_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.invalid_laf_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
INVALID_OBJECTS
Shows the SYS/SYSTEM and Non-SYS/SYSTEM invalid objects using DBMS_OUTPUT dbms_preup.invalid_objects;
exec dbms_preup.invalid_objects;
 
INVALID_OBJECTS_EXIST_CHECK
Returns information about invalid objects and the count dbms_preup.invalid_objects_exist_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.invalid_objects_exist_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
INVALID_SYS_TABLEDATA_CHECK
Checks for invalid schemas from registry$ and user$ tables dbms_preup.invalid_sys_tabledata_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.invalid_sys_tabledata_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
INVALID_SYS_TABLEDATA_FIXUP
Fixes issues with invalid schemas from registry$ and user$ tables dbms_preup.invalid_sys_tabledata_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.invalid_sys_tabledata_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
INVALID_USR_TABLEDATA_CHECK
Check for invalid data in tables dependent on Oracle-Maintained types by looking through dependency$ for types owned by schemas in registry$ or user$ dbms_preup.invalid_usr_tabledata_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.invalid_usr_tabledata_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
INVALID_USR_TABLEDATA_FIXUP
Fixes invalid data in tables dependent on Oracle-Maintained types by looking through dependency$ for types owned by schemas in registry$ or user$ dbms_preup.invalid_usr_tabledata_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.invalid_usr_tabledata_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
IS_CON_ROOT
Returns TRUE if the current container is CDB$ROOT is_con_root RETURN BOOLEAN;
BEGIN
  IF dbms_preup.is_con_root THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
IS_SIZE_THIS_MEMPARAM
Returns TRUE if memory sizing is specified for the named parameter in the upgrade. Tests sizing for cs_idx, jv_idx, sp_idx, lp_idx, sr_idx, pt_idx, st_idx, and mt_idx is_size_this_memparam(name IN v$parameter.name%TYPE)
RETURN BOOLEAN;
BEGIN
  IF dbms_preup.is_size_this_memparam('jv_idx') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
JAVAVM_STATUS_CHECK
Java Virtual Machine Status check dbms_preup.javavm_status_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.javavm_status_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
JOB_QUEUE_PROCESS_0_CHECK
Returns the value of the parameter job_queue_processes. The CLOB is used to return an error message if the check raises and exception dbms_preup.job_queue_process_0_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.job_queue_process_0_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
JVM_MITIGATION_PATCH_CHECK
Verifies the javavm mitigation patch is installed and active dbms_preup.jvm_mitigation_patch_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.jvm_mitigation_patch_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
JVM_MITIGATION_PATCH_FIXUP
Runs the internal procedure SYS.DBMS_JAVA_DEV.ENABLE dbms_preup.jvm_mitigation_patch_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.jvm_mitigation_patch_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
MIN_ARCHIVE_DEST_SIZE_CHECK
Determine free space needed for when Archivelog mode is enabled and destination is NOT in the FRA dbms_preup.min_archive_dest_size_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.min_archive_dest_size_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
MIN_RECOVERY_AREA_SIZE_CHECK
Determine minimum free space needed when both Archivelog and Flashback Database are enabled dbms_preup.min_recovery_area_size_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.min_recovery_area_size_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
MV_REFRESH_CHECK
Verifies all MVs are fresh and sumdelta$ is empty dbms_preup.mv_refresh_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.mv_refresh_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
NETWORK_ACL_PRIV_CHECK
looks for existing network ACLs in 11g to issue warnings about a format change in 12c dbms_preup.network_acl_priv_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.network_acl_priv_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
NEW_TIME_ZONES_EXIST_CHECK
New Time Zones check dbms_preup.new_time_zones_exist_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.new_time_zones_exist_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
NUM_PDB_BATCHES_UPG
An estimate of the number of upgrade batches dbms_preup.num_pdb_batches_upg(pdbs_in_parallel IN NUMBER)
RETURN NUMBER;
SELECT dbms_preup.num_pdb_batches_upg(
FROM dual;
 
NUM_PDBS_UPG_IN_PARALLEL
Returns the number of pdbs upgrading in parallel at a time dbms_preup.num_pdbs_upg_in_parallel RETURN NUMBER;
SELECT dbms_preup.num_pdb_upg_in_parallel(
FROM dual;
 
OLAP_PAGE_POOL_SIZE_CHECK
Returns 1 if the OLAP page pool size has not been set dbms_preup.olap_page_pool_size_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.olap_page_pool_size_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
OLD_TIME_ZONES_EXIST_CHECK
Returns 1 if a valid timezone for upgrade exists dbms_preup.old_time_zones_exist_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.old_time_zones_exist_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
OLS_SYS_MOVE_CHECK
Returns 1 if there the tables SYS.PREUPG_AUD$ does not exist dbms_preup.ols_sys_move_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.ols_sys_move_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
OLS_VERSION_CHECK
Verifies that the OLS version is the same as the CATPROC version or is supported for direct upgrade dbms_preup.ols_version_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.ols_version_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
ORACLE_RESERVED_USERS_CHECK
Checks for reserved users by name and version number dbms_preup.oracle_reserved_users_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.oracle_reserved_users_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
ORDIMAGEINDEX_CHECK
The upgrade will remove any ORDIMAGE indexes dbms_preup.ordimageindex_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.ordimageindex_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
OVERLAP_NETWORK_ACL_CHECK
Returns 1 if no NACL overlaps exist dbms_preup.overlap_network_acl_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.overlap_network_acl_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PARAMETER_DEPRECATED_CHECK
Returns a list of deprecated parameters if found dbms_preup.parameter_deprecated_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.parameter_deprecated_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PARAMETER_MIN_VAL_CHECK
Checks that minimum values for a numeric parameter are acceptable dbms_preup.parameter_min_val_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.parameter_min_val_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PARAMETER_MIN_VAL_FIXUP
Fixes any minimum numeric parameter values that are not within the acceptable range dbms_preup.parameter_min_val_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.parameter_min_val_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
PARAMETER_NEW_NAME_VAL_CHECK
Renamed parameters that may or may not requre a new value dbms_preup.parameter_new_name_val_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.parameter_new_name_val_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PARAMETER_NEW_NAME_VAL_FIXUP
At this time Oracle is relying on DBUA to perform the fixup since the <InitParams> are still present dbms_preup.parameter_new_name_val_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.parameter_new_name_val_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
PARAMETER_OBSOLETE_CHECK
Renamed parameters has been that may or may not requre a new value dbms_preup.parameter_obsolete_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.parameter_obsolete_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PARAMETER_OBSOLETE_FIXUP
At this time Oracle is relying on DBUA to perform the fixup since the <InitParams> are still present dbms_preup.parameter_obsolete_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.parameter_obsolete_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
PARAMETER_RENAME_CHECK
Checks for renamed parameters dbms_preup.parameter_rename_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.parameter_rename_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PARAMETER_RENAME_FIXUP
Does nothing: Oracle still relies on DBUA to perform the fixup dbms_preup.parameter_rename_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.parameter_rename_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
PA_PROFILE_CHECK
Returns 1 if view DBA_PRIV_CAPTURES does not exist dbms_preup.pa_profile_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.pa_profile_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PENDING_DST_SESSION_CHECK
Checks for pending distributed transactions before the upgrade dbms_preup.pending_dst_session_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.pending_dst_session_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PGA_AGGREGATE_LIMIT_CHECK
Detects where the database has an pga_aggregate_limit in the spfile that is too small ... it must be >2GB dbms_preup.pga_aggregate_limit_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.pga_aggregate_limit_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
POST_DICTIONARY_CHECK
Rrecommends re-gathering dictionary stats post upgrade. Checks if statistics has been taken after upgrade, if not report it and generate the fixup in the  postupgrade fixup script, after the fixup run, it will not fail and therefore it will report this check as successfull. dbms_preup.post_dictionary_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.post_dictionary_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
POST_DICTIONARY_FIXUP
Executes dictionary stats post upgrade dbms_preup.post_dictionary_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.post_dictionary_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
POST_FIXED_OBJECTS_CHECK
Recommends gathering fixed objetcs stats post upgrade dbms_preup.post_fixed_objects_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.post_fixed_objects_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
POST_JVM_MITIGAT_PATCH_CHECK
Looks for an enabled trigger owned by SYS named 'DBMS_JAVA_DEV_TRG' dbms_preup.post_jvm_mitigat_patch_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.ppost_jvm_mitigat_patch_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PRE_FIXED_OBJECTS_CHECK
Checks for stats collection on fixed object tables. If none of the fixed object tables have had stats collected then gather fixed objects stats else do nothing dbms_preup.pre_fixed_objects_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.pre_fixed_objects_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PRE_FIXED_OBJECTS_FIXUP
Executes sys.dbms_stats.gather_fixed_objects_stats dbms_preup.pre_fixed_objects_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.pre_fixed_objects_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
PURGE_RECYCLEBIN_CHECK
Selects the count of objects in sys.recyclebin$ dbms_preup.purge_recyclebin_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.purge_recyclebin_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
PURGE_RECYCLEBIN_FIXUP
Executes 'PURGE DBA_RECYCLEBIN' dbms_preup.purge_recyclebin_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN number;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.purge_recyclebin_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
PVALUE_TO_NUMBER
Converts a parameter string to a number. The function takes into account that the parameter string may have a 'K' or 'M' multiplier character. dbms_preup.pvalue_to_number(value_string VARCHAR2)
RETURN NUMBER;
SELECT dbms_preup.pvalue_to_number('10M')
FROM dual;

SELECT dbms_preup.pvalue_to_number('10 M')
FROM dual;

SELECT dbms_preup.pvalue_to_number('10G')
FROM dual;
 
REMOTE_REDO_CHECK
Checks to detect if REDO configuration is supported for above version 11gR2 dbms_preup.remote_redo_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.remote_redo_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
REPCAT_SETUP_CHECK
Checks for Materialized View replication likely as a result of Oracle dropping Advanced Replication dbms_preup.repcat_setup_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.repcat_setup_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
RLP_PARAM_CHECK
Verifies that the REMOTE_LOGIN_PASSWORDFILE is not SHARED dbms_preup.rlp_param_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.rlp_param_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
ROLLBACK_SEGMENTS_CHECK
Checks for the use of manual undo with rollback segments dbms_preup.rollback_segments_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.rollback_segments_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
RUN_ALL_CHECKS
Runs all preupgrade checks dbms_preup.run_all_checks(result_xml OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.run_all_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
RUN_CHECK
Runs a named check dbms_preup.run_check(
check_name IN  VARCHAR2,
result_xml OUT CLOB)
RETURN BOOLEAN;
TBD
 
RUN_FIXUP
Calls a named FIXUP

Overload 1		 dbms_preup.run_fixup(check_name IN VARCHAR2)
RETURN BOOLEAN;
TBD
Overload 2 dbms_preup.run_fixup(
check_name IN VARCHAR2,
fixup_id   IN NUMBER)
RETURN BOOLEAN;
TBD
 
RUN_FIXUP_AND_REPORT
Present for backward compatibility and has no way to return a status so it is essentially useless dbms_preup.run_fixup_and_report(check_name IN VARCHAR2);
TBD
 
RUN_FIXUP_ONLY
Calls a named FIXUP with results returned as XML dbms_preup.run_fixup_only(
check_name       IN     VARCHAR2,
check_result_xml IN OUT VARCHAR2)
RETURN BOOLEAN;
TBD
 
RUN_INT_PROC
Intended to run any internal procedure such as dbms_stats and others, calls execute_sql_statement and returns a boolean to report if the procedure could run dbms_preup.run_int_proc(
statement  IN     VARCHAR2,
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN BOOLEAN;
TBD
 
RUN_PREUPGRADE
Runs the preupgrade and contains some of the most humorous text to be found in an Oracle script dbms_preup.run_preupgrade(
output_filename IN VARCHAR2 DEFAULT NULL,
xml             IN BOOLEAN  DEFAULT FALSE)
RETURN BOOLEAN;
TBD
 
SYNC_STANDBY_DB_CHECK
Checks V$ARCHIVE_DEST_STATUS and V$ARCHIVE_DEST for STATUS <> 'VALID' with  GAP_STATUS indicating a gap dbms_preup.sync_standby_db_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.sync_standby_db_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
SYS_DEFAULT_TABLESPACE_CHECK
Verifies schemas SYS and SYSTEM both in SYSTEM tablespace dbms_preup.sys_default_tablespace_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.sys_default_tablespace_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
SYS_DEFAULT_TABLESPACE_FIXUP
Resets schemas SYS and SYSTEM to SYSTEM tablespace if they are not set properly dbms_preup.sys_default_tablespace_fixup(
result_txt IN OUT VARCHAR2,
pSqlcode   IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 RsltVal VARCHAR2(1024);
 RetVal  NUMBER;
 SQLCode NUMBER;
BEGIN
  RetVal := dbms_preup.sys_default_tablespace_fixup(RsltVal, SQLCode);
  dbms_output.put_line(TO_CHAR(RetVal) || ': ' || RsltVal);
  dbms_output.put_line(TO_CHAR(SQLCode);
END;
/
 
TABLESPACES_CHECK
If local undo is enabled, each PDB will have its own UNDO tablespace and reported on its respective log, if disabled all PDB's are sharing same UNDO tablespace which is created in cdb$root. Upgrade sizes UNDO tablespace based on the default number of PDB's being upgraded in parallel, with undo space size = (total_space x num_pdbs) upgraded in parallel. dbms_preup.tablespaces_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.tablespaces_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
TABLESPACES_INFO_CHECK
Checks for local undo and autoextend enabled tablespaces dbms_preup.tablespaces_info_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.tablespace_info_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
TEMPTS_NOTEMPFILE_CHECK
Verifies the temp tablespace is uses a tempfile dbms_preup.tempts_notempfile_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.tempts_notempfile_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
TRGOWNER_NO_ADMNDBTRG_CHECK
Checks for database triggers created by users that didn't receive the privilege directly dbms_preup.trgowner_no_admndbtrg_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.trgowner_no_admndbtrg_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
TWO_PC_TXN_EXIST_CHECK
Looks for entries in sys.dba_2pc_pending dbms_preup.two_pc_txn_exist_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.two_pc_exist_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
ULTRASEARCH_DATA_CHECK
Once Ultra Search instance is created the wk$instance table is populated. The logic determines if Ultra Search has data or not by looking up wk$instance table. WKSYS.WK$INSTANCE table exists when Ultra Search is installed. If it's not installed, WKSYS.WK$INSTANCE doesn't exist and the pl/sql block raises exception. dbms_preup.ultrasearch_data_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.ultrasearch_data_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
UNDERSCORE_EVENTS_CHECK
Checks v$parameter2 for names are 'EVENT' and  '_TRACE_EVENTS' and 'ISDEFAULT' is FALSE' dbms_preup.underscore_events_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.underscore_events_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
UNIAUD_TAB_CHECK
Unified Audit in use check dbms_preup.uniaud_tab_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.uniaud_tab_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
UPG_BY_STD_UPGRD_CHECK
Construct a quoted and comma separated list of components that will be upgraded by the upgrade script and verifies them dbms_preup.upg_by_std_upgrd_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.upg_by_std_upgrd_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
XBRL_VERSION_CHECK
Checks sys.user$ WHERE name=''XBRLSYS' dbms_preup.xbrl_version_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.xbrl_version_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
XDB_RESOURCE_TYPE_CHECK
Verifies the attribute order of XDB.XDB$RESOURCE_T is either correct or feasible for patching dbms_preup.xdb_resource_type_check(result_txt OUT CLOB)
RETURN NUMBER;
DECLARE
 outVal CLOB;
 retVal NUMBER;
BEGIN
  retVal := dbms_preup.xdb_resource_check(outVal);
  dbms_output.put_line(retVal);
  dbms_output.put_line(outVal);
END;
/
 
XML_TO_TEXT
Undocumented dbms_preup.xml_to_text(xml CLOB) RETURN CLOB;
TBD
Related Topics
Built-in Functions
Built-in Packages
DBMS_OUTPUT
DBMS_REGISTRY
DBMS_SYSTEM
UTL_FILE
What's New In 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved