APEX Verification Script

-- Oracle APEX Diagnostic Agent for APEX Installs -- ===================================================== -- USAGE: -- ====== -- Login to SQL*PLUS as the SYSTEM user and then execute this SQL script -- as follows: -- @/apex_verify.sql -- By default output is written to apex_verify_out.html in the current directory REM The formatting method used in this note is based on the formatting methods used in the following notes: REM Oracle9iAS Portal Diagnostics Agent (PDA) (Doc ID 169490.1) REM Capture Single Sign-On Configuration Tables to HTML Formatted File (Doc ID 244112.1) clear buffer; set serveroutput on set echo off set arraysize 1 set trims on set linesize 240 set pagesize 0 set sqlprefix off set verify off set feedback off set heading off set timing off set define on set escape off --prompt V 3.4f - Modified APEX and ORDS account status queries, 23 JAN 2017 --prompt --prompt Enter output filename. If file exists will be overwritten. spool apex_verify_out.html exec dbms_output.put_line(''); exec dbms_output.put_line(''); --select 'APEX Verification Script' from dual; --select 'APEX Verification Script' from dual; select 'APEX Verification Script' from dual; --select 'APEX Verification Script' from dual; --select 'APEX Verification Script' from dual; --select 'APEX Verification Script' from dual; --select 'APEX Verification Script' from dual; select '

' || '' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' Ver 3.4f ' || '

' from dual; --START Active version of APEX in the DB define APEX = 'APEX IS NOT INSTALLED' column APEX_VER new_val APEX NOPRINT --use the following to get the apex schema for the version of apex registered in the dba_registry. SELECT SCHEMA APEX_VER FROM dba_registry WHERE comp_id = 'APEX'; --WHERE (comp_id = 'APEX' or comp_id like 'HTML%'); define GET_VER ='APEX_RELEASE'; define VERSION = '&APEX..&GET_VER'; --Above notionally resolves to APEX_040200.APEX_RELEASE --END determine Active version of APEX in the DB --START Determine tablespace used by APEX schema define APEX_TABLESPACE = 'NO TABLESPACE' column APEX_TAB new_val APEX_TABLESPACE NOPRINT select default_tablespace APEX_TAB from dba_users where username='&APEX'; --END Determine tablespace used by APEX schema --START Determine IF tablespace used by APEX schema is autoextend or not define APEX_TABLESPACE_AUTOEXTEND = 'NO' column APEX_TAB_AE new_val APEX_TABLESPACE_AUTOEXTEND NOPRINT select distinct(autoextensible)APEX_TAB_AE from dba_data_files where tablespace_name = '&APEX_TABLESPACE'; --END Determine IF tablespace used by APEX schema is autoextend or not --START Determine tablespace used by FLOWS_FILES define FLOWS_FILES_TABLESPACE = 'NO TABLESPACE' column FLOWS_FILES_TAB new_val FLOWS_FILES_TABLESPACE NOPRINT select default_tablespace FLOWS_FILES_TAB from dba_users where username='FLOWS_FILES'; --END Determine tablespace used by FLOWS_FILES --START Determine IF tablespace used by FLOWS_FILES schema is autoextend or not define FLOWS_FILES_TABLESPACE_AUTO = 'NO' column FLOWS_FILES_TAB_AE new_val FLOWS_FILES_TABLESPACE_AUTO NOPRINT select distinct(autoextensible)FLOWS_FILES_TAB_AE from dba_data_files where tablespace_name = '&FLOWS_FILES_TABLESPACE'; --END Determine IF tablespace used by FLOWS_FILES schema is autoetxend or not --START Determine temporary tablespace used by APEX Installation define TEMP_APEX = 'NO TABLESPACE' column APEX_TEMP new_val TEMP_APEX NOPRINT select temporary_tablespace APEX_TEMP from dba_users where username='&APEX'; --END Determine temporary tablespace used by APEX Installation --START DATABASE VERSION -- select banner from v$version; select '

APEX Database Information' || '

' FROM dual; select '' FROM dual; select '' FROM dual; select '' from v$version; select '

' \|\| 'DB Information
' \|\| banner \|\| '

' FROM dual; --COMMENTS --select ' For APEX 3.2 and below, DB must be 9.2.0.3 or above.
select ' For APEX 3.2 and below, DB must be 9.2.0.3 or above.
For APEX 4.0, DB must be 10.2.0.3 or above or 10g Express' from dual; --END DATABASE VERSION --start Get exact version of APEX select '
APEX ' || 'Version Registered in DBA Registry
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' from &VERSION; select '
' || 'Version ' || 'API Compatibility
' || version_no || ' ' || api_compatibility || '
' FROM dual; --end --Get exact version of APEX --Begin Get Number of Valids in the APEX Schema select '
Number of APEX Valids/Invalids in the &APEX and FLOWS_FILES schemas
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dba_objects where owner = upper('&APEX') and status='VALID'; select '
Total APEX Valids
' || count(1) || '
' FROM dual; --End Get Number of Valids in the APEX Schema --Begin Get number of Invalids in the APEX Schema select '' FROM dual; select '' FROM dual; select '' from dba_objects where owner = upper('&APEX') and status='INVALID'; select '
Total APEX Invalids
' || count(1) || '
' FROM dual; --End Get Number of invalids in the APEX Schema --Begin Get Number of Invalids in the flows_files schema select '' FROM dual; select '' FROM dual; select '' from dba_objects where owner = 'FLOWS_FILES' and status='INVALID'; select '
Total FLOWS_FILES Invalids
' || count(1) || '
' FROM dual; --End Get Number of Invalids in the flows_files schema --BEGIN Get information about Valids/Invalids in the APEX Schema select '
List of &APEX and FLOWS_FILES Invalid Objects
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' from dba_objects where owner in (UPPER('&APEX'),'FLOWS_FILES') and status = 'INVALID' order by object_type; select '
' || 'Object Name ' || 'Object Type
' || Object_name || ' ' || object_type || '
' FROM dual; --End Get information about Valids/Invalids in the APEX Schema --Start Get images directory exec dbms_output.put_line( '
Virtual Image Directory (default and recommended -> /i/)
' ); exec dbms_output.put_line( '' ); exec dbms_output.put_line( '' ); exec dbms_output.put_line( ''); --select '' from &APEX..wwv_flows where security_group_id = 10 and rownum=1; exec dbms_output.put_line( '
Virtual Directory
'||&APEX..wwv_flow_image_prefix.g_image_prefix || '
' || flow_image_prefix || '
' ); --End Get images directory --START APEX Related Schemas select '
APEX Related Schemas ' || '
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' from dba_users where ( username like 'APEX\_%' escape '\' or username = 'ANONYMOUS' or username = 'FLOWS_FILES' ) and username not in ( 'APEX_LISTENER', 'APEX_REST_PUBLIC_USER' ) order by username; select '
' || 'Username ' || 'Account Status
' || username || ' ' || account_status || '
' FROM dual; -- END APEX Related Schemas --START ORDS Related Schemas select '
ORDS Related Schemas ' || '
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' from dba_users where ( username like 'ORDS\_%' escape '\' or username in ( 'APEX_LISTENER', 'APEX_REST_PUBLIC_USER' )) order by username; select '
' || 'Username ' || 'Account Status
' || username || ' ' || account_status || '
' FROM dual; -- END ORDS Related Schemas --START Proxy Users select '
Proxy Users' || '
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' from proxy_users where proxy in ( 'APEX_REST_PUBLIC_USER', 'ORDS_REST_PUBLIC_USER' ) order by proxy, client; select '
' || 'Proxy ' || 'Client
' || proxy || ' ' || client || '
' FROM dual; -- END Proxy Users --START Prior APEX Versions which May be Cleaned Up exec dbms_output.put_line('
Prior APEX Versions which May be Cleaned Up
'); exec dbms_output.put_line(''); exec dbms_output.put_line(''); select '' from dba_users where (username like 'APEX\_0%' escape '\' or username like 'FLOWS\_0%' escape '\') and (username <> '&APEX') order by username asc; exec dbms_output.put_line('
Consider Removing All Listed
' || username ||'
'); --Comment on APEX Version Cleanup select '' || 'If your current APEX version is fully functional and backed up, consider removing earlier versions.
See "How to Uninstall Oracle HTML DB / Application Express from the Database (Doc ID 558340.1)"' from dual; --END Prior APEX Versions which May be Cleaned Up --START PL/SQL TOOLKIT VERSION -- select owa_util.get_version from dual; select '
PL/SQL Toolkit Version
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dual; select '
' || 'Version
' || owa_util.get_version || '
' FROM dual; -- COMMENTS select '' || 'Check the PL/SQL Web Toolkit version. If less than 10.1.2.0.6 then ' || 'upgrade (discuss with Oracle Support before upgrading)' || '' from dual; --END PL/SQL TOOLKIT VERSION --start DUPLICATE OWA PACKAGES -- SELECT OWNER, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME = 'OWA'; select '
Duplicate OWA ' || 'packages
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' FROM DBA_OBJECTS WHERE OBJECT_NAME = 'OWA'; select '
' || 'Owner ' || 'Object Type
' || owner ||' ' || object_type || '
' FROM dual; -- COMMENTS select '' || 'Make sure you do not have duplicate copies of OWA packages. You should see the output as below:

SYS..............PACKAGE
SYS..............PACKAGE BODY
PUBLIC........SYNONYM' from dual; --end DUPLICATE OWA PACKAGES --START Shared Pool Size select '
Shared Pool Size - Please see the APEX Installation Guide for your APEX/DB version for required settings ' || '
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from v$parameter where name = 'shared_pool_size'; select '
Shared Pool Size (MB)
' ||value/1024/1024|| '
' FROM dual; --END Shared Pool Size --START NLS Characterset Values select '
NLS CHARACTER SET Information
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '' from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); select '
Parameter Parameter Value
' || parameter ||' ' || value ||'
' FROM dual; --END - NLS Characterset Values --START Free Space in System select '
Free Space in System Tablespace ' || '
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dba_free_space where tablespace_name ='SYSTEM'; select '
MB Free in System
' ||sum(bytes)/1024/1024|| '
' FROM dual; --END Free Space in System Tablespace --START Free Space in APEX Tablespace select '
Free Space in &APEX_TABLESPACE Tablespace (AUTOEXTEND=&APEX_TABLESPACE_AUTOEXTEND) used by &APEX
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dba_free_space where tablespace_name ='&APEX_TABLESPACE'; select '
Free Space in MB
' ||sum(bytes)/1024/1024|| '
' FROM dual; --END Free Space in APEX Tablespace --START Free Space in FLOWS_FILES Tablespace select '
Free Space in &FLOWS_FILES_TABLESPACE (AUTOEXTEND=&FLOWS_FILES_TABLESPACE_AUTO) Tablespace used by FLOWS_FILES
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dba_free_space where tablespace_name ='&FLOWS_FILES_TABLESPACE'; select '
Free Space in MB
' ||sum(bytes)/1024/1024|| '
' FROM dual; --END Free Space in FLOWS_FILES Tablespace --START Temporary Tablespace used by the APEX Installation (By default the APEX Schema and FLOWS_FILES use same temporary tablespace during installation) select '
Default Temporary Tablespace used for &APEX is: &TEMP_APEX
' FROM dual; --select '' FROM dual; --END Temporary Tablespace used by the APEX Installation (By default the APEX Schema and FLOWS_FILES use same temporary tablespace during installation) -- Begin Get Job Queue Processes select '
Number of Job Queue Processes
' FROM dual; select '
' FROM dual; select '' FROM dual; select '' from v$parameter where name='job_queue_processes'; select '
Number of Job Queue Processes
' || value || '
' FROM dual; -- End Get Job Queue Processes --Start Get information about XML DB select '
' || 'XDB STATUS
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; col owner format a10 col object_name format a20 select '', '', '', '' from dba_objects where object_name = 'DBMS_XMLPARSER'; select '
' || 'owner ' || 'object_name ' || 'object_type ' || 'Status
' || owner ||' ' || object_name || ' ' || object_type || ' ' || status || '
' FROM dual; select '' || 'Make sure XML DB packages are installed and valid. You should see the output as below:

PUBLIC....DBMS_XMLPARSER....SYNONYM..............VALID
XDB..........DBMS_XMLPARSER....PACKAGE...............VALID
XDB..........DBMS_XMLPARSER....PACKAGE BODY....VALID' from dual; --END Get information about XML DB --Start Determine if APEX is a Development or Runtime Installation define WWV_FLOWS = 'WWV_FLOWS' define INSTALL_TYPE = '&APEX..&WWV_FLOWS' select '
' || 'APEX Install Type (1=Dev 0=Runtime)
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from &INSTALL_TYPE where id = 4000; select '
' ||'Install Type
' || count(1) || '
' FROM dual; --End Determine if APEX is a Development or Runtime Installation --BEGIN Determine if APEX has ever been used exec dbms_output.put_line('
Has APEX Been Used?
'); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); select '', '' from apex_workspaces order by workspace_id; exec dbms_output.put_line('
Workspace ID Workspace Name
' || workspace_id ||' ' || workspace_display_name || '
'); --Comment on usage select '' || 'Internal Workspaces have IDs less than 100,000.
Any Workspace ID >= 100,000 indicates APEX has been used. (In 12c Multi-Tenant, check only valid for PDB).' from dual; --END Determine if APEX has ever been used --Start Determine DB Service Name select '
' || 'Database Service Name
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from v$parameter where name='service_names'; select '
' || 'DB Service Name
' || value || '
' FROM dual; --End Determine DB Service Name --START check for enabling of Network Services select '
' || 'Enabling of Network Services (11g DBs and Later)
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '','' from dba_network_acl_privileges; select '
' || 'ACL ' || 'Principal ' || 'Privilege
' || acl || ' ' || principal || ' ' || privilege || '
' FROM dual; --END check for enabling of network services --START Get DBA Registry Info select '
' ||'DBA Registry Info
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; col comp_name format a30 col version format a10 col status format a10 col comp_id format a15 select '', '', '', '', '' from dba_registry; select '
' ||'Component ID ' || 'Component Name ' || 'Version ' || 'Schema ' || 'Status
' || comp_id ||' ' || comp_name || ' ' || version || ' ' || schema || ' ' || status || '
' FROM dual; --END Get DBA Registry Info --START Get APEX Instance Settings exec dbms_output.put_line( '
'||' APEX Instance Settings
' ); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); select '', '', '' from &APEX..wwv_flow_platform_prefs order by name; -- where NAME in('AUTOEXTEND_TABLESPACES','BIGFILE_TABLESPACES_ENABLED','PRINT_BIB_LICENSED','PRINT_SVR_PROTOCOL','PRINT_SVR_HOST','PRINT_SVR_PORT','SMTP_HOST_ADDRESS','SMTP_HOST_PORT') order by name; --from &APEX..wwv_flow_platform_prefs order by name; exec dbms_output.put_line('
' ||'Name ' ||'Value ' ||'Description
' ||Name||' ' ||Value || ' ' ||pref_desc|| '
'); --END Get APEX Instance Settings --START Show the number of objects granted to the APEX schema exec dbms_output.put_line( '
'||' APEX Instance Grant Information. (Note that grant details may vary between APEX versions).
' ); exec dbms_output.put_line('

'); exec dbms_output.put_line(''); exec dbms_output.put_line(''); select '' from dba_tab_privs where grantee = '&APEX'; exec dbms_output.put_line('
Total Objects Granted to &APEX
' || count(1) ||'
'); --END --Start Get Grants given to APEX Schema exec dbms_output.put_line('
The following displays all grants issued to the &APEX Schema.
'); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); select '', '' from dba_tab_privs where grantee = '&APEX' order by table_name; exec dbms_output.put_line('
Table Name Privilege
' || table_name || ' ' || privilege || '
'); --END --START TOTAL INVALID OBJECTS select '
' || 'Number of Invalid Objects in the DB
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dba_objects where status = 'INVALID'; select '
' || 'Total Invalid Objects in DB
' || count(1) || '
' FROM dual; -- COMMENTS select '' || 'There should be no invalid objects in the database ' || 'pertaining to the owners within APEX/FLOWS. If there ' || 'are any, recompile. Use the utlrp.sql script under the ' || 'database home to recompile.' from dual; --end TOTAL INVALID OBJECTS --START LIST OF ALL INVALID OBJECTS IN THE DATABASE select '
' || 'List of ALL Invalid Objects in the DB
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '', '', '' from DBA_OBJECTS where status = 'INVALID' order by owner; select '
Owner Object Name Object type Status
' || OWNER ||' ' || object_name ||' ' || object_type ||' ' || status ||'
' FROM dual; --END LIST OF INVALID OBJECTS IN THE DATABASE --START TOTAL INVALID SYNONYMS select '
' || 'Number of Invalid Synonyms in the DB
' FROM dual; select '' FROM dual; select '' FROM dual; select '' from dba_objects where status = 'INVALID' and object_type='SYNONYM'; select '
' || 'Total Invalid Synonyms in DB
' || count(1) || '
' FROM dual; -- COMMENTS select '' || 'There should be no invalid objects in the database ' || 'pertaining to the owners within APEX/FLOWS. If there ' || 'are any, recompile. Use the utlrp.sql script under the ' || 'database home to recompile.' from dual; --end TOTAL INVALID SYNONYMS --START LIST OF INVALID SYNONYMS AND THEIR OWNERS IN THE DATABASE select '
' || 'List of Invalid SYNONYMS in the DB
' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '' FROM dual; select '', '', '', '' from DBA_SYNONYMS A, DBA_OBJECTS B where A.SYNONYM_NAME=B.OBJECT_NAME AND A.OWNER=B.OWNER AND B.STATUS='INVALID' order by A.OWNER; select '
Synonym Owner Synonym Name Object Owner Object Name
' || A.OWNER ||' ' || A.SYNONYM_NAME ||' ' || A.TABLE_OWNER ||' ' || A.TABLE_NAME ||'
' FROM dual; --END LIST OF INVALID SYNONYMS AND THEIR OWNERS IN THE DATABASE exec dbms_output.put_line(''); spool off