Oracle | PRVTEMX

Oracle PRVTEMX_SQL Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose Enterprise Manager Express SQL Support Utilities

AUTHID CURRENT_USER

Dependencies

ANYDATA	DBMS_ASSERT	PRVTEMX_ADMIN
DBA_ADVISOR_EXECUTIONS	DBMS_REPORT	PRVT_ADVISOR
DBA_ADVISOR_EXEC_PARAMETERS	DBMS_SCHEDULER	PRVT_REPORT_TAGS
DBA_ADVISOR_OBJECTS	DBMS_SESSION	PRVT_SQLPA
DBA_ADVISOR_PARAMETERS	DBMS_SQLPA	SQL_BINDS
DBA_ADVISOR_TASKS	DBMS_SQLTUNE	SYS_IXMLAGG
DBA_RSRC_CONSUMER_GROUPS	DBMS_STANDARD	USER_ADVISOR_OBJECTS
DBA_SQLSET	DBMS_SYS_ERROR	V$ADVISOR_PROGRESS
DBA_SQLTUNE_BINDS	DBMS_UTILITY	WRI$_REPT_SQLPI
DBA_SQL_PROFILES	DUAL	XMLTYPE
DBMS_ADVISOR

Documented No

First Available 12.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/prvtemp_sql.plb

Subprograms

ALTER_ANALYSIS_TASK_XML	REPORT_ANALYSIS_TASK_INFO_XML	REPORT_SQLSET_LIST_XML
CREATE_ANALYSIS_TASK	REPORT_ANALYSIS_TASK_LIST_XML	SCHEDULE_ANALYSIS_COMPARE
EXECUTE_QUICK_CHECK_TASK	REPORT_QUICK_CHECK_DFLTS_XML	SCHEDULE_ANALYSIS_EXECUTION
REPORT_ANALYSIS_DEFAULTS_XML	REPORT_SET_ANALYSIS_DFLTS_XML	SCHEDULE_QUICK_CHECK_TASK

ALTER_ANALYSIS_TASK_XML

Undocumented

Time invested in finding a valid action type did not produce one prvtemx_sql.alter_analysis_task_xml( task_id IN NUMBER, action_type IN VARCHAR2, show_sql_only IN NUMBER) RETURN XMLTYPE;

SELECT task_id, description

      FROM dba_advisor_tasks

      WHERE execution_end IS NULL

      ORDER BY 1;

      

       TASK_ID DESCRIPTION

      -------- --------------------------

             2 Automatic SPM Evolve Task

             3 Automatic SPM Evolve Task

             4

             5

             7 zonemap task

             9

           493

      

      SELECT prvtemx_sql.alter_analysis_task_xml(2, 'zonemap task', 1);

             *

      ORA-20000: invalid action type

CREATE_ANALYSIS_TASK

Undocumented prvtemx_sql.creative_analysis_task( task_name IN VARCHAR2, description IN VARCHAR2, sts_owner IN VARCHAR2, sts_name IN VARCHAR2, show_sql_only IN NUMBER) RETURN XMLTYPE;

TBD

EXECUTE_QUICK_CHECK_TASK

Undocumented prvtemx_sql.executive_quick_check_task( change_spec IN VARCHAR2, task_name IN VARCHAR2, consumer_group IN VARCHAR2);

TBD

REPORT_ANALYSIS_DEFAULTS_XML

Undocumented prvtemx_sql.report_analysis_defaults_xml RETURN XMLTYPE;

SELECT prvtemx_sql.report_analysis_defaults_xml;

      

      REPORT_ANALYSIS_DEFAULTS_XML

      ----------------------------------------------------------------------------

      <report db_version="23.0.0.0.0" cpu_cores="8" hyperthread="Y" con_id="1"

         con_name="CDB$ROOT" timezone_offset="0" dbtz_offset="0" time_at_dbtz="off" packs="2"

         service_type="0">

        <report_id><![CDATA[/orarep/sqlpa/default_params]]></report_id>

        <spa_defaults disable_multi_exec="FALSE" num_rows_to_fetch="ALL_ROWS"

         local_time_limit="UNUSED" total_time_limit="UNLIMITED" comparison_metric="UNUSED"

         workload_impact_threshold="1" sql_impact_threshold="1" execute_fulldml="FALSE"

         apply_capture_compileenv="0"/>

      </report>

REPORT_ANALYSIS_TASK_INFO_XML

Undocumented

TID = task_id prvtemx_sql.report_analysis_task_info_xml(tid IN NUMBER) RETURN XMLTYPE;

SELECT prvtemx_sql.report_analysis_task_info_xml(2);

      

      
      PRVTEMX_SQL.REPORT_ANALYSIS_TASK_INFO_XML(2)

      ----------------------------------------------------------------------------

      <report db_version="23.0.0.0.0" cpu_cores="8" hyperthread="Y" con_id="1"

         con_name="CDB$ROOT" timezone_offset="0" dbtz_offset="0" time_at_dbtz="off" packs="2"

         service_type="0">

        <report_id><![CDATA[/orarep/sqlpa/task_list]]></report_id>

        <execution_list/>

        <report_list/>

        <task_info task_name="SYS_AUTO_SPM_EVOLVE_TASK" spa_type="UNUSED"

         quick_check_type="UNUSED"/>

      </report>

REPORT_ANALYSIS_TASK_LIST_XML

Undocumented prvtemx_sql.report_analysis_task_list_xml(result_limit IN NUMBER) RETURN XMLTYPE;

SELECT prvtemx_sql.report_analysis_task_list_xml(3);

      

      PRVTEMX_SQL.REPORT_ANALYSIS_TASK_LIST_XML(3)

      ----------------------------------------------------------------------------

      <report db_version="23.0.0.0.0" cpu_cores="8" hyperthread="Y" con_id="1"

         con_name="CDB$ROOT" timezone_offset="0" dbtz_offset="0" time_at_dbtz="off" packs="2"

         service_type="0">

        <report_id><![CDATA[/orarep/sqlpa/task_list%3ftop%3d3]]></report_id>

        <task_list/>

        <session_user>SYS</session_user>

      </report>

REPORT_QUICK_CHECK_DFLTS_XML

Undocumented prvtemx_sql.report_quick_check_dflts_xml RETURN XMLTYPE;

SELECT prvtemx_sql.report_quick_check_dflts_xml;

      

      REPORT_QUICK_CHECK_DFLTS_XML

      ----------------------------------------------------------------------------

      <report db_version="23.0.0.0.0" cpu_cores="8" hyperthread="Y" con_id="1"

         con_name="CDB$ROOT" timezone_offset="0" dbtz_offset="0" time_at_dbtz="off" packs="2"

         service_type="0">

        <report_id><![CDATA[/orarep/sqlpa/default_qc_params]]></report_id>

        <sts_list/>

        <cg_list>

          <cg name="BATCH_GROUP"/>

          <cg name="DEFAULT_CONSUMER_GROUP"/>

          <cg name="DSS_CRITICAL_GROUP"/>

          <cg name="DSS_GROUP"/>

          <cg name="ETL_GROUP"/>

          <cg name="INTERACTIVE_GROUP"/>

          <cg name="LOW_GROUP"/>

          <cg name="ORA$APPQOS_0"/>

          <cg name="ORA$APPQOS_1"/>

          <cg name="ORA$APPQOS_2"/>

          <cg name="ORA$APPQOS_3"/>

          <cg name="ORA$APPQOS_4"/>

          <cg name="ORA$APPQOS_5"/>

          <cg name="ORA$APPQOS_6"/>

          <cg name="ORA$APPQOS_7"/>

          <cg name="ORA$AUTOTASK"/>

          <cg name="SYS_GROUP"/>

        </cg_list>

      </report>

REPORT_SET_ANALYSIS_DFLTS_XML

Undocumented

A DBMS_SQLPA analysis parameter was tried with the result shown prvtemx_sql.report_set_analysis_dflts_xml(spa_defaults IN VARCHAR2);

exec prvtemx_sql.report_set_analysis_dflts_xml('<SQL PERCENTAGE="10">');

          
      *

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00007: unexpected end-of-file encountered

REPORT_SQLSET_LIST_XML

Undocumented prvtemx_sql.report_sqlset_list_xml RETURN XMLTYPE;

 SELECT prvtemx_sql.report_sqlset_list_xml;

      

      REPORT_SQLSET_LIST_XML

      ----------------------------------------------------------------------------

      <report db_version="23.0.0.0.0" cpu_cores="8" hyperthread="Y" con_id="1"

         con_name="CDB$ROOT" timezone_offset="0" dbtz_offset="0" time_at_dbtz="off" packs="2"

         service_type="0">

        <report_id><![CDATA[/orarep/sqlpa/default_qc_params]]></report_id>

        <sts_list>

          <sts_owner="SYS" name="SWAT$AUTO_MV_ADV_STS" statement_count="0"/>

          <sts_owner="SYS" name="SWAT$AUTO_MV_ADV_SURROGATE_STS" statement_count="0"/>

          <sts_owner="SYS" name="SWAT_ARM_STS_VER" statement_count="0"/>

          <sts_owner="SYS" name="SWAT_AUTO_STS" stgatement_count=""0"/>

        </sts_list>

        <default_params>

          <sqlset_owner>

          <sqlset_name>

        </default_params>

      </report>

SCHEDULE_ANALYSIS_COMPARE

Undocumented prvtemx_sql.schedule_analysis_compare( task_id IN NUMBER, task_name IN VARCHAR2, execution_name IN VARCHAR2, description IN VARCHAR2, execution_name1 IN VARCHAR2, execution_name2 IN VARCHAR2, comparison_metric IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE, show _sql_only IN NUMBER) RETURN XMLTYPE;

TBD

SCHEDULE_ANALYSIS_EXECUTION

Undocumented prvtemx_sql.schedule_analysis_execution( task_id IN NUMBER, task_name IN VARCHAR2, exsecution_name IN VARCHAR2, description IN VARCHAR2, execution_type IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE, show_sql_only IN NUMBER) RETURN XMLTYPE;

TBD

SCHEDULE_QUICK_CHECK_TASK

Undocumented prvtemx_sql.schedule_quick_check_task( change_spec IN VARCHAR2, task_name IN VARCHAR2, description IN VARCHAR2, time_limit IN VARCHAR2) RETURN VARCHAR2;

TBD