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
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

Related Topics
Built-in Functions
Built-in Packages
What's New In 19c
What's New In 20c-21c

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
  DBSecWorx