Oracle | PRVTEMX

Oracle PRVTEMX_SQL Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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