Oracle DBMS_AUTO_INDEX_INTERNAL
Version 19.3

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 Support package for Auto Indexing
AUTHID DEFINER
Dependencies
COL$ DBMS_AUTO_TASK_ADMIN SQL$TEXT
COL_GROUP_USAGE$ DBMS_LOB SQLOBJ$
COL_USAGE$ DBMS_OUTPUT SQLOBJ$AUXDATA
DBA_ADVISOR_EXECUTIONS DBMS_REPAIR SQLSET_ROW
DBA_ADVISOR_FINDINGS DBMS_SMB_INTERNAL TAB$
DBA_ADVISOR_SQLPLANS DBMS_SPD_INTERNAL USER$
DBA_ADVISOR_SQLSTATS DBMS_SPM WRH$_SQLTEXT
DBA_AUTO_INDEX_EXECUTIONS DBMS_SPM_INTERNAL WRI$_ADV_ACTIONS
DBA_AUTO_INDEX_STATISTICS DBMS_SQLDIAG WRI$_ADV_AI_COL_USAGE
DBA_AUTO_INDEX_VERIFICATIONS DBMS_SQLPA WRI$_ADV_EXECUTIONS
DBA_INDEXES DBMS_SQLTUNE WRI$_ADV_FINDINGS
DBA_INDEX_USAGE DBMS_SQLTUNE_INTERNAL WRI$_ADV_MESSAGE_GROUPS
DBA_IND_PARTITIONS DBMS_SQLTUNE_UTIL0 WRI$_ADV_OBJECTS
DBA_IND_SUBPARTITIONS DBMS_STANDARD WRI$_ADV_SQLACCESS_ADV
DBA_OBJECTS DBMS_STATS WRI$_ADV_TASKS
DBA_SEGMENTS DBMS_STATS_INTERNAL WRI$_OPTSTAT_TAB_HISTORY
DBA_SQLSET DBMS_STATS_LIB WRI$_SQLSET_DEFINITIONS
DBA_SQLSET_REFERENCES DBMS_SYS_ERRORL$ WRI$_SQLSET_MASK
DBA_SQLSET_STATEMENTS DBMS_UTILITY WRI$_SQLSET_PLANS
DBA_SQL_PLAN_BASELINES DUAL WRI$_SQLSET_PLAN_LINES
DBA_TABLES ICOL$ WRI$_SQLSET_STATEMENTS
DBA_TABLESPACES MON_MODS_ALL$ WRI$_SQLSET_STATISTICS
DBA_USERS OBJ$ X$MODACT_LENGTH
DBMS_ADVISOR PARTOBJ$ XMLTYPE
DBMS_APPLICATION_INFO PLITBLM XQSEQUENCE
DBMS_ASSERT PRVT_ADVISOR _auto_index_ind_objects
DBMS_AUTO_INDEX PRVT_SQLPA _auto_index_log
DBMS_AUTO_INDEX_LIB SMB$CONFIG  
Documented No
First Available 19c
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsaii.plb
Subprograms
 
AI_CLEANUP (new 19c)
Not sure but the exception may well relate to the fact that AI_CLEAR was run first and there may have been "NO DATA" to clean up. dbms_auto_index_internal.ai_cleanup(CLEANUP_TYPE IN NUMBER);
exec dbms_auto_index_internal.ai_cleanup(1);
BEGIN dbms_auto_index_internal.ai_cleanup(1); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6397
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6894
ORA-06512: at line 1
 
AI_CLEAR (new 19c)
Removes auto indexing jobs from DBA_ADVISOR_LOG and likely elsewhere as well dbms_auto_index_internal.ai_clear;
exec dbms_auto_index_internal.ai_clear;

PL/SQL procedure successfully completed.
 
AI_INIT (new 19c)
Initializes Automatic Indexing dbms_auto_index_internal.ai_init;
exec dbms_auto_index_internal.ai_init;

PL/SQL procedure successfully completed.
 
CAPTURE_STS (new 19c)
Undocumented dbms_auto_index_internal.capture_sts(
sts_own IN VARCHAR2,
sts     IN VARCHAR2);
TBD
 
CHECK_AUTO_INDEX_ENABLED (new 19c)
Presumably will in the future raise an exception when auto indexing is not enable dbms_auto_index_internal.check_auto_index_enabled;
SQL> exec dbms_auto_index_internal.check_auto_index_enabled;
BEGIN dbms_auto_index_internal.check_auto_index_enabled; END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at line 1
 
CONFIGURE (new 19c)
Configure an Auto Indexing parameter dbms_auto_index_internal.configure(
parameter_name  IN VARCHAR2,
parameter_value IN VARCHAR2,
allow           IN BOOLEAN,
allow_internal  IN BOOLEAN);
TBD
 
EXECUTE_TASK (new 19c)
Run an auto indexing task but apparently not the way I tried at right dbms_auto_index_internal.execute_task(
task_id IN  NUMBER,
err     OUT NUMBER);
SQL> SELECT MAX(task_id)
  2  FROM dba_advisor_log;

MAX(TASK_ID)
------------
101

DECLARE
 outVal NUMBER;
BEGIN
  dbms_auto_index_internal.execute_task(5, outVal);
  dbms_output.put_line(outVal);
END;
/
DECLARE
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7247
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7022
ORA-06512: at line 4
 
FINDING_NAME (new 19c)
Returns the finding name corresponding to the finding code dbms_auto_index_internal.finding_name(finding_code IN NUMBER) RETURN VARCHAR2;
SELECT dbms_auto_index_internal.finding_name(1)
FROM dual;

DBMS_AUTO_INDEX_INTERNAL.FINDING_NAME(1)
-----------------------------------------
Compiled statements
 
GEN_INITIAL_KEY_ID (new 19c)
Undocumented dbms_auto_index_internal.gen_iniital_key_id(cols IN VARCHAR2) RETURN VARCHAR2;
TBD
 
GET_LAST_ACTIVITY_TIME (new 19c)
Returns the start and end times of the most recent execution of the Index Advisor task dbms_auto_index_internal.get_last_activity_time(
start_exec_time OUT TIMESTAMP WITH TIME ZONE,
end_exec_time   OUT TIMESTAMP WITH TIME ZONE);
SQL> SELECT task_id, task_name, execution_start, execution_end
  2  FROM dba_advisor_log
  3  WHERE task_name LIKE '%AUTO%INDEX%'
  4* ORDER BY 1;

 TASK_ID TASK_NAME            EXECUTION_START      EXECUTION_END
-------- -------------------- -------------------- --------------------
       5 SYS_AUTO_INDEX_TASK  01-JUL-2019 19:23:45 01-JUL-2019 19:23:45


DECLARE
 stime TIMESTAMP WITH TIME ZONE;
 etime TIMESTAMP WITH TIME ZONE;
BEGIN
  dbms_auto_index_internal.get_last_activity_time(stime, etime);
  dbms_output.put_line(stime);
  dbms_output.put_line(etime);
END;
/
01-JUL-19 07.23.45.000000 PM -05:00
01-JUL-19 07.23.45.000000 PM -05:00


PL/SQL procedure successfully completed.
 
GET_REPORT_LEVELS (new 19c)
Undocumented dbms_auto_index_internal.get_report_levels(report_level IN VARCHAR2) RETURN NUMBER;
SELECT dbms_auto_index_internal.get_report_levels('DETAIL')
FROM dual;

SELECT dbms_auto_index_internal.get_report_levels('DETAIL')
       *
ERROR at line 1:
ORA-13618: The specified value is not a valid value for procedure argument LEVEL.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8498
 
GET_REPORT_SECTIONS (new 19c)
Undocumented dbms_auto_index_internal.get_report_sections(report_section IN VARCHAR2) RETURN NUMBER;
SELECT dbms_auto_index_internal.get_report_sections('HEADER')
FROM dual;

SELECT dbms_auto_index_internal.get_report_sections('HEADER')
       *
ERROR at line 1:
ORA-13618: The specified value is not a valid value for procedure argument SECTION.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8443
 
INSERT_AUTO_INDEX_PARAMETERS (new 19c)
Undocumented dbms_auto_index_internal.insert_auto_index_parameters;
SQL> exec dbms_auto_index_internal.insert_auto_index_parameters;
BEGIN dbms_auto_index_internal.insert_auto_index_parameters; END;
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_SMB$CONFIG_PKEY) violated
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9146
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9146
ORA-06512: at line 1
 
IS_AUTO_INDEX_TASK (new 19c)
Returns TRUE if the task number corresponds to an auto indexing task dbms_auto_index_internal.is_auto_index_task(task_id IN NUMBER) RETURN BOOLEAN;
SQL> SELECT task_id, task_name
  2  FROM dba_advisor_log
  3  ORDER BY task_id;

 TASK_ID    TASK_NAME
-------- ------------------------
       1 SYS_AUTO_SQL_TUNING_TASK
       2 SYS_AUTO_SPM_EVOLVE_TASK
       3 SYS_AI_SPM_EVOLVE_TASK
       4 SYS_AI_VERIFY_TASK
       5 SYS_AUTO_INDEX_TASK
       6 AUTO_STATS_ADVISOR_TASK
       7 INDIVIDUAL_STATS_ADVISOR_TASK

BEGIN
  IF dbms_auto_index_internal.is_auto_index_task(4) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

BEGIN
  IF dbms_auto_index_internal.is_auto_index_task(5) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T

PL/SQL procedure successfully completed.
 
REPORT_ACTIVITY (new 19c)
??? dbms_auto_index_internal.report_activity(
activity_start IN     TIMESTAMP WITH TIME ZONE
activity_end   IN     TIMESTAMP WITH TIME ZONE
section_flags  IN     NUMBER
level_flags    IN     NUMBER
report_xml     IN OUT XMLTYPE);
TBD
 
SET_GLOBAL_AI_TRACE (new 19c)
Undocumented dbms_auto_index_internal.set_global_ai_trace;
exec dbms_auto_index_internal.set_global_ai_trace;

PL/SQL procedure successfully completed.
 
TASK_PROC (new 19c)
Undocumented and apparently also unfinished dbms_auto_index_internal.task_proc(sts_capture IN BOOLEAN);
SQL> exec dbms_auto_index_internal.task_proc(TRUE);
BEGIN dbms_auto_index_internal.task_proc(TRUE); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7349
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6386
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6293
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7283
ORA-06512: at line 1



SQL> exec dbms_auto_index_internal.task_proc(FALSE);
BEGIN dbms_auto_index_internal.task_proc(FALSE); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7349
ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
ORA-06512: at "SYS.PRVT_ADVISOR", line 932
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7247
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7022
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 21
ORA-06512: at "SYS.PRVT_ADVISOR", line 915
ORA-06512: at "SYS.PRVT_ADVISOR", line 3451
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7308
ORA-06512: at line 1
 
Hidden Auto Index Views
The views were specifically named by Oracle with a leading underscore and in lower case to make them difficult to find. Difficult but not impossible. SQL> desc "_auto_index_log"
Name                 Null?    Type
-------------------- -------- -----------------
TASK_ID              NOT NULL NUMBER
EXECUTION_NAME                VARCHAR2(128)
LOG_ID               NOT NULL NUMBER
OBJECT_ID                     NUMBER
FINDING_CODE                  NUMBER
FINDING_NAME                  VARCHAR2(80)
FLAGS                         NUMBER
VC_ARG1                       VARCHAR2(4000)
VC_ARG2                       VARCHAR2(4000)
VC_ARG3                       VARCHAR2(4000)
N_ARG1                        NUMBER
N_ARG2                        NUMBER

SQL> desc "_auto_index_ind_objects"
Name                 Null?    Type
-------------------- -------- -----------------
TASK_ID              NOT NULL NUMBER
OBJECT_ID            NOT NULL NUMBER
INDEX_OWNER                   VARCHAR2(4000)
INDEX_NAME                    VARCHAR2(4000)
TABLE_OWNER                   VARCHAR2(4000)
TABLE_NAME                    VARCHAR2(4000)
COLUMN_LIST                   CLOB
TABLESPACE_NAME               VARCHAR2(4000)
LAST_EXECUTION_NAME           VARCHAR2(4000)
TYPE                          NUMBER
PROPERTY                      NUMBER
INDEX_OBJ#                    NUMBER
FLAGS                         NUMBER
REBUILD_COUNT                 NUMBER
MISESTIMATE_COUNT             NUMBER

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTO_INDEX
DBMS_I_INDEX_UTL
DBMS_INDEX_UTL
DBMS_INDEXING
DBMS_PCLXUTIL
DBMS_SPACE.CREATE_INDEX_COST
What's New In 18c
What's New In 19c

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