Oracle PRVTEMX_ADMIN
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose Undocumented
AUTHID CURRENT_USER
Dependencies
DBA_ROLES DUAL V$DATABASE
DBMS_ASSERT GV$INSTANCE V$PARAMETER
DBMS_REPORT PLITBLM V$PDBS
DBMS_SQL PRVTEMX_DBHOME V$SYSTEM_PARAMETER
DBMS_SQLTUNE PRVTEMX_RSRCMGR V_$OBJECT_PRIVILEGE
DBMS_SQLTUNE_UTIL1 PRVTEMX_SQL WRI$_REPT_CONFIG
DBMS_SQLTUNE_UTIL2 PRVT_EMX WRI$_REPT_SECURITY
DBMS_STANDARD PRVT_REPORT_TAGS WRI$_REPT_STORAGE
DBMS_SYS_ERROR PRVT_SMGUTIL XMLTYPE
DBMS_UADV_ARR SYSTEM_PRIVILEGE_MAP XQSEQUENCE
DBMS_UNDO_ADV    
Documented No
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to EM_EXPRESS_BASIC
Source {ORACLE_HOME}/rdbms/admin/prvsemx_admin.plb
Subprograms
 
ADD_REDOLOG_GROUP_MEMBER_XML
Undocumented prvtemx_admin.add_redolog_group_member_xml(
p_group_number IN NUMBER,
p_file_name    IN VARCHAR2,
p_show_sql     IN NUMBER,
p_report_ref   IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
BACKUP_CONTROL_FILE_XML
Undocumented prvtemx_admin.backup_control_file_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.backup_control_file_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/backup_control_file]]></report_id>
 <sql>ALTER DATABASE BACKUP CONTROLFILE TO TRACE</sql>
</report>
 
CLEAR_REDO_LOGFILE_XML
Undocumented prvtemx_admin.clear_redo_logfile_xml(
p_group_number IN NUMBER,
p_show_sql     IN NUMBER,
p_report_ref   IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_admin.clear_redo_logfile_xml(2, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/clear_redo_logfile]]></report_id>
 <sql>ALTER DATABASE CLEAR LOGFILE GROUP 2</sql>
</report>
 
CREATE_REDOLOG_GROUP_XML
Undocumented prvtemx_admin.create_redolog_group_xml(
p_group_number  IN NUMBER,
p_thread_number IN NUMBER,
p_files         IN XMLTYPE,
p_size          IN VARCHAR2,
p_show_sql      IN NUMBER,
p_report_ref    IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
DROP_REDOLOG_GROUP_XML
Undocumented prvtemx_admin.drop_redolog_group_xml(
p_group_number IN NUMBER,
p_show_sql     IN NUMBER,
p_report_ref   IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_admin.drop_redolog_group_xml(2, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/drop_redolog_group]]></report_id>
 <sql>ALTER DATABASE DROP LOGFILE GROUP 2</sql>
</report>
 
FORCE_CHECKPOINT_XML
Undocumented prvtemx_admin.force_checkpoint_xml(
p_show_sql   IN NUMBER,
p_report_ref IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_admin.force_checkpoint_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/force_checkpoint]]></report_id>
 <sql>ALTER SYSTEM CHECKPOINT</sql>
</report>
 
GET_REMOTE_UNDO_INFO
Undocumented prvtemx_admin.get_remote_undo_info(
p_is_summary            IN NUMBER,
p_analysis_start_period IN DATE,
p_analysis_end_period   IN DATE,
p_desired_retention     IN NUMBER)
RETURN VARCHAR2;
TBD
 
REPORT_ADD_DATAFILE_XML
Undocumented prvtemx_admin.report_add_datafile_xml(
p_tbs_name       IN VARCHAR2,
p_tbs_type       IN VARCHAR2,
p_oracle_managed IN NUMBER,
p_datafiles      IN XMLTYPE,
p_df_number      IN NUMBER,
p_df_size        IN VARCHAR2,
p_df_auto_extend IN NUMBER,
p_df_next_size   IN VARCHAR2,
p_df_max_size    IN VARCHAR2,
p_df_reuse       IN NUMBER,
p_show_sql       IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_ALTER_PROFILE_XML
Undocumented prvtemx_admin.report_alter_profile_xml(
p_profile  IN VARCHAR2,
p_limits   IN CLOB,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_ALTER_TBS_INMEMORY_XML (new 12.2)
Undocumented prvtemx_admin.report_alter_tbs_inmemory_xml(
p_tbs_name  IN VARCHAR2,
p_in_memory IN NUMBER,
p_show_sql  IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_ALTER_USER
Undocumented prvtemx_admin.report_alter_user(
p_username           IN VARCHAR2,
p_auth_type          IN VARCHAR2,
p_newpasswd          IN VARCHAR2,
p_profile            IN VARCHAR2,
p_passwd_expire      IN NUMBER,
p_account_lock       IN NUMBER,
p_default_tablespace IN VARCHAR2,
p_temp_tablespace    IN VARCHAR2,
p_quota_tablespace   IN VARCHAR2,
p_max_bytes          IN VARCHAR2,
p_show_sql           IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_alter_user('AUDSYS', 'PASSWORD', 'Orac1e', 'DEFAULT', p_show_sql=>1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/alter_user?profile=DEFAULT&show_sql=1&username=AUDSYS]]></report_id>
 <sql>alter user &quot;AUDSYS&quot; identified by ******* profile &quot;DEFAULT&quot; container=ALL;</sql>
</report>
 
REPORT_CHANGE_DF_STATUS_XML
Undocumented prvtemx_admin.report_change_df_status_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_datafile IN VARCHAR2,
p_status   IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_CHANGE_TBS_STATUS_XML
Undocumented prvtemx_admin.report_change_tbs_status_xml(
p_name           IN VARCHAR2,
p_type           IN VARCHAR2,
p_status         IN VARCHAR2,
p_offline_option IN VARCHAR2,
p_show_sql       IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_change_tbs_status_xml('USERS', 'PERMANENT', 'OFFLINE', p_show_sql=>1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/change_tbs_status]]></report_id>
 <sql>ALTER TABLESPACE &quot;USERS&quot; OFFLINE;</sql>
</report>
 
REPORT_CREATE_PROFILE_XML
Undocumented prvtemx_admin.report_create_profile_xml(
p_profile  IN VARCHAR2,
p_limits   IN CLOB,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_CREATE_ROLE_XML
Undocumented prvtemx_admin.report_create_role_xml(
p_role     IN VARCHAR2,
p_privs    IN CLOB,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_CREATE_TABLESPACE_XML
Undocumented prvtemx_admin.report_create_tablespace_xml(
p_name                IN VARCHAR2,
p_type                IN VARCHAR2,
p_oracle_managed      IN NUMBER,
p_datafileS           IN XMLTYPE,
p_df_number           IN NUMBER,
p_df_size             IN VARCHAR2,
p_df_auto_extend      IN NUMBER,
p_df_next_size        IN VARCHAR2,
p_df_max_size         IN VARCHAR2,
p_df_reuse            IN NUMBER,
p_bigfile             IN NUMBER,
p_block_size          IN VARCHAR2,
p_logging             IN VARCHAR2,
p_force_logging       IN NUMBER,
p_encryption          IN NUMBER,
p_enc_algorithm       IN VARCHAR2,
p_enc_password        IN VARCHAR2,
p_compression         IN VARCHAR2,
p_status              IN VARCHAR2,
p_tbs_group           IN VARCHAR2,
p_ext_allocation      IN VARCHAR2,
p_ext_allocation_size IN VARCHAR2,
P_auto_seg_mgmt       IN NUMBER,
p_retention           IN NUMBER,
p_is_default          IN NUMBER,
p_show_sql            IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_CREATE_USER_XML
Undocumented prvtemx_admin.report_create_user_xml(
p_username           IN VARCHAR2,
p_auth_type          IN VARCHAR2,
p_passwd             IN VARCHAR2,
p_profile            IN VARCHAR2,
p_default_tablespace IN VARCHAR2,
p_temp_tablespace    IN VARCHAR2,
p_passwd_expire      IN NUMBER,
p_account_lock       IN NUMBER,
p_privs              IN CLOB,
p_show_sql           IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_create_user_xml('C##ABC', 'PASSWORD', 'ZZYZX', 'DEFAULT', 'USERS', 'TEMP', p_show_sql=>1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/create_user?account_lock=0&auth_type=PASSWORD&default_tablespace=USERS
 &passwd_expire=0&profile=DEFAULT&show_sql=1&temp_tablespace=TEMP&username=C##ABC]]></report_id>
 <sql>create user &quot;C##ABC&quot; identified by ******* profile &quot;DEFAULT&quot; account unlock default tablespace
 &quot;USERS&quot; temporary tablespace &quot;TEMP&quot;;
 alter user &quot;C##ABC&quot; set container_data=all container=current;</sql>
</report>
 
REPORT_DBSEARCH_XML (new 12.2)
Undocumented prvtemx_admin.report_dbsearch_xml(
p_params IN dbms_report.ref_string_idspec)
RETURN XMLTYPE;
TBD
 
REPORT_DF_AUTO_EXTEND_XML
Undocumented prvtemx_admin.rpoert_df_auto_extend_xml(
p_tbs_name       IN VARCHAR2,
p_tbs_type       IN VARCHAR2,
p_datafile       IN VARCHAR2,
p_df_auto_extend IN NUMBER,
p_df_next_size   IN VARCHAR2,
p_df_max_size    IN VARCHAR2,
p_show_sql       IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_DROP_DATAFILE_XML
Undocumented prvtemx_admin.report_drop_datafile_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_datafile IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_DROP_profile_XML
Undocumented prvtemx_admin.report_drop_profile_xml(
p_profile  IN VARCHAR2,
p_cascade  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_drop_profile_xml('UWPROFILE', 0, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/drop_profile?cascade=0&profile=UWPROFILE&show_sql=1]]></report_id>
 <sql>drop profile &quot;UWPROFILE&quot;;</sql>
</report>
 
REPORT_DROP_ROLE
Undocumented prvtemx_admin.report_drop_role(
p_role     IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_drop_role('CONNECT', 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/drop_role?role=CONNECT&show_sql=1]]></report_id>
 <sql>drop role &quot;CONNECT&quot;;</sql>
</report>
 
REPORT_DROP_TABLESPACE_XML
Undocumented prvtemx_admin.report_drop_tablespace_xml(
p_name             IN VARCHAR2,
p_drop_contents    IN NUMBER,
P_drop_datafiles   IN NUMBER,
p_drop_constraints IN NUMBER,
p_show_sql         IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_DROP_USER
Undocumented prvtemx_admin.report_drop_user(
p_username IN VARCHAR2,
p_cascade  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_drop_user(USER, 0, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/drop_user?cascade=0&show_sql=1&username=SYS]]></report_id>
 <sql>drop user &quot;SYS&quot;;</sql>
</report>
 
REPORT_GRANT_PRIV_XML
Undocumented prvtemx_admin.report_grant_priv_xml(
p_username     IN VARCHAR2,
p_privs        IN CLOB,
p_schema       IN VARCHAR2,
p_objects      IN CLOB,
p_grant_all    IN NUMBER,
p_grant_option IN NUMBER,
p_show_sql     IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_RESIZE_DATAFILE_XML
Undocumented prvtemx_admin.report_resize_datafile_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_datafile IN VARCHAR2,
p_df_size  IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_RESIZE_TABLESPACE_XML
Undocumented prvtemx_admin.report_resize_tablespace_xml(
p_name     IN VARCHAR2,
p_type     IN VARCHAR2,
p_size     IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SERVER_INFO_XML
Undocumented prvtemx_admin.report_server_info_xml(
p_locale   IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SET_DEFAULT_TBS_XML
Undocumented prvtemx_admin.report_set_default_tbs_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SET_PARAM_XML
Undocumented prvtemx_admin.report_set_param_xml(
p_name           IN VARCHAR2,
p_scope          IN VARCHAR2,
p_sid            IN VARCHAR2,
p_deferred       IN NUMBER,
p_comment        IN VARCHAR2,
p_type           IN NUMBER,
p_value          IN VARCHAR2,
p_value_list_xml IN XMLTYPE,
p_show_sql       IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_set_param_xml('AUDIT_SYS_OPERATIONS', 'SPFILE', '*', 0, p_value=>'TRUE', p_show_sql=>1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/config/set_param]]></report_id>
 <sql>alter system reset &quot;AUDIT_SYS_OPERATIONS&quot; scope=SPFILE sid=&apos;*&apos;;</sql>
</report>
 
REPORT_SET_TBS_GROUP_XML
Undocumented prvtemx_admin.report_set_tabs_group_xml(
p_tbs_name  IN VARCHAR2,
p_tbs_group IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SHOW_ARCHLOG_FILES_XML
Undocumented prvtemx_admin.report_show_archlog_files_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_archlog_files_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/show_archlog_files]]></report_id>
 <script><![CDATA[
  -- parameters needed to run the show parameter query
  var b_date_fmt varchar2(100);
  -- initialize parameter value
  begin
    :b_date_fmt := dbms_report.date_fmt;
  end;
  /

  -- get information about control files
  select xmlelement("archlogs", xmlagg(xmlelement("file",
  xmlattributes(al.sequence# as "seq", al.thread# as "thread", al.blocks * block_size as "size",
  al.first_change# as "lowest_scn", to_char(al.first_time, :b_date_fmt) as "lowest_scn_time",
  to_char(al.completion_time, :b_date_fmt) as "archive_time", nls_initcap(al.creator) as "creator",
  nls_initcap(al.registrar) as "registrar", nls_initcap(al.status) as "status",
  nls_initcap(al.is_recovery_dest_file) as "reco",  al.backup_count as "backup_count",
  al.compressed as "compressed", al.backed_by_vss as "vss"), al.name)))
  from v$archived_log al
 ;]]></script>
</report>
 
REPORT_SHOW_CONTROL_FILES_XML
Undocumented prvtemx_admin.report_show_control_files_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_control_files_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/show_control_files]]></report_id>
 <script><![CDATA[
 -- parameters needed to run the show parameter query
 var b_date_fmt varchar2(100);
 -- initialize parameter value
 begin
   :b_date_fmt := dbms_report.date_fmt;
 end;
 /

 -- get information about control files
 select
 -- list of files
 xmlelement("control", null, (select xmlelement("files", xmlagg(xmlelement("file", xmlattributes(nls_initcap(reco) as "reco", bsize as "bsize", fsize as "fsize"), name) order by name))
 from (
  select name, is_recovery_dest_file reco, block_size bsize, block_size * file_size_blks fsize
  from v$controlfile)),
  -- size information
  (select xmlelement("records", xmlagg(xmlelement("rec", xmlattributes(nls_initcap(type) as "type",
   record_size as "rsize", records_used as "used", records_total as "total"), null)
   order by record_size * records_total))
   from v$controlfile_record_section),
  -- advanced data
  (select xmlelement("advanced", xmlattributes(nls_initcap(controlfile_type) as "type",
   to_char(controlfile_created, :b_date_fmt) as "created", controlfile_sequence# as "seq",
   controlfile_change# as "scn", to_char(controlfile_time, :b_date_fmt) as "modified"), null)
   from v$database))
  from dual
 ;]]></script>
</report>
 
REPORT_SHOW_DB_PROPS_XML
Undocumented prvtemx_admin.report_show_dbs_props_xml(
p_show_sql IN NUMBER,
p_reptag   IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_db_props_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/config/show_db_props]]></report_id>
 <script><![CDATA[
  -- parameters needed to run the show database properties query
  /

  -- get information about database properties
  select xmlelement("database_properties", xmlagg(xmlelement("prop",
  xmlattributes(p.property_name as "name", p.property_value as "value", p.description as "description"))))
  from database_properties p
  ;]]>
 </script>
</report>
 
REPORT_SHOW_FEATURE_USAGE_XML
Undocumented prvtemx_admin.report_show_feature_usage_xml(
p_show_sql IN NUMBER,
p_reptag   IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_feature_usage_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/config/show_feature_usage]]></report_id>
 <script><![CDATA[
  -- parameters needed to run the show parameter query
  var b_date_fmt varchar2(100);
  -- initialize parameter value
  begin
    :b_date_fmt := dbms_report.date_fmt;
  end;
  /

  -- get information about feature usage
  select xmlconcat(
    (select xmlelement("features", xmlagg(xmlelement("f",
     xmlattributes(s.dbid as "dbid", s.name as "name", s.version as "ver",
     s.detected_usages as "usages", s.total_samples as "samples",
     s.currently_used as "curr_used", to_char(s.first_usage_date, :b_date_fmt) as "f_usage_date",
     to_char(s.last_usage_date, :b_date_fmt) as "l_usage_date", s.aux_count as "aux_count",
     to_char(s.last_sample_date, :b_date_fmt) as "l_sample_date",
     s.last_sample_period as "l_sample_period",
     s.sample_interval as "sample_interval",
     s.description as "description"),
     s.feature_info) order by s.version desc))
     from dba_feature_usage_statistics s), (
  select xmlelement("high_water_marks", xmlagg(xmlelement("hwm", xmlattributes(
  nls_initcap(s.name) as "name", s.description as "description", s.version as "ver",
  s.highwater as "highwater", s.last_value as "last_value"))))
  from dba_high_water_mark_statistics s, v$database d
  where s.version in (
    select max(version)
    from dba_high_water_mark_statistics)
  and s.dbid = d.dbid))
  from dual
  ;]]>
 </script>
</report>
 
REPORT_SHOW_OBJECTS_XML
Undocumented prvtemx_admin.report_show_objects_xml(
p_limit IN NUMBER,
p_owner IN VARCHAR2,
p_type  IN VARCHAR2,
p_name  IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
REPORT_SHOW_OBJFILTERS_XML
Undocumented prvtemx_admin.report_show_objfilters_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_objfilters_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_objfilters]]></report_id>
 <script>
   select xmlelement(&quot;obj_owners&quot;,
   xmlagg(xmlelement(&quot;obj_owner&quot;, xmlattributes(OWNER as &quot;schema&quot;))
   order by OWNER))
   from (select distinct OWNER from SYS.all_objects);
 </script>
 <script>
  select xmlelement(&quot;obj_types&quot;,
  xmlagg(xmlelement(&quot;obj_type&quot;,
  xmlattributes(OBJECT_TYPE_NAME as &quot;type&quot;))
  order by OBJECT_TYPE_NAME))
  from (select distinct OBJECT_TYPE_NAME from SYS.v_$object_privilege);
 </script>
</report>
 
REPORT_SHOW_OBJ_PRIVS_XML
Undocumented prvtemx_admin.report_show_obj_privs_xml(
p_type     IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SHOW_PARAMS_XML
Undocumented prvtemx_admin.report_show_params_xml(
p_level    IN VARCHAR2,
p_meta     IN NUMBER,
p_name     IN VARCHAR2,
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER,
p_reptag   IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SHOW_PRIVS_XML
Undocumented prvtemx_admin.report_show_privs_xml(
p_username     IN VARCHAR2,
p_granted_only IN NUMBER,
p_show_sql     IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_privs_xml('AUDSYS', 1, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_privs?granted_only=1&username=AUDSYS]]></report_id>
 <script><![CDATA[
  var b_username varchar2(128);
  begin
    :b_username := 'AUDSYS';
  end;
  /

  select xmlelement("granted_privs", xmlagg(xmlelement("priv",
         xmlattributes(NAME as "name", case ADMIN_OPTION when 'YES' then '1' else '0' end as "adm",
                                       case DEFAULT_ROLE when 'YES' then '1' else '0' end as "default",
                                       case COMMON       when 'YES' then '1' else '0' end as "common",
         IS_ROLE as "is_role"))
  order by GRANTEE, NAME))
  from (
    select GRANTEE, PRIVILEGE as NAME, ADMIN_OPTION, null as DEFAULT_ROLE, COMMON, '0' as IS_ROLE
    from SYS.dba_sys_privs
    UNION ALL
    select GRANTEE, GRANTED_ROLE as NAME, ADMIN_OPTION, DEFAULT_ROLE, COMMON, '1' as IS_ROLE
    from SYS.dba_role_privs) p
  where grantee=:b_username
  and COMMON='YES';]]>
 </script>
</report>
 
REPORT_SHOW_PROFILEDETAIL_XML
Undocumented prvtemx_admin.report_show_profiledetail_xml(
p_profile  IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_profiledetail_xml('DEFAULT', 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_profiledetail?profile=DEFAULT&show_sql=1]]></report_id>
 <script><![CDATA[
  var b_profile varchar2(128);
  begin
    :b_profile := 'DEFAULT';
  end;
  /

  select xmlelement("profiles", xmlelement("profile", xmlattributes(PROFILE as "profile"),
  xmlelement("limits", xmlagg(xmlelement("limit",
  xmlattributes(RESOURCE_NAME as "resource_name", RESOURCE_TYPE as "type", LIMIT as "limit"))))))
  from SYS.dba_profiles
  where PROFILE=:b_profile
  group by PROFILE;]]>
 </script>
</report>
 
REPORT_SHOW_PROFILES_XML
Undocumented prvtemx_admin.report_show_profiles_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_profiles_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_profiles?show_sql=1]]></report_id>
 <script><![CDATA[
  select xmlelement("profiles", xmlagg(xmlelement("profile",
  xmlattributes(p1.PROFILE as "profile", p1.LIMIT as "connect_time", p2.LIMIT as "sessions_per_user"))
  order by p1.PROFILE))
  from (
    select PROFILE, LIMIT from SYS.dba_profiles where RESOURCE_NAME='CONNECT_TIME') p1
    inner join (
      select PROFILE, LIMIT from SYS.dba_profiles where RESOURCE_NAME='SESSIONS_PER_USER') p2 on p1.PROFILE = p2.PROFILE
      inner join (
      select PROFILE, count(distinct CON_ID) as CON_COUNT
      from SYS.cdb_profiles
      where CON_ID != 2
      group by PROFILE) pc on pc.PROFILE = p1.PROFILE
      inner join (
      select count(CON_ID) as CON_COUNT
      from SYS.v_$containers c
      where c.con_id != 2) c on c.CON_COUNT=pc.CON_COUNT
 ;]]></script>
</report>
 
REPORT_SHOW_REDOLOG_FILES_XML
Undocumented prvtemx_admin.report_show_redolog_files_xml(
p_group_number IN NUMBER,
p_show_sql     IN NUMBER,
p_reptag       IN NUMBER,
p_report_ref   IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_redolog_files_xml(1, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/show_redolog_files]]></report_id>
 <script><![CDATA[
  -- parameters needed to run the show parameter query
  var b_date_fmt varchar2(100);
  var b_group_number number;
  -- initialize parameter value
  begin
    :b_date_fmt := dbms_report.date_fmt;
    :b_group_number := 1;
  end;
  /

  -- list all log files and group information
  select xmlelement("groups", xmlagg(xmlelement("group", xmlattributes(l.group# as "num", max(l.thread#) as "thread",
         max(l.sequence#) as "seq", round(max(l.bytes)/1024) as "size_kb", nls_initcap(max(l.archived)) as "archived",
         nls_initcap(max(l.status)) as "status", max(l.first_change#) as "lowest_scn",
         max(to_char(l.first_time, :b_date_fmt)) as "lowest_scn_time", max(l.next_change#) as "next_scn",
         max(to_char(l.next_time, :b_date_fmt)) as "next_scn_time"),
         xmlagg(xmlelement("member", xmlattributes(nls_initcap(lf.type) as "type", nls_initcap(lf.status) as "status",
         nls_initcap(lf.is_recovery_dest_file) as "reco"), lf.member)
         order by member))
         order by l.group#))
  from v$log l, v$logfile lf
  where lf.group# = l.group#
  and l.group# = nvl(:b_group_number, l.group#)
  and lf.group# = nvl(:b_group_number, lf.group#)
  group by l.group#;]]>
 </script>
</report>
 
REPORT_SHOW_ROLEDETAIL_XML
Undocumented prvtemx_admin.report_show_roledetail_xml(
p_role     IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
-- output formatting is incomplete due to the time required to do it all
SELECT prvtemx_admin.report_show_roledetail_xml('DBA', 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_roledetail?role=DBA]]></report_id>
 <script><![CDATA[
  var b_role varchar2(128);

  begin
    :b_role := 'DBA';
  end;
  /
  select xmlelement("privs", xmlagg(xmlelement("priv",
  xmlattributes(NAME as "name", AUTHENTICATION_TYPE as "auth_type", case COMMON when 'YES' then '1' else '0' end as "common"))
  order by NAME))
  from (
    select ROLE as NAME, AUTHENTICATION_TYPE, 1 as IS_ROLE, COMMON
    from SYS.dba_roles
    where COMMON='YES')
  where NAME = :b_role;]]></script>
 <script><![CDATA[
  var b_username varchar2(128);

  begin
    :b_username := 'DBA';
  end;
  /

  select xmlelement("granted_privs", xmlagg(xmlelement("priv",
  xmlattributes(NAME as "name", case ADMIN_OPTION when 'YES' then '1' else '0' end as "adm",
  case DEFAULT_ROLE when 'YES' then '1' else '0' end as "default",
  case COMMON when 'YES' then '1' else '0' end as "common",
  IS_ROLE as "is_role"))
  order by GRANTEE, NAME))
  from (
    select GRANTEE, PRIVILEGE as NAME, ADMIN_OPTION, null as DEFAULT_ROLE, COMMON, '0' as IS_ROLE
    from SYS.dba_sys_privs
    UNION ALL
    select GRANTEE, GRANTED_ROLE as NAME, ADMIN_OPTION, DEFAULT_ROLE, COMMON, '1' as IS_ROLE
    from SYS.dba_role_privs) p
    where grantee=:b_username
    and COMMON='YES';]]>
 </script>
 <script><![CDATA[
  var b_username varchar2(128);

  begin
    :b_username := 'DBA';
  end;
  /
  select xmlelement("granted_objprivs", xmlagg(xmlelement("obj_priv",
  xmlattributes(OWNER as "schema", TABLE_NAME as "object", GRANTOR as "grantor", PRIVILEGE as "privilege",
  case GRANTABLE when 'YES' then '1' else '0' end as "grantable", case HIERARCHY when 'YES' then '1' else '0' end as "hierarchy",
  case COMMON when 'YES' then '1' else '0' end as "common",
  TYPE as "type"))
  order by OWNER, TYPE, TABLE_NAME))
  from SYS.dba_tab_privs
  where grantee=:b_username
  ;]]>
 </script>
</report>
 
REPORT_SHOW_ROLES_XML
Undocumented prvtemx_admin.report_show_roles_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_roles_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_roles?show_sql=1]]></report_id>
 <script><![CDATA[
  select xmlelement("privs", xmlagg(xmlelement("priv", xmlattributes(NAME as "name",
  AUTHENTICATION_TYPE as "auth_type", case COMMON when 'YES' then '1' else '0' end as "common")) order by NAME))
  from (select ROLE as NAME, AUTHENTICATION_TYPE, 1 as IS_ROLE, COMMON from SYS.dba_roles where COMMON='YES' );]]>
 </script>
</report>
 
REPORT_SHOW_TABLESPACES_XML
Undocumented prvtemx_admin.report_show_tablespaces_xml(
p_level    IN VARCHAR2,
p_tbs_name IN VARCHAR2,
p_show_sql IN NUMBER,
p_reptag   IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SHOW_TBLSPCNAMES_XML
Undocumented prvtemx_admin.report_show_tblspcnames_xml(
p_status   IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_SHOW_UNDO_DETAILS_XML
Undocumented prvtemx_admin.report_show_undo_details_xml(
p_inst_id               IN NUMBER,
p_analysis_start_period IN DATE,
p_analysis_end_period   IN DATE,
p_desired_retention     IN NUMBER,
p_report_ref            IN VARCHAR2,
p_show_sql              IN NUMBER)
RETURN XMLTYPE;
-- output formatting is incomplete due to the time required to do it all
SELECT prvtemx_admin.report_show_undo_details_xml(1, SYSDATE-30, SYSDATE-1/24, p_show_sql=>1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.59" cpu_time="0.53" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/show_undo_details]]></report_id>
 <undo_details>
  <report_parameters inst_id="1" start_analysis_period="12/24/2014 21:02:59" end_analysis_period="12/31/2014 21:02:59" duration="604800"/>
  <settings inst_id="1" inst_name="orabase" undo_management="auto" undo_retention="900" undo_tbs_name="UNDOTBS1" block_size="8192" temp_enabled="0" oldest_mem_time="12/29/2014 11:44:26"/>
  <tablespaces>
    <report_tablespaces>
      <level>all</level>
      <tbs_name>UNDOTBS1</tbs_name>
    </report_tablespaces>
    <tbs name="UNDOTBS1" bsz="8192" co="U" em="L" at="S" sm="M" ret="NG">
      <df ae="YES" kb="660480" fkb="632704" ib="640" ukb="659456">C:\APP\ORACLE\ORADATA\ORABASE\UNDOTBS01.DBF</df>
    </tbs>
  </tablespaces>
  <info tsn="UNDOTBS1" ts="32768" aex="1" unr="900" auto="1" rg="0" br="40021833" rr="1912" hr="0">
    <undo_sizes target="1912" target_index="5" start="637" end="5736" inc="254.95">
     42,42,42,42,42,42,42,42,42,42,42,42,43,44,44,47,50,56,56,57,57</undo_sizes>
    <advisor>Finding 1:The undo tablespace is OK. </advisor>
  </info>
 <script><![CDATA[

-- parameters needed to run the show parameter query
var b_date_fmt varchar2(100);
var b_dbid number;
var b_inst_id number;
var b_block_size number;
var b_use_mem number;
var b_mem_begin_time varchar2(100);
var b_mem_end_time varchar2(100);
var b_use_awr number;
var b_awr_begin_time varchar2(100);
var b_awr_end_time varchar2(100);

-- initialize parameter value
begin
:b_date_fmt := dbms_report.date_fmt;
:b_dbid := 1933765913;
:b_inst_id := 1;
:b_block_size := 8192;
:b_use_mem := 1;
:b_mem_begin_time := '12/29/2014 11:44:26';
:b_mem_end_time := '12/31/2014 21:02:59';
:b_use_awr := 1;
:b_awr_begin_time := '12/24/2014 21:02:59';
:b_awr_end_time := '12/29/2014 11:44:26';
end;
/

-- get information about undo statistics
select xmlelement(
"stats",
xmlattributes(
'undo' as "type",
round(max(undo_kbs), 2) as "max_gen_rate_kbs",
round(sum(undo_kb) / max(total_time), 2) as "avg_gen_rate_kbs",
round(max(active_kb + unexpired_kb + expired_kb)) as "max_used_kb",
substr(max(undotsn), instr(max(undotsn), '@')+1) as "last_undotsn",
substr(max(maxquerylen),
instr(max(maxquerylen), '@') + 1) as "max_sqlid",
to_number(substr(max(maxquerylen), 1,
instr(max(maxquerylen), '@') - 1)) as "max_qlen",
round(min(min_undoretention)) as "min_tuned_retention",
round(max(max_undoretention)) as "max_tuned_retention",
round(sum(nospaceerrcnt)) as "no_space_errors",
round(sum(ssolderrcnt)) as "snap_too_old_error",
round(sum(txncount) / max(total_time)) as "avg_txn_per_sec",
round(sum(txncount)) as "txn_count",
round(max(maxconcurrency)) as "max_concurrency",
round(sum(unexpired_reuse)) as "unexpired_reused"),
xmlelement("stat_info", xmlelement("stat", xmlattributes(1 as "id", 'undo_gen_rate' as "name", 1024 as "factor", 'bytes' as "unit")),
xmlelement("stat", xmlattributes(2 as "id", 'active_undo_kb' as "name", 1024 as "factor", 'bytes' as "unit")),
xmlelement("stat", xmlattributes(3 as "id", 'unexpired_undo_kb' as "name", 1024 as "factor", 'bytes' as "unit")),
xmlelement("stat", xmlattributes(4 as "id", 'expired_undo_kb' as "name", 1024 as "factor", 'bytes' as "unit")),
xmlelement("stat", xmlattributes(5 as "id", 'unexpired_steal' as "name")), xmlelement("stat", xmlattributes(6 as "id", 'expired_steal' s "name")),
xmlelement("stat", xmlattributes(7 as "id", 'unexpired_rel' as "name")),
xmlelement("stat", xmlattributes(8 as "id", 'expired_rel' as "name")),
xmlelement("stat", xmlattributes(9 as "id", 'unexpired_reused' as "name")),
xmlelement("stat", xmlattributes(10 as "id", 'expired_reused' as "name")),
xmlelement("stat", xmlattributes(11 as "id", 'space_error_cnt' as "name")),
xmlelement("stat", xmlattributes(12 as "id", 'snapold_error_cnt' as "name")),
xmlelement("stat", xmlattributes(13 as "id", 'trans_cnt' as "name")),
xmlelement("stat", xmlattributes(14 as "id", 'max_concurency' as "name")),
xmlelement("stat", xmlattributes(15 as "id", 'tuned_retentation' as "name", 'time' as "unit"))),
xmlelement("buckets", xmlattributes(max(bs) as "bucket_interval", max(bid)+1 as "bucket_count", to_char(min(start_time), :b_date_fmt) as "start_time", to_char(min(end_time), :b_date_fmt) as "end_time",
max(total_time) as "duration"),
xmlagg(
xmlelement(
"bucket",
xmlattributes(
(bid + 1) as "bucket_id"),
decode(round(undo_kbs, 2), 0, null,
xmlelement("stat",
xmlattributes(1 as "id",
round(undo_kbs, 2) as "value"))),
decode(round(active_kb), 0, null,
xmlelement("stat",
xmlattributes(2 as "id",
round(active_kb) as "value"))),
decode(round(unexpired_kb), 0, null,
xmlelement("stat",
xmlattributes(3 as "id",
round(unexpired_kb) as "value"))),
decode(round(expired_kb), 0, null,
xmlelement("stat",
xmlattributes(4 as "id",
round(expired_kb) as "value"))),
decode(round(unexpired_steal), 0, null,
xmlelement("stat",
xmlattributes(5 as "id",
round(unexpired_steal) as "value"))),
decode(round(expired_steal), 0, null,
xmlelement("stat",
xmlattributes(6 as "id",
round(expired_steal) as "value"))),
decode(round(unexpired_rel), 0, null,
xmlelement("stat",
xmlattributes(7 as "id",
round(unexpired_rel) as "value"))),
decode(round(expired_rel), 0, null,
xmlelement("stat",
xmlattributes(8 as "id",
round(expired_rel) as "value"))),
decode(round(unexpired_reuse), 0, null,
xmlelement("stat",
xmlattributes(9 as "id",
round(unexpired_reuse) as "value"))),
decode(round(expired_reuse), 0, null,
xmlelement("stat",
xmlattributes(10 as "id",
round(expired_reuse) as "value"))),
decode(round(nospaceerrcnt), 0, null,
xmlelement("stat",
xmlattributes(11 as "id",
round(nospaceerrcnt) as "value"))),
decode(round(ssolderrcnt), 0, null,
xmlelement("stat",
xmlattributes(12 as "id",
round(ssolderrcnt) as "value"))),
decode(round(txncount), 0, null,
xmlelement("stat",
xmlattributes(13 as "id",
round(txncount) as "value"))),
decode(round(maxconcurrency), 0, null,
xmlelement("stat",
xmlattributes(14 as "id",
round(maxconcurrency) as "value"))),
decode(round(max_undoretention), 0, null,
xmlelement("stat",
xmlattributes(15 as "id",
round(max_undoretention) as "value"))))
order by bid)))
from (
select bid,
min(start_time) start_time,
max(end_time) end_time,
(max(end_time) - min(start_time)) * 86400 total_time,
min(bto) bto,
max(eto) eto,
max(bs) bs,
max(undotsn) undotsn,
(sum(perc * undoblks) *:b_block_size) / 1024 undo_kb,
(sum(perc * undoblks) * :b_block_size) / max(bs) / 1024 undo_kbs,
sum(perc * txncount) txncount,
max(maxquerylen) maxquerylen,
sum(perc * maxconcurrency) maxconcurrency,
sum(perc * unxpstealcnt) unexpired_steal,
sum(perc * unxpblkrelcnt) unexpired_rel,
sum(perc * unxpblkreucnt) unexpired_reuse,
sum(perc * expstealcnt) expired_steal,
sum(perc * expblkrelcnt) expired_rel,
sum(perc * expblkreucnt) expired_reuse,
sum(perc * nospaceerrcnt) nospaceerrcnt,
sum(perc * ssolderrcnt) ssolderrcnt,
(sum(perc * activeblks) * :b_block_size) / 1024 active_kb,
(sum(perc * unexpiredblks) * :b_block_size) / 1024 unexpired_kb,
(sum(perc * expiredblks) * :b_block_size) / 1024 expired_kb,
min(tuned_undoretention) min_undoretention,
max(tuned_undoretention) max_undoretention
from (
select /* ordered use_hash(vr2) */
decode(c2, 1, sbid, sbid + 1) bid,
decode(c2, 1, ibto, (sbid+1) * bs) bto,
decode(c2, 2, ieto, least(ieto,
((sbid+1) * bs)-1)) eto,
decode(c2, 1, p1, p2) perc,
round(decode(c2, 1, p1, p2) * idur) dur,
vr2.*
from (
select 1 c1, 1 c2 from dual union all
select 1 c1, 2 c2 from dual) explode, (
select /*+ no_merge */
1 c1,
-- start bucket id
trunc(ibto / bs) sbid,
-- percentage coverage of first bucket
(least(ieto,
(trunc(ibto / bs) + 1) * bs) - ibto) / idur p1,
-- percentage coverage of second bucket
greatest(0,
ieto - ((trunc(ibto / bs) + 1) * bs) + 1) / idur p2,
vr1.*
from (
select greatest(round(((end_time -
start_time) * 86400) / 128), max_dur) bs,
round(ieto - ibto) idur,
vr0.*
from (
select min(begin_time) over() start_time,
max(end_time) over() end_time,
max(round((end_time -
begin_time) * 86400)) over() max_dur,
round((begin_time -
(min(begin_time) over())) * 86400) ibto,
round(((end_time -
(min(begin_time) over())) * 86400) - 1) ieto,
to_char(end_time, :b_date_fmt) || '@' || undotsn undotsn,
undoblks,
txncount,
decode(maxquerylen, 0, null,
to_char(maxquerylen, 'FM000000000000') || '@' ||
maxquerysqlid) maxquerylen,
maxconcurrency,
unxpstealcnt,
unxpblkrelcnt,
unxpblkreucnt,
expstealcnt,
expblkrelcnt,
expblkreucnt,
ssolderrcnt,
nospaceerrcnt,
activeblks,
unexpiredblks,
expiredblks,
tuned_undoretention
from (
select begin_time, end_time, undotsn, undoblks, txncount, maxquerylen, maxquerysqlid, maxconcurrency, unxpstealcnt, unxpblkrelcnt, unxpblkreucnt, expstealcnt, expblkrelcnt, expblkreucnt, ssolderrcnt,
nospaceerrcnt, activeblks, unexpiredblks, expiredblks, tuned_undoretention
from dba_hist_undostat
where begin_time >= to_date(:b_awr_begin_time, :b_date_fmt)
and end_time <= to_date(:b_awr_end_time, :b_date_fmt)
and dbid = :b_dbid
and instance_number = :b_inst_id
and :b_use_awr = 1
union
select begin_time, end_time, undotsn, undoblks, txncount, maxquerylen, maxqueryid maxquerysqlid, maxconcurrency, unxpstealcnt, unxpblkrelcnt, unxpblkreucnt, expstealcnt, expblkrelcnt, expblkreucnt,
ssolderrcnt, nospaceerrcnt, activeblks, unexpiredblks, expiredblks, tuned_undoretention
from gv$undostat
where begin_time >= to_date(:b_mem_begin_time, :b_date_fmt)
and end_time <= to_date(:b_mem_end_time, :b_date_fmt)
and inst_id = :b_inst_id
and :b_use_mem= 1) undo) vr0) vr1) vr2
   where vr2.c1 = explode.c1
   -- post filter eliminating first (resp. second) part of the row if
   -- associated percentage is 0
   and (explode.c2 = 1 or vr2.p2 != 0)) vr3
   group by bid
   );]]>
  </script>
 </undo_details>
</report>
 
REPORT_SHOW_UNDO_SUMMARY_XML
Undocumented prvtemx_admin.report_show_undo_summary_xml(
p_inst_id  IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
-- output formatting is incomplete due to the time required to do it all
SELECT prvtemx_admin.report_show_undo_summary_xml(1, 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/show_undo_summary]]></report_id>
 <script><![CDATA[

  -- parameters needed to run the show parameter query
  var b_date_fmt varchar2(100);
  var b_inst_id_low number;
  var b_inst_id_high number;

  -- initialize parameter value
  begin
    :b_date_fmt := dbms_report.date_fmt;
    :b_inst_id_low := 1;
    :b_inst_id_high := 1;
  end;
  /

-- get information about undo statistics
select xmlelement("undo_summary", xmlagg(xmlelement("instance",
xmlattributes(inst_id as "inst_id", inst_name as "inst_name", block_size as "block_size", nls_initcap(undo_management) as "management",
undo_retention as "retention", undo_tablespace as "tablespace", undo_tablespace_size as "tablespace_size"),
decode(info_xml, null, null, xmltype(info_xml)),
xmlelement(
"undostats",
xmlattributes(
to_char(begin_time, :b_date_fmt) as "bt",
to_char(end_time, :b_date_fmt) as "et",
duration as "dur",
undoblks as "blks",
txncount as "tc",
maxquerylen as "mql",
maxconcurrency as "mconc",
unxpstealcnt as "ustc",
unxpblkrelcnt as "urlc",
unxpblkreucnt as "uruc",
expstealcnt as "estc",
expblkrelcnt as "erlc",
expblkreucnt as "eruc",
ssolderrcnt as "snerr",
nospaceerrcnt as "sperr",
tuned_undoretention as "tur"), null))
order by inst_id))
from (
select inst_id,
max(decode(name, 'inst_name', value, null)) inst_name,
max(decode(name, 'undo_info', value, null)) info_xml,
max(decode(name, 'db_block_size', value, null)) block_size,
max(decode(name, 'undo_management', value, null)) undo_management,
max(decode(name, 'undo_retention', value, null)) undo_retention,
max(decode(name, 'undo_tablespace', value, null)) undo_tablespace,
max(decode(name, 'undo_tablespace_size', value, null))
undo_tablespace_size,
max(begin_time) begin_time,
max(end_time) end_time,
max(duration) duration,
max(undoblks) undoblks,
max(txncount) txncount,
max(maxquerylen) maxquerylen,
max(maxconcurrency) maxconcurrency,
max(unxpstealcnt) unxpstealcnt,
max(unxpblkrelcnt) unxpblkrelcnt,
max(unxpblkreucnt) unxpblkreucnt,
max(expstealcnt) expstealcnt,
max(expblkrelcnt) expblkrelcnt,
max(expblkreucnt) expblkreucnt,
max(ssolderrcnt) ssolderrcnt,
max(nospaceerrcnt) nospaceerrcnt,
max(tuned_undoretention) tuned_undoretention
from table(gv$(cursor(
select userenv('INSTANCE') inst_id,
vr0.*
from (
select null name,
null value,
min(begin_time) begin_time,
max(end_time) end_time,
(max(end_time) - min(begin_time)) * 86400 duration,
decode(sum(undoblks), 0, null,
sum(undoblks)) undoblks,
decode(sum(txncount), 0, null,
sum(txncount)) txncount,
decode(max(maxquerylen), 0, null,
max(maxquerylen)) maxquerylen,
decode(max(maxconcurrency), 0, null,
max(maxconcurrency)) maxconcurrency,
decode(sum(unxpstealcnt), 0, null,
sum(unxpstealcnt)) unxpstealcnt,
decode(sum(unxpblkrelcnt), 0, null,
sum(unxpblkrelcnt)) unxpblkrelcnt,
decode(sum(unxpblkreucnt), 0, null,
sum(unxpblkreucnt)) unxpblkreucnt,
decode(sum(expstealcnt), 0, null,
sum(expstealcnt)) expstealcnt,
decode(sum(expblkrelcnt), 0, null,
sum(expblkrelcnt)) expblkrelcnt,
decode(sum(expblkreucnt), 0, null,
sum(expblkreucnt)) expblkreucnt,
decode(sum(ssolderrcnt), 0, null,
sum(ssolderrcnt)) ssolderrcnt,
decode(sum(nospaceerrcnt), 0, null,
sum(nospaceerrcnt)) nospaceerrcnt,
max(tuned_undoretention) tuned_undoretention
from v$undostat
union all
select name, value,
null begin_time, null end_time, null duration,
null undoblks, null txncount, null maxquerylen,
null maxconcurrency, null unxpstealcnt,
null unxpblkrelcnt, null unxpblkreucnt,
null expstealcnt, null expblkrelcnt,
null expblkreucnt, null ssolderrcnt, null nospaceerrcnt,
null tuned_undoretention
from v$parameter
where name in ('undo_management', 'undo_retention',
'undo_tablespace', 'db_block_size')
union all
select 'undo_tablespace_size' name,
to_char(sum(f.bytes)) value,
null begin_time, null end_time, null duration,
null undoblks, null txncount, null maxquerylen,
null maxconcurrency, null unxpstealcnt,
null unxpblkrelcnt, null unxpblkreucnt,
null expstealcnt, null expblkrelcnt,
null expblkreucnt, null ssolderrcnt, null nospaceerrcnt,
null tuned_undoretention
from v$parameter p, v$tablespace t, v$datafile f
where p.name = 'undo_tablespace'
and upper(p.value) = t.name
and f.ts# = t.ts#
group by t.name
union all
select 'undo_info' name,
sys.prvtemx_admin.get_remote_undo_info(1) value,
null begin_time, null end_time, null duration,
null undoblks, null txncount, null maxquerylen,
null maxconcurrency, null unxpstealcnt,
null unxpblkrelcnt, null unxpblkreucnt,
null expstealcnt, null expblkrelcnt,
null expblkreucnt, null ssolderrcnt,
null nospaceerrcnt, null tuned_undoretention
from v$timer
  union all
  select 'inst_name' name, INSTANCE_NAME value,
  null begin_time, null end_time, null duration,
  null undoblks, null txncount, null maxquerylen,
  null maxconcurrency, null unxpstealcnt,
  null unxpblkrelcnt, null unxpblkreucnt,
  null expstealcnt, null expblkrelcnt,
  null expblkreucnt, null ssolderrcnt,
  null nospaceerrcnt, null tuned_undoretention
  from v$instance) vr0)))
  where inst_id between :b_inst_id_low and :b_inst_id_high
  group by inst_id) ut;]]>
 </script>
</report>
 
REPORT_SHOW_UNUSED_UNDOTBS_XML
Undocumented prvtemx_admin.report_show_unused_undotbs_xml(p_inst_id IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_unused_undotbs_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.04" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/storage/show_unused_undo_tbs]]></report_id>
 <unused_undotbs/>
</report>
 
REPORT_SHOW_USERDETAIL_XML
Undocumented prvtemx_admin.report_show_userdetail_xml(
p_username IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
-- output formatting is incomplete due to the time required to do it all
SELECT prvtemx_admin.report_show_userdetail_xml('AUDSYS', 1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
 <report_id><![CDATA[/orarep/security/show_userdetail?username=AUDSYS]]></report_id>
 <script><![CDATA[
  var b_date_fmt varchar2(100);
  begin
    :b_date_fmt := dbms_report.date_fmt;
    :b_username := 'AUDSYS';
  end;
  /
  select xmlelement("users", xmlagg(xmlelement("user",
xmlattributes(
USERNAME as "username",
ACCOUNT_STATUS as "account_status",
PDB_LOCKED as "pdb_locked",
MULTI_STATUS as "multi_status",
to_char(LOCK_DATE, :b_date_fmt) as "lock_date",
MULTI_LOCK_DATE as "multi_lock_date",
to_char(EXPIRY_DATE, :b_date_fmt) as "expiry_date",
MULTI_EXPIRY_DATE as "multi_expiry_date",
DEFAULT_TABLESPACE as "default_tablespace",
MULTI_DEFAULT_TABLESPACE as "multi_default_tablespace",
TEMPORARY_TABLESPACE as "temporary_tablespace",
MULTI_TEMPORARY_TABLESPACE as "multi_temporary_tablespace",
PROFILE as "profile",
MULTI_PROFILE as "multi_profile",
AUTHENTICATION_TYPE as "auth_type",
to_char(CREATED, :b_date_fmt) as "created",
case COMMON when 'YES' then '1' else '0' end as "common"))
order by USERNAME))
from (
select USERNAME,
decode(min(ACCOUNT_STATUS), max(ACCOUNT_STATUS), null, '1') as MULTI_STATUS,
min(ACCOUNT_STATUS) as ACCOUNT_STATUS,
case when sum(case when upper(ACCOUNT_STATUS) like '%LOCKED%' then 1 else 0 end) > 0 then '1' else '0' end as PDB_LOCKED,
decode(min(LOCK_DATE), max(LOCK_DATE), null, '1') as MULTI_LOCK_DATE,
max(LOCK_DATE) as LOCK_DATE,
decode(min(LOCK_DATE), max(EXPIRY_DATE), null, '1') as MULTI_EXPIRY_DATE,
max(EXPIRY_DATE) as EXPIRY_DATE,
decode(min(DEFAULT_TABLESPACE), max(DEFAULT_TABLESPACE), null, '1')
as MULTI_DEFAULT_TABLESPACE,
min(DEFAULT_TABLESPACE) as DEFAULT_TABLESPACE,
decode(min(TEMPORARY_TABLESPACE), max(TEMPORARY_TABLESPACE), null, '1') as MULTI_TEMPORARY_TABLESPACE,
min(TEMPORARY_TABLESPACE) as TEMPORARY_TABLESPACE,
decode(min(PROFILE), max(PROFILE), null, '1') as MULTI_PROFILE,
min(PROFILE) as PROFILE,
min(AUTHENTICATION_TYPE) as AUTHENTICATION_TYPE,
min(CREATED) as CREATED,
max(COMMON) as COMMON
from SYS.cdb_users
where COMMON='YES'
group by USERNAME, AUTHENTICATION_TYPE)
where USERNAME=:b_username;]]></script>
 <script><![CDATA[
 var b_username varchar2(128);
  begin
    :b_username := 'AUDSYS';
  end;
  /

select xmlelement("granted_privs", xmlagg(xmlelement("priv",
xmlattributes(
NAME as "name",
case ADMIN_OPTION when 'YES' then '1' else '0' end as "adm",
case DEFAULT_ROLE when 'YES' then '1' else '0' end as "default",
case COMMON       when 'YES' then '1' else '0' end as "common",
IS_ROLE as "is_role"))
order by GRANTEE, NAME))
from (
select GRANTEE,
PRIVILEGE as NAME,
ADMIN_OPTION,
null as DEFAULT_ROLE,
COMMON,
'0' as IS_ROLE
from SYS.dba_sys_privs
UNION ALL
select GRANTEE,
GRANTED_ROLE as NAME,
ADMIN_OPTION,
DEFAULT_ROLE,
COMMON,
'1' as IS_ROLE
from SYS.dba_role_privs
) p where grantee=:b_username
and COMMON='YES';]]></script>
<script><![CDATA[
var b_username varchar2(128);

  begin
    :b_username := 'AUDSYS';
  end;
  /

  select xmlelement("granted_objprivs", xmlagg(xmlelement("obj_priv",
xmlattributes(
OWNER as "schema",
TABLE_NAME as "object",
GRANTOR as "grantor",
PRIVILEGE as "privilege",
case GRANTABLE
when 'YES' then '1'
else '0'
end as "grantable",
case HIERARCHY
when 'YES' then '1'
else '0'
end as "hierarchy",
case COMMON
when 'YES' then '1'
else '0'
end as "common",
TYPE as "type"))
order by OWNER, TYPE, TABLE_NAME))
from SYS.dba_tab_privs
where grantee=:b_username;]]>
 </script>
 <script><![CDATA[
  var b_username varchar2(128);
  begin
    :b_username := 'AUDSYS';
  end;
  /

  select xmlelement("ts_quotas", xmlagg(xmlelement("ts_quota",
xmlattributes(
ts.TABLESPACE_NAME as "tablespace_name",
q.BYTES as "bytes",
q.MAX_BYTES as "max_bytes",
q.BLOCKS as "blocks",
q.MAX_BLOCKS as "max_blocks",
case q.DROPPED
when 'YES' then '1'
else '0'
end as "dropped"
)
)
order by ts.TABLESPACE_NAME
)
)
from SYS.dba_tablespaces ts
left join SYS.dba_ts_quotas q on ts.TABLESPACE_NAME=q.TABLESPACE_NAME
and q.username=:b_username
where ts.CONTENTS='PERMANENT';]]>
 </script>
</report>
 
REPORT_SHOW_USERS_XML
Undocumented prvtemx_admin.report_show_users_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_users_xml(1)
FROM dual;

<report db_version="12.1.0.2.0" elapsed_time="0.01" cpu_time="0.02" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-28800" packs="2">
<report_id><![CDATA[/orarep/security/show_users]]></report_id>
<script><![CDATA[
  var b_date_fmt varchar2(100);
  begin
    :b_date_fmt := dbms_report.date_fmt;
    :b_username := '';
  end;
  /

  select xmlelement("users", xmlagg(xmlelement("user", xmlattributes(USERNAME as "username",
  ACCOUNT_STATUS as "account_status",
  PDB_LOCKED as "pdb_locked",
  MULTI_STATUS as "multi_status",
  to_char(LOCK_DATE, :b_date_fmt) as "lock_date",
  MULTI_LOCK_DATE as "multi_lock_date",
  to_char(EXPIRY_DATE, :b_date_fmt) as "expiry_date",
  MULTI_EXPIRY_DATE as "multi_expiry_date",
  DEFAULT_TABLESPACE as "default_tablespace",
  MULTI_DEFAULT_TABLESPACE as "multi_default_tablespace",
  TEMPORARY_TABLESPACE as "temporary_tablespace",
  MULTI_TEMPORARY_TABLESPACE as "multi_temporary_tablespace",
  PROFILE as "profile",
  MULTI_PROFILE as "multi_profile",
  AUTHENTICATION_TYPE as "auth_type",
  to_char(CREATED, :b_date_fmt) as "created",
  case COMMON when 'YES' then '1' else '0' end as "common" ))
  order by USERNAME))
  from (
    select USERNAME,
    decode(min(ACCOUNT_STATUS), max(ACCOUNT_STATUS), null, '1') as MULTI_STATUS,
    min(ACCOUNT_STATUS) as ACCOUNT_STATUS,
    case when sum(case when upper(ACCOUNT_STATUS) like '%LOCKED%' then 1 else 0 end) > 0 then '1'
    else '0' end as PDB_LOCKED,
    decode(min(LOCK_DATE), max(LOCK_DATE), null, '1') as MULTI_LOCK_DATE,
    max(LOCK_DATE) as LOCK_DATE,
    decode(min(LOCK_DATE), max(EXPIRY_DATE), null, '1') as MULTI_EXPIRY_DATE,
    max(EXPIRY_DATE) as EXPIRY_DATE,
    decode(min(DEFAULT_TABLESPACE), max(DEFAULT_TABLESPACE), null, '1') as MULTI_DEFAULT_TABLESPACE,
    min(DEFAULT_TABLESPACE) as DEFAULT_TABLESPACE,
    decode(min(TEMPORARY_TABLESPACE), max(TEMPORARY_TABLESPACE), null, '1') as MULTI_TEMPORARY_TABLESPACE,
    min(TEMPORARY_TABLESPACE) as TEMPORARY_TABLESPACE,
    decode(min(PROFILE), max(PROFILE), null, '1') as MULTI_PROFILE,
    min(PROFILE) as PROFILE,
    min(AUTHENTICATION_TYPE) as AUTHENTICATION_TYPE,
    min(CREATED) as CREATED,
    max(COMMON) as COMMON
    from SYS.cdb_users
    where COMMON='YES'
    group by USERNAME, AUTHENTICATION_TYPE)
  ;]]></script>
</report>
 
REPORT_TBS_AUTO_EXTEND_XML
Undocumented prvtemx_admin.report_tbs_auto_extend_xml(
p_name        IN VARCHAR2,
p_type        IN VARCHAR2,
p_auto_extend IN NUMBER,
p_next_size   IN VARCHAR2,
p_max_size    IN VARCHAR2,
p_show_sql    IN NUMBER)
RETURN XMLTYPE;
TBD
 
SWITCH_REDO_LOGFILE_XML
Undocumented prvtemx_admin.switch_redo_logfile_xml(
p_show_sql   IN NUMBER,
p_report_ref IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
SWITCH_UNDOTBS_XML
Undocumented prvtemx_admin.switch_undotbs_xml(
p_inst_id    IN NUMBER,
p_tbs_name   IN VARCHAR2,
p_report_ref IN VARCHAR2,
p_show_sql   IN NUMBER)
RETURN XMLTYPE;
TBD

Related Topics
DBMS_REPORT
Packages
PRVTEMX_CELL
PRVTEMX_DBHOME
PRVTEMX_MEMORY
PRVTEMX_PERF
What's New In 12cR1
What's New In 12cR2

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