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 will be available on generic Linux platforms in January and soon on AIX and Windows

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