[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
dbua_OutInUpdate BOOLEAN,
db_match BOOLEAN
);
SELECT USER INTO tmp_varchar1 FROM SYS.DUAL;
EXECUTE IMMEDIATE 'SELECT name FROM v$database' INTO db_name;
EXECUTE IMMEDIATE 'SELECT dbms_preup.get_con_name FROM sys.dual' INTO con_name;
EXECUTE IMMEDIATE 'SELECT dbms_preup.get_con_id FROM sys.dual' INTO con_id;
EXECUTE IMMEDIATE 'SELECT version FROM v$instance' INTO db_version;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name = ''compatible'''
INTO db_compat;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name = ''db_block_size'''
INTO db_block_size;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name = ''undo_management'''
INTO db_undo;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name = ''undo_tablespace'''
INTO db_undo_tbs;
EXECUTE IMMEDIATE 'SELECT count(*) FROM v$database WHERE flashback_on = ''NO'''
INTO p_count;
EXECUTE IMMEDIATE 'SELECT LOG_MODE from v$database'
INTO db_log_mode;
EXECUTE IMMEDIATE 'SELECT platform_id, platform_name
FROM v$database'
INTO db_platform_id, db_platform;
EXECUTE IMMEDIATE 'SELECT value FROM sys.v$parameter WHERE name = ''cpu_count'''
INTO tmp_varchar1;
'SELECT value FROM v$parameter WHERE name = ''parallel_threads_per_cpu'''
INTO tmp_varchar1;
'SELECT edition FROM sys.registry$ WHERE cid=''CATPROC'''
INTO tmp_varchar1;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name = ''use_indirect_data_buffers'''
INTO tmp_varchar1;
EXECUTE IMMEDIATE 'SELECT status FROM V$INSTANCE'
INTO tmp_varchar1;
EXECUTE IMMEDIATE 'SELECT version from v$timezone_file'
INTO db_tz_version;
EXECUTE IMMEDIATE 'SELECT version, prv_version FROM sys.registry$
WHERE cid = ''CATPROC'''
INTO t_db_dict_vers, t_db_prev_vers;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name =
:1 AND isdefault = ''TRUE'''
INTO c_value
USING reqp(i).name;
'Verify if a user or role with the name GDS_CATALOG_SELECT exists');
max_minvp := idx; -- update last index used in minvp
select name from v$obsolete_parameter order by name;
select name from v$parameter
where isdeprecated = 'TRUE' order by name;
sp(idx).dbua_OutInUpdate := TRUE;
sp(idx).dbua_OutInUpdate := TRUE;
sp(idx).dbua_OutInUpdate := TRUE;
EXECUTE IMMEDIATE 'SELECT NULL FROM v$parameter WHERE name =
LOWER(:1) AND isdefault = ''FALSE'''
INTO t_null
USING rp(i).oldname;
EXECUTE IMMEDIATE 'SELECT NULL FROM v$parameter WHERE name =
LOWER(:1) AND isdefault = ''FALSE'''
INTO t_null
USING op(i).name;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name =
LOWER(:1) AND isdefault = ''FALSE'''
INTO c_value
USING sp(i).oldname;
'SELECT cid, status, version, schema#
FROM sys.registry$ WHERE namespace =''SERVER''';
EXECUTE IMMEDIATE 'SELECT name FROM sys.user$ WHERE user#=:1'
INTO c_schema
USING n_schema;
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.user$ WHERE name = ''WKSYS'''
INTO c_null;
EXECUTE IMMEDIATE 'SELECT FLOWS_010500.wwv_flows_release from sys.dual'
INTO c_version;
EXECUTE IMMEDIATE 'SELECT FLOWS_010600.wwv_flows_release from sys.dual'
INTO c_version;
EXECUTE IMMEDIATE 'SELECT FLOWS_020000.wwv_flows_release from sys.dual'
INTO c_version;
EXECUTE IMMEDIATE 'SELECT FLOWS_020100.wwv_flows_release from sys.dual'
INTO c_version;
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.user$ WHERE name = ''DVSYS'''
INTO c_null;
'SELECT tablespace_name, contents, extent_management FROM SYS.dba_tablespaces ' ||
'WHERE tablespace_name in (:1,' || default_tablespaces || ') or ' ||
'tablespace_name in (SELECT distinct T.tablespace_name ' ||
'FROM sys.dba_queues Q, ' ||
'sys.dba_tables T ' ||
'WHERE Q.queue_table=T.table_name AND Q.owner = T.owner) or ' ||
'tablespace_name in (SELECT temporary_tablespace ' ||
'FROM sys.dba_users ' ||
'WHERE username = ''SYS'') '
USING db_undo_tbs;
'SELECT SUM(bytes) FROM sys.dba_segments seg WHERE seg.tablespace_name = :1'
INTO sum_bytes
USING p_tsname;
'SELECT SUM(bytes) FROM sys.dba_temp_files files WHERE ' ||
'files.tablespace_name = :1'
INTO sum_bytes
USING p_tsname;
'SELECT SUM(bytes) FROM sys.dba_data_files files WHERE ' ||
'files.tablespace_name = :1'
INTO sum_bytes
USING p_tsname;
'SELECT SUM(decode(maxbytes, 0, 0, maxbytes-bytes)) ' ||
'FROM sys.dba_temp_files WHERE tablespace_name=:1'
INTO sum_bytes
USING p_tsname;
'SELECT SUM(decode(maxbytes, 0, 0, maxbytes-bytes)) ' ||
'FROM sys.dba_data_files WHERE tablespace_name=:1'
INTO sum_bytes
USING p_tsname;
'SELECT segment_name, next_extent, max_extents, status FROM SYS.dba_rollback_segs
WHERE owner=''PUBLIC'' OR (owner=''SYS'' AND segment_name != ''SYSTEM'')';
'SELECT tablespace_name, sum(bytes) FROM sys.dba_segments
WHERE segment_name = :1 AND ROWNUM = 1 GROUP BY tablespace_name'
INTO p_tsname, sum_bytes
USING tmp_varchar1;
'SELECT ROUND(SUM(DECODE(maxbytes, 0, 0,maxbytes-bytes)/:1))
FROM sys.dba_data_files WHERE tablespace_name=:2'
INTO rs_info(idx).auto
USING c_kb, p_tsname;
'SELECT ROUND(SUM(DECODE(maxbytes, 0, 0,maxbytes-bytes)/:1))
FROM sys.dba_data_files WHERE tablespace_name=:2'
INTO tmp_num1
USING c_kb, p_tsname;
EXECUTE IMMEDIATE 'SELECT rfd.name, rfd.space_limit, rfd.space_used,
rfd.space_reclaimable, rfd.number_of_files,
vp1.value, vp2.value
FROM v$recovery_file_dest rfd, v$parameter vp1, v$parameter vp2
WHERE UPPER(vp1.name) = ''DB_RECOVERY_FILE_DEST'' AND
UPPER(vp2.name) = ''DB_RECOVERY_FILE_DEST_SIZE'''
INTO flashback_info.name, flashback_info.limit, flashback_info.used,
flashback_info.reclaimable, flashback_info.files,
flashback_info.file_dest, flashback_info.dsize;
EXECUTE IMMEDIATE 'SELECT count(*) FROM sys.dba_tables tb, sys.dba_queues q
WHERE q.queue_table = tb.table_name AND
tb.tablespace_name = '' || ts_info(t).name || '' AND tb.owner NOT IN
(''SYS'',''SYSTEM'',''MDSYS'',''ORDSYS'',''OLAPSYS'',''XDB'',
''LBACSYS'',''CTXSYS'',''ODM'',''DMSYS'', ''WKSYS'',''WMSYS'',
''SYSMAN'',''EXFSYS'') '
INTO delta_queues;
'SELECT file_name, autoextensible from sys.dba_temp_files ' ||
'where tablespace_name = :1' using ts_info(t).name;
'SELECT file_name, autoextensible from sys.dba_data_files ' ||
'where tablespace_name = :1' using ts_info(t).name;
EXECUTE IMMEDIATE 'SELECT version from v$timezone_file'
INTO db_tz_version;
'UPDATE registry$database set tz_version = :1'
USING db_tz_version;
'UPDATE registry$database set tz_version = :1'
USING db_tz_version;
DELETE sys.props$ WHERE name IN (''DST_UPGRADE_STATE'',
''DST_PRIMARY_TT_VERSION'',
''DST_SECONDARY_TT_VERSION'')';
EXECUTE IMMEDIATE 'INSERT INTO sys.props$ (name, value$, comment$)
VALUES (''DST_UPGRADE_STATE'', ''NONE'',
''State of Day Light Saving Time Upgrade'')';
EXECUTE IMMEDIATE 'INSERT INTO sys.props$ (name, value$, comment$)
VALUES (''DST_PRIMARY_TT_VERSION'', TO_CHAR( :1, ''FM999''),
''Version of primary timezone data file'')'
USING db_tz_version;
EXECUTE IMMEDIATE 'INSERT INTO sys.props$ (name, value$, comment$)
VALUES (''DST_SECONDARY_TT_VERSION'', ''0'',
''Version of secondary timezone data file'')';
EXECUTE IMMEDIATE 'SELECT directory_path from SYS.DBA_DIRECTORIES where directory_name=:1'
INTO path
USING c_dir_obj;
EXECUTE IMMEDIATE 'SELECT TO_CHAR(SYSTIMESTAMP,''YYYY-MM-DD HH24:MI:SS '') FROM SYS.DUAL'
INTO timeinfo;
DisplayLine (pPreScriptUFT, ' ''select dbms_preup.get_con_name from sys.dual'' into con_name;');
'select dbms_preup.get_con_name from sys.dual' INTO con_name;
DisplayLine (pPostScriptUFT, ' ''select dbms_preup.get_con_name from sys.dual'' into con_name;');
'select dbms_preup.get_con_name from sys.dual' INTO con_name;
EXECUTE IMMEDIATE 'SELECT TO_CHAR(SYSTIMESTAMP,''YYYY-MM-DD HH24:MI:SS '') FROM SYS.DUAL'
INTO timeinfo;
EXECUTE IMMEDIATE 'SELECT NLS_UPPER(platform_name) FROM v$database'
INTO platform;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter where '
|| 'name=''db_unique_name'''
INTO uniqueName;
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name =
LOWER(:1)'
INTO c_value
USING minvp(i).name;
sp(i).dbua_OutInUpdate := FALSE;
'SELECT default_tablespace FROM sys.dba_users WHERE username =:1'
INTO cmp_info(i).def_ts
USING schema;
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.dba_tables t
WHERE EXISTS
(SELECT 1 FROM sys.dba_queues q
WHERE q.queue_table = t.table_name AND q.owner = t.owner)
AND t.tablespace_name = :1 AND rownum <= 1'
INTO t_null
USING tsname;
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.dba_users
WHERE username = ''SYS'' AND temporary_tablespace = :1'
INTO t_null
USING tsname;
DisplayLine ('');
IF sp(i).dbua_OutInUpdate THEN
IF sp(i).db_match = TRUE AND
sp(i).newvalue IS NOT NULL THEN
--
DisplayLine ('');
DisplayCenter('[Update parameters]');
DisplayCenter('[Update Oracle Database ' || db_version ||
' init.ora or spfile]');
DisplayCenter('[No parameters to update]');
'SELECT status from V$INSTANCE',
c_dbua_detail_type_text,
'Close the database and reopen it using OPEN as the state',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
sp(i).dbua_OutInUpdate = FALSE THEN
DisplayLine('
IF sp(i).dbua_OutInUpdate = FALSE THEN
DisplayLine('
|| '''Select Options'' in Oracle installer and then select ' || crlf
|| 'Oracle Label Security.';
'SELECT status FROM sys.registry$ WHERE cid=''AMD''
AND namespace=''SERVER'''
INTO n_status;
'SELECT NULL FROM sys.user$ WHERE name=''AUDIT_ADMIN'''
INTO t_null;
EXECUTE IMMEDIATE 'SELECT user# FROM sys.user$ WHERE name=''APPQOSSYS'''
INTO tmp_num1;
'SELECT NULL FROM sys.obj$ WHERE owner# = (SELECT user# from SYS.USER$
WHERE name=''APPQOSSYS'') AND
name =''WLM_METRICS_STREAM'' AND type# = 2'
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE name = ''AUDSYS'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''AUDIT_VIEWER'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''SYSBACKUP'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''SYSDG'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''SYSKM'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''CAPTURE_ADMIN'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GSMCATUSER'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GSMUSER'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GSMADMIN_INTERNAL'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GSMUSER_ROLE'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GSM_POOLADMIN_ROLE'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GSMADMIN_ROLE'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''GDS_CATALOG_SELECT'''
INTO t_null;
result_txt:= genDBUAXMLCheck('GDS_CATALOG_SELECT',
c_check_level_error,
'A user or role named "GDS_CATALOG_SELECT" found in the database.',
'A user or role named "GDS_CATALOG_SELECT" found in the database.',
'"GDS_CATALOG_SELECT" user or role must be dropped prior to upgrading.',
c_dbua_detail_type_text,
'To drop the role "GDS_CATALOG_SELECT", use the command:'
|| ' DROP ROLE GDS_CATALOG_SELECT' || ', and To drop the user "GDS_CATALOG_SELECT"'
|| ' use the command: DROP USER GDS_CATALOG_SELECT CASCADE',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
return 'ERROR: --> A user or role with the name "GDS_CATALOG_SELECT" found in the database.' || crlf
|| crlf || ' This is an Oracle defined role.'
|| crlf || ' You must drop this user or role prior to upgrading.';
return 'The GDS_CATALOG_SELECT user or role must be dropped prior to upgrading.';
'SELECT NULL FROM sys.wrm$_wr_control WHERE dbid != (SELECT dbid FROM v$database)'
INTO t_null;
'To update the inactive DBIDs in AWR, run the script awrupd12.sql as SYSDBA',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_post );
|| crlf || ' For manual upgrades, update the compatible to 12.1.0 in your'
|| crlf || ' init.ora or spfile after shutting down the database, but prior'
|| crlf || ' to starting up the database for upgrade. The DBUA will automatically'
|| crlf || ' increase the compatible value prior to upgrading.';
|| crlf || crlf || ' Update your init.ora or spfile to make this change.';
EXECUTE IMMEDIATE 'SELECT NULL FROM dba_dependencies
WHERE referenced_name IN (''DBMS_LDAP'')
AND owner NOT IN (''SYS'',''PUBLIC'',''ORD_PLUGINS'')
AND rownum <= 1'
INTO t_null;
htmlentities('SELECT name FROM dba_dependencies WHERE'
|| ' referenced_name IN (''DBMS_LDAP'') '
|| ' AND owner NOT IN (''SYS'',''PUBLIC'',''ORD_PLUGINS'')'
|| ' AND rownum <= 1'),
c_dbua_fixup_type_auto,
c_dbua_fixup_stage_pre );
'SELECT DISTINCT owner FROM DBA_DEPENDENCIES
WHERE referenced_name IN (''DBMS_LDAP'')
AND owner NOT IN (''SYS'',''PUBLIC'',''ORDPLUGINS'')';
EXECUTE IMMEDIATE 'SELECT value FROM V$PARAMETER WHERE NAME=''processes'''
INTO processes;
EXECUTE IMMEDIATE 'SELECT value FROM V$PARAMETER WHERE NAME=''processes'''
INTO processes;
|| crlf || ' You should update your processes value prior to the upgrade'
|| crlf || ' to a value of at least ' || c_max_processes || '.'
|| crlf || ' For example:'
|| crlf || ' ALTER SYSTEM SET PROCESSES=' || c_max_processes || ' SCOPE=SPFILE'
|| crlf || ' or update your init.ora file.';
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.registry$ r, v$option o
WHERE r.cid = ''DV'' and r.cname = o.parameter and
o.value = ''TRUE'''
INTO t_null;
htmlentities ('SELECT r.cid FROM sys.registry$ r, v$option o '
|| 'WHERE r.cid = ''DV'' and r.cname = o.parameter and '
|| 'o.value = ''TRUE'''),
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.registry$ WHERE cid=''EM''
AND status NOT IN (99,8)'
INTO t_null;
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM sys.enabled$indexes'
INTO t_count;
|| ' query: SELECT COUNT(1) FROM SYS.ENABLED$INDEXES',
c_dbua_fixup_type_auto,
c_dbua_fixup_stage_pre);
'SELECT status FROM sys.registry$ WHERE (cid=''RUL'' OR cid=''EXF'')
WHERE namespace=''SERVER'''
INTO n_status;
EXECUTE IMMEDIATE 'SELECT NULL FROM v$recover_file WHERE rownum <=1'
INTO t_null;
htmlentities ('SELECT count(*) FROM v$recover_file WHERE rownum <=1'),
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
'SELECT NULL FROM sys.user$ WHERE (name=''FILES_BACKUP_MODE'' and type#=0)'
INTO t_null;
'SELECT name FROM sys.user$ WHERE (name=''FILES_BACKUP_MODE'' and type#=0)',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre);
'SELECT value, isdefault FROM v$parameter WHERE name = ''log_archive_format'''
INTO laf_format, tmp_varchar1;
|| ' Database Upgrade Assistant will update this parameter to database'
|| ' default value. This value can be customized after the upgrade.',
'log_archive_format is invalid',
'Update your initialization parameter to a valid value.',
c_dbua_detail_type_sql,
htmlentities('select value from v$parameter where name = ''log_archive_format'''),
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE name = ''log_archive_format'''
INTO format;
EXECUTE IMMEDIATE 'SELECT LOG_MODE from v$database'
INTO log_mode;
result_txt := 'ERROR: --> log_archive_format must be updated' || crlf
|| crlf || ' As of 10.1, log_archive_format requires a %r format qualifier'
|| crlf || ' be present in its format string. Your current setting is:'
|| crlf || ' log_archive_format=''' || format || '''.';
result_txt := 'Update log_archive_format prior to upgrade.';
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.dba_objects
WHERE status = ''INVALID'' AND object_name NOT LIKE ''BIN$%'' AND
rownum <=1'
INTO t_null;
EXECUTE IMMEDIATE 'SELECT NULL FROM SYS.DBA_OBJECTS
WHERE status = ''INVALID'' AND object_name NOT LIKE ''BIN$%'' AND
rownum <=1 AND
object_name NOT IN
(SELECT name FROM SYS.dba_dependencies
START WITH referenced_name IN (
''V$LOGMNR_SESSION'', ''V$ACTIVE_SESSION_HISTORY'',
''V$BUFFERED_SUBSCRIBERS'', ''GV$FLASH_RECOVERY_AREA_USAGE'',
''GV$ACTIVE_SESSION_HISTORY'', ''GV$BUFFERED_SUBSCRIBERS'',
''V$RSRC_PLAN'', ''V$SUBSCR_REGISTRATION_STATS'',
''GV$STREAMS_APPLY_READER'',''GV$ARCHIVE_DEST'',
''GV$LOCK'',''DBMS_STATS_INTERNAL'',''V$STREAMS_MESSAGE_TRACKING'',
''GV$SQL_SHARED_CURSOR'',''V$RMAN_COMPRESSION_ALGORITHM'',
''V$RSRC_CONS_GROUP_HISTORY'',''V$PERSISTENT_SUBSCRIBERS'',''V$RMAN_STATUS'',
''GV$RSRC_CONSUMER_GROUP'',''V$ARCHIVE_DEST'',''GV$RSRCMGRMETRIC'',
''GV$RSRCMGRMETRIC_HISTORY'',''V$PERSISTENT_QUEUES'',''GV$CPOOL_CONN_INFO'',
''GV$RMAN_COMPRESSION_ALGORITHM'',''DBA_BLOCKERS'',''V$STREAMS_TRANSACTION'',
''V$STREAMS_APPLY_READER'',''GV$SGA_DYNAMIC_FREE_MEMORY'',''GV$BUFFERED_QUEUES'',
''GV$RSRC_PLAN_HISTORY'',''GV$ENCRYPTED_TABLESPACES'',''V$ENCRYPTED_TABLESPACES'',
''GV$RSRC_CONS_GROUP_HISTORY'',''GV$RSRC_PLAN'',
''GV$RSRC_SESSION_INFO'',''V$RSRCMGRMETRIC'',''V$STREAMS_CAPTURE'',
''V$RSRCMGRMETRIC_HISTORY'',''GV$STREAMS_TRANSACTION'',''DBMS_LOGREP_UTIL'',
''V$RSRC_SESSION_INFO'',''GV$STREAMS_CAPTURE'',''V$RSRC_PLAN_HISTORY'',
''GV$FLASHBACK_DATABASE_LOGFILE'',''V$BUFFERED_QUEUES'',
''GV$PERSISTENT_SUBSCRIBERS'',''GV$FILESTAT'',''GV$STREAMS_MESSAGE_TRACKING'',
''V$RSRC_CONSUMER_GROUP'',''V$CPOOL_CONN_INFO'',''DBA_DML_LOCKS'',
''V$FLASHBACK_DATABASE_LOGFILE'',''GV$HM_RECOMMENDATION'',
''V$SQL_SHARED_CURSOR'',''GV$PERSISTENT_QUEUES'',''GV$FILE_HISTOGRAM'',
''DBA_WAITERS'',''GV$SUBSCR_REGISTRATION_STATS'')
AND referenced_type in (''VIEW'',''PACKAGE'') OR
name = ''V_$ROLLNAME''
CONNECT BY
PRIOR name = referenced_name and
PRIOR type = referenced_type)'
INTO t_null;
'SELECT count(*) FROM dba_tables
WHERE table_name = ''REGISTRY$SYS_INV_OBJS'''
INTO tbl_exists;
'INSERT INTO registry$sys_inv_objs
SELECT owner,object_name,object_type
FROM sys.dba_objects
WHERE status !=''VALID'' AND owner in (''SYS'',''SYSTEM'')
ORDER BY owner';
SELECT owner,object_name,object_type
FROM sys.dba_objects
WHERE status !=''VALID'' AND owner in (''SYS'',''SYSTEM'')
ORDER BY owner';
EXECUTE IMMEDIATE 'SELECT count(*) FROM sys.dba_objects
WHERE status !=''VALID'' AND owner NOT in (''SYS'',''SYSTEM'')'
INTO nonsys_invalid_objs;
'SELECT count(*) FROM dba_tables
WHERE table_name = ''REGISTRY$NONSYS_INV_OBJS'''
INTO tbl_exists;
'INSERT INTO registry$nonsys_inv_objs
SELECT owner,object_name,object_type
FROM sys.dba_objects
WHERE status !=''VALID'' AND owner NOT in (''SYS'',''SYSTEM'')
ORDER BY owner';
SELECT owner,object_name,object_type
FROM sys.dba_objects
WHERE status !=''VALID'' AND owner NOT in (''SYS'',''SYSTEM'')
ORDER BY owner';
'SELECT owner,object_name,object_type from registry$sys_inv_objs',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre);
EXECUTE IMMEDIATE 'SELECT COUNT(*) ' ||
'FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t, SYS.USER$ u ' ||
'WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# ' ||
'AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# ' ||
'AND BITAND(t.FLAGS, 256) = 256 AND o.OWNER# = u.USER# ' ||
'AND o.OWNER# in ' ||
'(SELECT r.schema# FROM SYS.REGISTRY$ r ' ||
'WHERE r.NAMESPACE = ''SERVER'')'
INTO t_count;
OPEN t_cursor FOR 'SELECT DISTINCT (o.NAME), u.NAME ' ||
'FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t, SYS.USER$ u ' ||
'WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# ' ||
'AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# ' ||
'AND BITAND(t.FLAGS, 256) = 256 AND o.OWNER# = u.USER# ' ||
'AND o.OWNER# in (SELECT r.schema# FROM SYS.REGISTRY$ r ' ||
'WHERE r.NAMESPACE = ''SERVER'')';
EXECUTE IMMEDIATE 'SELECT COUNT(*) ' ||
'FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t, SYS.USER$ u ' ||
'WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# ' ||
'AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# ' ||
'AND BITAND(t.FLAGS, 256) = 256 AND o.OWNER# = u.USER# ' ||
'AND o.OWNER# NOT IN ' ||
'(SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, ' ||
'SYS.REGISTRY$ r WHERE d.USER_ID = r.SCHEMA# ' ||
'AND r.NAMESPACE=''SERVER'')'
INTO t_count;
OPEN t_cursor FOR 'SELECT DISTINCT (o.NAME), u.NAME ' ||
'FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t, SYS.USER$ u ' ||
'WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# ' ||
'AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# ' ||
'AND BITAND(t.FLAGS, 256) = 256 AND o.OWNER# = u.USER# ' ||
'AND o.OWNER# NOT IN (SELECT UNIQUE (d.USER_ID) FROM ' ||
'SYS.DBA_USERS d, SYS.REGISTRY$ r WHERE ' ||
'd.USER_ID = r.SCHEMA# AND r.NAMESPACE=''SERVER'')';
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE
name=''job_queue_processes'''
INTO p_count;
'JOB_QUEUE_PROCESSES value must be updated.',
edetails,
'Either remove setting of JOB_QUEUE_PROCESSES value ' ||
'or set it to a value greater than ' || to_char(p_lowest) ||
'.',
c_dbua_detail_type_text,
'Either remove setting of JOB_QUEUE_PROCESSES value ' ||
'or set it to a value greater than ' || to_char(p_lowest) ||
'.',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
EXECUTE IMMEDIATE 'SELECT value FROM v$parameter WHERE
name=''job_queue_processes'''
INTO p_count;
|| crlf || ' This parameter must be removed or updated to a value greater'
|| crlf || ' than 0 (default value if not defined is 1000) prior to upgrade.'
|| crlf || ' Not doing so will affect the running of features that rely on'
|| crlf || ' this parameter, such as utlrp.sql after the upgrade.'
|| crlf || crlf || ' Update your init.ora or spfile to make this change.';
EXECUTE IMMEDIATE 'SELECT NULL FROM dba_dependencies
WHERE referenced_name IN (''DBMS_LDAP'')
AND owner NOT IN (''SYS'',''PUBLIC'',''ORD_PLUGINS'')
AND rownum <= 1'
INTO t_null;
'SELECT DISTINCT owner FROM DBA_DEPENDENCIES'
|| ' WHERE referenced_name IN'
|| ' (''UTL_TCP'',''UTL_SMTP'',''UTL_MAIL'',''UTL_HTTP'',''UTL_INADDR'')'
|| ' AND owner NOT IN (''SYS'',''PUBLIC'',''ORDPLUGINS'')';
'SELECT DISTINCT owner FROM all_tab_columns'
|| ' WHERE data_type IN'
|| ' (''ORDIMAGE'', ''ORDAUDIO'', ''ORDVIDEO'', ''ORDDOC'','
|| ' ''ORDSOURCE'', ''ORDDICOM'') AND'
|| ' (data_type_owner = ''ORDSYS'' OR'
|| ' data_type_owner = owner) AND'
|| ' (owner != ''PM'')';
'SELECT version from v$timezone_file',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
return 'Time zone data file must be updated in the new ORACLE_HOME.';
'SELECT user# from SYS.USER$ WHERE name=''ORACLE_OCM'''
INTO tmp_num1;
'SELECT NULL FROM sys.obj$ WHERE owner# = (SELECT user# from SYS.USER$
WHERE name=''ORACLE_OCM'') AND
name =''MGMT_DB_LL_METRICS'' AND type# = 9'
INTO t_null;
'Your time zone file must be updated, this can be done after the Upgrade is executed.',
'Execute the dbms_dst package after your database is upgraded.',
c_dbua_detail_type_text,
'Execute the dbms_dst package after your database is upgraded.',
c_dbua_fixup_type_auto,
c_dbua_fixup_stage_pre);
return 'Update the timezone using the DBMS_DST package after upgrade is complete.';
EXECUTE IMMEDIATE 'SELECT status FROM sys.registry$ WHERE cid=''OLS''
AND namespace=''SERVER'''
INTO status;
SELECT count(*) INTO preaud_cnt FROM dba_tables
WHERE table_name = 'AUD$' AND owner = 'SYS';
EXECUTE IMMEDIATE 'SELECT count(*) FROM dba_tables where OWNER=''SYS'' AND table_name=''PREUPG_AUD$'''
into preaud_cnt;
'To view the number of records that will be moved use the command: SELECT count(*) from system.aud$',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre);
EXECUTE IMMEDIATE 'SELECT count(*) FROM SYSTEM.aud$'
INTO aud_rowcnt;
'SELECT COUNT(*) FROM sys.dba_indexes WHERE index_type = ''DOMAIN''
and ityp_name = ''ORDIMAGEINDEX'''
INTO t_count;
htmlentities('SELECT COUNT(*) FROM sys.dba_indexes WHERE index_type'
|| ' = ''DOMAIN'' and ityp_name = ''ORDIMAGEINDEX'''),
c_dbua_fixup_type_auto,
c_dbua_fixup_stage_pre );
'SELECT dbai.index_name, dbai.owner FROM SYS.DBA_INDEXES dbai
WHERE dbai.index_type = ''DOMAIN'' AND
dbai.ityp_name = ''ORDIMAGEINDEX''
ORDER BY dbai.owner';
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.dba_2pc_pending WHERE rownum <=1'
INTO t_null;
htmlentities('SELECT count(*) FROM sys.dba_2pc_pending WHERE rownum <=1'),
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre);
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM sys.recyclebin$'
INTO obj_count;
'select count(*) from sys.recyclebin$',
c_dbua_fixup_type_auto,
c_dbua_fixup_stage_validation);
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM sys.recyclebin$'
INTO obj_count;
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.user$ WHERE name=''DMSYS'''
INTO t_null;
'select name from sys.user$ where name=''DMSYS''',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
EXECUTE IMMEDIATE 'SELECT NULL FROM sys.user$ WHERE name=''XBRLSYS'''
INTO t_null;
'select name from sys.user$ where name=''XBRLSYS''',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
'SELECT LOG_MODE FROM v$database'
INTO tmp_varchar1;
EXECUTE IMMEDIATE 'SELECT vp.value FROM v$parameter vp WHERE
UPPER(vp.NAME) = ''DB_RECOVERY_FILE_DEST'''
INTO tmp_varchar1;
select count(a.dest_name)
from v$archive_dest a join v$parameter p
on(lower(a.dest_name)=lower(p.name))
where p.name like ''log_archive_dest_%''
and regexp_like(p.value,''*[ ^]?location([ ])?=([ ])?*'',''i'')
and a.status=''VALID'''
INTO t_count;
SELECT count(*) FROM v$parameter v
WHERE v.NAME LIKE ''log_archive_dest_%'' AND
REGEXP_LIKE(v.VALUE,''*[ ^]?service([ ])?=([ ])?*'',''i'')'
INTO t_count;
SELECT count(*) FROM v$archive_dest ad
WHERE ad.dest_id=1 AND
ad.target=''STANDBY'''
INTO t_count;
EXECUTE IMMEDIATE 'SELECT NULL FROM v$parameter WHERE
name LIKE ''log_archive_dest%'' AND upper(value) LIKE ''SERVICE%''
AND rownum <=1'
INTO t_null;
EXECUTE IMMEDIATE 'SELECT NULL FROM v$database WHERE
database_role=''PRIMARY'''
INTO t_null;
EXECUTE IMMEDIATE 'SELECT COUNT(*)
FROM V$ARCHIVE_DEST_STATUS DS, V$ARCHIVE_DEST D
WHERE DS.DEST_ID = D.DEST_ID
AND D.TARGET = ''STANDBY''
AND NOT (DS.STATUS = ''VALID'' AND DS.GAP_STATUS = ''NO GAP'')'
INTO unsynch_standby_count;
htmlentities('SELECT name FROM v$parameter WHERE'
|| ' name LIKE ''log_archive_dest%'' AND'
|| ' upper(value) LIKE ''SERVICE%'' AND rownum <=1'),
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
EXECUTE IMMEDIATE 'SELECT default_tablespace FROM sys.dba_users WHERE username = ''SYS'''
INTO t_ts1;
EXECUTE IMMEDIATE 'SELECT default_tablespace FROM sys.dba_users WHERE username = ''SYSTEM'''
INTO t_ts2;
htmlentities('select username,default_tablespace from sys.dba_users'
|| ' where username IN (''SYS'',''SYSTEM'')'),
c_dbua_fixup_type_auto,
c_dbua_fixup_stage_pre );
EXECUTE IMMEDIATE 'SELECT default_tablespace FROM sys.dba_users WHERE username = ''SYS'''
INTO t_ts1;
EXECUTE IMMEDIATE 'SELECT default_tablespace FROM sys.dba_users WHERE username = ''SYSTEM'''
INTO t_ts1;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM wksys.wk$instance'
INTO i_count;
|| ' the following query: SELECT COUNT(*) FROM wksys.wk$instance',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_pre );
'SELECT NULL FROM sys.user$ WHERE name = ''PROVISIONER'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE name = ''XS_RESOURCE'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE name = ''XS_SESSION_ADMIN'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE name = ''XS_NAMESPACE_ADMIN'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE name = ''XS_CACHE_ADMIN'''
INTO t_null;
select_stmt VARCHAR2(500);
select_stmt := 'SELECT cname FROM sys.registry$ WHERE namespace=' ||
dbms_assert.enquote_literal('SERVER') ||
' AND cid NOT IN (' ||
my_components_list ||
')';
OPEN not_my_comps_cursor FOR select_stmt;
'SELECT NULL FROM sys.user$ WHERE NAME = ''EM_EXPRESS_BASIC'''
INTO t_null;
'SELECT NULL FROM sys.user$ WHERE NAME = ''EM_EXPRESS_ALL'''
INTO t_null;
check_stmt := 'select value from v$parameter where name=''open_cursors''';
'For example, to change parameter file: update the PFILE or use ' ||
'"ALTER SYSTEM SET OPEN_CURSORS=' || c_min_open_cursors || ' ' ||
'SCOPE=SPFILE". Note the update in the PFILE/SPFILE will not take ' ||
'effect until the next database startup.',
c_dbua_detail_type_text,
'To avoid exceeding number of open cursors during Oracle ' ||
'Application Express (APEX) upgrade, ' ||
'increase OPEN_CURSORS before upgrading the database.',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_validation);
'SELECT value FROM V$PARAMETER WHERE NAME=''open_cursors'''
INTO open_cursors;
|| crlf || ' o For example, to change parameter file: update the PFILE'
|| crlf || ' or use "ALTER SYSTEM SET OPEN_CURSORS='
|| c_min_open_cursors || ' SCOPE=SPFILE".'
|| crlf || ' Note the update in the PFILE/SPFILE will not take'
|| crlf || ' effect until the next database startup.';
select null into t_null
from sys.dba_triggers
where trigger_name='DBMS_JAVA_DEV_TRG' and owner='SYS'
and status='ENABLED';
'Oracle JavaVM (OJVM) Patch Set Update (PSU), then you should enable Java development just before the upgrade '||
'using the command: EXECUTE SYS.DBMS_JAVA_DEV.ENABLE; '||crlf||
|| crlf || ' installing the Oracle recommended Oracle JavaVM (OJVM) Patch Set Update (PSU), '
|| crlf || ' then you should enable Java development just before upgrade using the command: '||crlf
|| crlf || ' EXECUTE SYS.DBMS_JAVA_DEV.ENABLE;'||crlf
select null into t_null
from sys.dba_triggers
where trigger_name='DBMS_JAVA_DEV_TRG' and owner='SYS'
and status='ENABLED';
'Patch Set Update (PSU) if it was not applied before database upgrade '||
'on '||db_version||' ORACLE_HOME.'||' Please refer to MOS note 1929745.1 for more details.',
c_dbua_detail_type_text,
'Oracle JavaVM Component Database PSU is released as part of the Critical Patch Update program from October 2014 onwards. '||
'Oracle recommends applying the latest OJVM PSU patch to ALL databases that have Oracle JavaVM present in the database, '||
'regardless of whether you are explicitly using it or not. Even if Oracle JavaVM is not present in the database it is '||
'best practice to install the OJVM PSU in case a new database is created in the ORACLE_HOME.',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_post);
|| crlf || ' Critical Patch Update program from October 2014 onwards.'
|| crlf || ' Oracle recommends applying the latest OJVM PSU patch to ALL'
|| crlf || ' databases that have Oracle JavaVM present in the database,'
|| crlf || ' regardless of whether you are explicitly using it or not.'
|| crlf || ' Even if Oracle JavaVM is not present in the database it is'
|| crlf || ' best practice to install the OJVM PSU in case a new database'
|| crlf || ' is created in the ORACLE_HOME.'||crlf
|| crlf || ' After upgrade, apply latest Oracle JavaVM (OJVM) Bundle Patch (BP) or '
|| crlf || ' or Patch Set Update (PSU) if it was not applied before database upgrade '
|| crlf || ' on '||db_version||' ORACLE_HOME. Please refer to MOS note 1929745.1 for more details.';
return 'Apply latest Oracle JavaVM (OJVM) Bundle Patch (BP) or Patch Set Update (PSU).';
'SELECT version, to_number(replace(substr(version,1,6), ''.'', '''')) ' ||
'FROM sys.registry$ ' ||
'WHERE cid = ''APEX'' and namespace=''SERVER''';
'SELECT to_number(replace(substr(''' || c_apex_version ||
''',1,6), ''.'', '''')) FROM sys.dual';
check_stmt := 'SELECT version FROM sys.registry$ ' ||
'WHERE cid = ''APEX'' and namespace=''SERVER''';
check_stmt := 'SELECT version FROM sys.registry$ ' ||
'WHERE cid = ''APEX'' and namespace=''SERVER''';
'SELECT 1 FROM sys.v$parameter ' ||
'WHERE ' ||
'( ' || -- criteria (a)
' (upper(name) = ''RESOURCE_LIMIT'' AND isdefault = ''TRUE'') ' ||
' AND ' || -- criteria (b)
' 0 < (SELECT count(*) ' ||
' FROM sys.dba_users ' ||
' WHERE profile in ' ||
' (SELECT unique(profile) ' ||
' FROM sys.dba_profiles ' ||
' WHERE resource_type = ''KERNEL'' and ' ||
' limit not in (''UNLIMITED'', ''DEFAULT'')) ' ||
' ) ' ||
' AND ' || -- criteria (c)
' 1 = (SELECT count(*) ' ||
' FROM sys.registry$ ' ||
' WHERE ' ||
' upper(cid) = ''CATPROC'' AND ' ||
' (substr(version, 1, 4) in (''10.2'', ''11.1'', ''11.2'') ' ||
' OR substr(version, 1, 8) = ''12.1.0.1'') ' ||
' ) ' ||
')';
'For example, to change parameter file: update PFILE or use ' ||
'"ALTER SYSTEM SET RESOURCE_LIMIT=FALSE SCOPE=SPFILE". ' ||
'Note that the update in the PFILE/SPFILE will not take ' ||
'effect until the next database startup.',
c_dbua_detail_type_text,
'RESOURCE_LIMIT default value is FALSE in 12.1.0.1 release and ' ||
'earlier but is TRUE in 12.1.0.2 onwards.',
c_dbua_fixup_type_manual,
c_dbua_fixup_stage_validation);
||crlf||' o For example, to change parameter file: update PFILE or use'
||crlf||' "ALTER SYSTEM SET RESOURCE_LIMIT=FALSE SCOPE=SPFILE". Note that'
||crlf||' the update will not take effect until next database startup.';
IN ( select name hidden_param_name, value hidden_param_value
from SYS.V$PARAMETER
where name LIKE '\_%' ESCAPE '\'
and ismodified != 'MODIFIED'
order by name )
LOOP
IF pOutputDest = c_output_file AND pOutputType = c_output_text THEN
-- write into preupgrade.log
DisplayLine(pOutputUFT,
i.hidden_param_name || ' = ' || i.hidden_param_value);
IN ( select (translate(value,chr(13)||chr(10),' ')) event_value
from sys.v$parameter2
where upper(name) ='EVENT' and isdefault='FALSE' order by name )
LOOP
IF pOutputDest = c_output_file AND pOutputType = c_output_text THEN
-- write to preupgrade.log
DisplayLine(pOutputUFT, i.event_value);
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM sys.v$parameter WHERE name LIKE ''\_%'' ESCAPE ''\'' AND ismodified != ''MODIFIED'''
INTO t_status;
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM sys.v$parameter2 WHERE (UPPER(name) = ''EVENT''
OR UPPER(name)=''_TRACE_EVENTS'') AND isdefault=''FALSE'''
INTO t_status;
EXECUTE IMMEDIATE 'SELECT count(*) FROM sys.aud$ WHERE dbid is null'
INTO t_status;
EXECUTE IMMEDIATE 'SELECT count(*) FROM system.aud$ WHERE dbid is null'
INTO t_status;
EXECUTE IMMEDIATE 'SELECT count(*) FROM sys.fga_log$ WHERE dbid is null'
INTO t_status;
execute immediate 'select cdb from v$database'
into s_isCdb;
'select upper(SYS_CONTEXT(''USERENV'', ''CON_NAME'')) from sys.dual'
into conName;
execute immediate 'select upper(name) from sys.v$database' into conName;
'select SYS_CONTEXT(''USERENV'', ''CON_ID'') from sys.dual'
into conId;
'select count(*) from sys.v$pdbs'
into nPdbs;
'insert into sys.registry$log (cid, namespace, operation, optime) ' ||
' values (''PREUPG_BGN'', ' ||
' SYS_CONTEXT(''REGISTRY$CTX'', ''NAMESPACE''), -1, SYSTIMESTAMP)';
'insert into sys.registry$log (cid, namespace, operation, optime) ' ||
' values (''PREUPG_END'', ' ||
' SYS_CONTEXT(''REGISTRY$CTX'', ''NAMESPACE''), -1, SYSTIMESTAMP)';
EXECUTE IMMEDIATE 'SELECT open_mode FROM sys.v$database' INTO open_mode;