Oracle | PRVTEMX

Oracle PRVTEMX_SQL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Enterprise Manager Express SQL Support Utilities

AUTHID CURRENT_USER

Dependencies

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

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)

      FROM dual;

      ERROR:

      ORA-20000: invalid action type

      ORA-06512: at "SYS.PRVTEMX_SQL", line 650

      ORA-06512: at "SYS.PRVTEMX_SQL", line 611

      ORA-06512: at line 1

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

      FROM dual;

      

      REPORT_ANALYSIS_DEFAULTS_XML

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

      <report db_version="21.0.0.0.0" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" 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)

      FROM dual;

      

      PRVTEMX_SQL.REPORT_ANALYSIS_TASK_INFO_XML(2)

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

      <report db_version="21.0.0.0.0" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" 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)

      FROM dual;

      

      PRVTEMX_SQL.REPORT_ANALYSIS_TASK_LIST_XML(3)

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

      <report db_version="21.0.0.0.0" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" 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

      FROM dual;

      

      REPORT_QUICK_CHECK_DFLTS_XML

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

      <report db_version="21.0.0.0.0" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">

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

      <sts_list>

      <sts owner="SYS" name="SYS_AUTO_STS" statement_count="5225"/>

      </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">');

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

      *

      ERROR at line 1:

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00007: unexpected end-of-file encountered

      ORA-06512: at "SYS.XMLTYPE", line 310

      ORA-06512: at "SYS.PRVTEMX_SQL", line 2107

      ORA-06512: at line 1

REPORT_SQLSET_LIST_XML

Undocumented prvtemx_sql.report_sqlset_list_xml RETURN XMLTYPE;

 SELECT prvtemx_sql.report_sqlset_list_xml

      FROM dual;

      

      REPORT_SQLSET_LIST_XML

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

      <report db_version="21.0.0.0.0" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">

      <report_id><![CDATA[/orarep/sqlpa/default_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="SYS_AUTO_STS" statement_count="5225"/>

      </sts_list>

      <default_params>

      <sqlset_owner>UNUSED</sqlset_owner>

      <sqlset_name>UNUSED</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