Oracle PRVTEMX_ADMIN
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose Undocumented
AUTHID CURRENT_USER
Dependencies
DBA_ROLES DUAL V$DATABASE
DBMS_ASSERT GV$INSTANCE V$OBJECT_PRIVILEGE
DBMS_REPORT PLITBLM V$PARAMETER
DBMS_SQL PRVTEMX_DBHOME V$PDBS
DBMS_SQLTUNE PRVTEMX_RSRCMGR V$SYSTEM_PARAMETER
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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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
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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/alter_user%3fprofile%3dDEFAULT%26show_sql%3d1%26username%3dAUDSYS]]></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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/create_user%3faccount_lock%3d0%26auth_type%3dPASSWORD
%26default_tablespace%3dUSERS%26passwd_expire%3d0%26profile%3dDEFAULT%26show_sql%3d1%26te
mp_tablespace%3dTEMP%26username%3dC%23%23ABC]]></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
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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/drop_profile%3fcascade%3d0%26profile%3dUWPROFILE%26show_sql%3d1]]></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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/drop_role%3frole%3dCONNECT%26show_sql%3d1]]></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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/drop_user%3fcascade%3d0%26show_sql%3d1%26username%3dSYS]]></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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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(4000);

  -- 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 sys.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_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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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 sys.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_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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/show_privs%3fgranted_only%3d1%26username%3dAUDSYS]]></report_id>
  <script><![CDATA[
    var b_username varchar2(32767);

    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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/show_profiledetail%3fprofile%3dDEFAULT%26show_sql%3d1]]></report_id>
  <script><![CDATA[
    var b_profile varchar2(32767);

    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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/show_profiles%3fshow_sql%3d1]]></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_SHOW_ROLEDETAIL_XML
Undocumented prvtemx_admin.report_show_roledetail_xml(
p_role     IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_roledetail_xml('DBA', 1)
FROM dual;
 
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="18.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" packs="2">
  <report_id><![CDATA[/orarep/security/show_roles%3fshow_sql%3d1]]></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;
SELECT prvtemx_admin.report_show_undo_details_xml(1, SYSDATE-30, SYSDATE-1/24, p_show_sql=>1)
FROM dual;
 
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;
SELECT prvtemx_admin.report_show_undo_summary_xml(1, 1)
FROM dual;
 
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="18.0.0.0.0" elapsed_time="0.03" cpu_time="0.03" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-21600" 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;
SELECT prvtemx_admin.report_show_userdetail_xml('AUDSYS', 1)
FROM dual;
 
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_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
Built-in Functions
Built-in Packages
DBMS_REPORT
PRVTEMX_CELL
PRVTEMX_DBHOME
PRVTEMX_MEMORY
PRVTEMX_PERF
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved