SET ECHO ON TERM ON NUMF "";
SPOOL sqctab.lis;
REM
REM $Header: 215187.1 sqctab.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqctab.sql
REM
REM DESCRIPTION
REM   This script creates sequences, tables, indexes and views owned by schema
REM   SQLTXPLAIN
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected as SQLTXPLAIN
REM
REM PARAMETERS
REM   1. None
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN
REM   3. Execute script sqctab.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus sqltxplan/[pwd]
REM   SQL> start sqctab.sql
REM
REM NOTES
REM   1. This script is executed automatically by sqcreate.sql
REM   2. For possible errors see sqctab.lis file
REM
SET ECHO OFF TERM OFF;
WHENEVER SQLERROR CONTINUE;
ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;
DECLARE
  rdbms_release NUMBER;
BEGIN
  IF USER != 'SQLTXPLAIN' THEN
    RAISE_APPLICATION_ERROR(-20100, 'Install failed - SQLTXPLAIN objects should be created connected as SQLTXPLAIN, not as '||USER);
  END IF;
  SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_release
    FROM v$instance;
  IF rdbms_release < 9.2 THEN
    RAISE_APPLICATION_ERROR(-20200, 'Install failed - SQLTXPLAIN should be installed in 9i(9.2) or higher, not in '||rdbms_release);
  END IF;
END;
/
SET ECHO ON;

/* ------------------------------------------------------------------------- */

CREATE TYPE varchar2_table AS TABLE OF VARCHAR2(2000);
/

CREATE TYPE bind_t IS OBJECT (
  nam VARCHAR2(64),
  pos INTEGER,
  ppo INTEGER,
  dty INTEGER,
  csi INTEGER,
  frm INTEGER,
  pre INTEGER,
  scl INTEGER,
  mxl INTEGER,
  cap VARCHAR2(8),
  val VARCHAR2(2000)
);
/

CREATE TYPE bind_nt AS TABLE OF bind_t;
/

/* ------------------------------------------------------------------------- */

COL start_with NEW_V start_with NOPRI;
SELECT (NVL(MOD(TRUNC(ABS(SYS.DBMS_RANDOM.RANDOM)), 8500), 0) + 1000) start_with FROM DUAL;

CREATE SEQUENCE sqlt$_statement_id_s START WITH &&start_with;

SELECT TO_CHAR(SYSDATE, 'YYMMDDHH24MISS')||'0000' start_with FROM DUAL;

CREATE SEQUENCE sqlt$_pk_id_s START WITH &&start_with;

CREATE SEQUENCE sqlg$_error_id_s;

CREATE SEQUENCE sqlt$_line_id_s;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqli$_tool_parameter (
  id                         INTEGER      NOT NULL,
  hidden                     CHAR(1)      NOT NULL, -- (N)o, (Y)es
  user_updateable            CHAR(1)      NOT NULL, -- (N)o, (Y)es
  name                       VARCHAR2(32) NOT NULL,
  description                VARCHAR2(64) NOT NULL,
  value_type                 CHAR(1)      NOT NULL, -- (N)umber, (C)har
  value                      VARCHAR2(128),
  default_value              VARCHAR2(128),
  low_value                  NUMBER,
  high_value                 NUMBER,
  value1                     VARCHAR2(128),
  value2                     VARCHAR2(128),
  value3                     VARCHAR2(128),
  value4                     VARCHAR2(128),
  value5                     VARCHAR2(128),
  instructions               VARCHAR2(128)
);

CREATE UNIQUE INDEX sqli$_tool_parameter_pk ON sqli$_tool_parameter(id);
ALTER TABLE sqli$_tool_parameter ADD (CONSTRAINT sqli$_tool_parameter_pk PRIMARY KEY (id));

CREATE UNIQUE INDEX sqli$_tool_parameter_u1 ON sqli$_tool_parameter(name);

/* ------------------------------------------------------------------------- */

DECLARE
  PROCEDURE ins (
    p_hidden          CHAR,
    p_user_updateable CHAR,
    p_name            VARCHAR2,
    p_description     VARCHAR2,
    p_value_type      CHAR,
    p_value           VARCHAR2,
    p_default_value   VARCHAR2,
    p_low_value       NUMBER,
    p_high_value      NUMBER,
    p_value1          VARCHAR2,
    p_value2          VARCHAR2,
    p_value3          VARCHAR2,
    p_value4          VARCHAR2,
    p_value5          VARCHAR2,
    p_instructions    VARCHAR2 )
  IS
  BEGIN
    INSERT INTO sqli$_tool_parameter VALUES (
      sqlt$_pk_id_s.NEXTVAL,
      p_hidden,
      p_user_updateable,
      p_name,
      p_description,
      p_value_type,
      p_value,
      p_default_value,
      p_low_value,
      p_high_value,
      p_value1,
      p_value2,
      p_value3,
      p_value4,
      p_value5,
      p_instructions );
  END;
BEGIN
  DELETE sqli$_tool_parameter;

  --  hid  upd  name                         description                       typ  value                         default    low   high       val1  val2  val3  val4  val5  instructions
  --  ~~~  ~~~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~   ~~~~~ ~~~~~~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~~~~~~~~
  ins('Y', 'N', 'note_number',               'Note Number',                    'C', '215187.1',                   NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, NULL);
  ins('Y', 'N', 'tool_owner',                'Tool Schema Owner',              'C', 'SQLTXPLAIN',                 NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, NULL);
  ins('Y', 'N', 'tool_name',                 'Tool Name',                      'C', 'SQLT',                       NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, NULL);
  ins('Y', 'N', 'tool_version',              'Tool Version',                   'C', '11.2.9.8',                   NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, NULL);
  ins('Y', 'N', 'interop_version',           'Inter Operability Version',      'N', '1',                          '1',       1,    9999,      NULL, NULL, NULL, NULL, NULL, '1-9999');
  ins('Y', 'N', 'install_date',              'Tool Install Date',              'C', TO_CHAR(SYSDATE, 'YYYYMMDD'), NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'YYYYMMDD');
  ins('Y', 'N', 'refresh_date',              'Refresh Date',                   'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'YYYYMMDD');
  ins('Y', 'N', 'refresh_days',              'Refresh Days',                   'N', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, NULL);

  ins('Y', 'N', 'host_name_short',           'Host Short Name',                'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'v$instance.host');
  ins('Y', 'N', 'cpu_count',                 'CPU Count',                      'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'v$parameter2.cpu_count');
  ins('Y', 'N', 'platform',                  'Platform',                       'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'product_component_version.product');
  ins('Y', 'N', 'product_version',           'Product Version',                'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'product_component_version.product.status');
  ins('Y', 'N', 'rdbms_version',             'RDBMS Version',                  'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'v$instance.version');
  ins('Y', 'N', 'rdbms_version_short',       'RDBMS Version Short',            'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'rdbms_version');
  ins('Y', 'N', 'rdbms_release',             'RDBMS Release',                  'N', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'rdbms_version_short');
  ins('Y', 'N', 'language',                  'Language',                       'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'sys_context.userenv.lang.language');
  ins('Y', 'N', 'database_id',               'Database ID',                    'N', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'v$database.id');
  ins('Y', 'N', 'database_name_short',       'Database Short Name',            'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'v$database.name');
  ins('Y', 'N', 'apps_release',              'APPS Release',                   'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'fnd_product_groups.release_name');
  ins('Y', 'N', 'apps_system_name',          'APPS System Name',               'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'fnd_product_groups.apps_system_name');
  ins('Y', 'N', 'apps_multi_org_flag',       'APPS MultiOrg Flag',             'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'fnd_product_groups.apps_multi_org_flag');

  ins('N', 'Y', 'skip_predicates_from_plan', 'Skip Predicates from Plan',      'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  'E',  NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_count_star',           'Skip COUNT(*)',                  'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'count_star_th',             'COUNT(*) Threshold',             'N', '10000000',                   '10000000',   0, 999999999, NULL, NULL, NULL, NULL, NULL, '10000000: 0-999999999');
  ins('N', 'Y', 'skip_segment_stats',        'Skip Segment Stats',             'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_session_stats',        'Skip Session Stats',             'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_session_wait_events',  'Skip Session Wait Events',       'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_constraints',          'Skip Constraints',               'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_column_stats',         'Skip Column Stats',              'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_dba_statistics_views', 'Skip DBA Statistics Views',      'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_histograms',           'Skip Histograms',                'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_histograms_history',   'Skip Histograms History',        'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y'); -- 11.2.9.6
  ins('N', 'Y', 'skip_partitions',           'Skip Partitions',                'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_subpartitions',        'Skip Subpartitions',             'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N');
  ins('N', 'Y', 'skip_part_column_stats',    'Skip Partition Column Stats',    'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N');
  ins('N', 'Y', 'skip_subpart_column_stats', 'Skip Subpartition Column Stats', 'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N');
  ins('N', 'Y', 'skip_storage',              'Skip Storage',                   'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_metadata',             'Skip Metadata',                  'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_init_ora',             'Skip Initialization Parameters', 'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_stored_outlines',      'Skip Stored Outlines',           'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_tuning_advisor',       'Skip Tuning Advisor',            'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_join_order',           'Skip Join Order',                'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y'); -- 11.2.9.7
  ins('N', 'Y', 'skip_10053_trace',          'Skip 10053 Trace',               'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y'); -- 11.2.9.8
  ins('N', 'Y', 'skip_10046_trace',          'Skip 10046 Trace',               'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y'); -- 11.2.9.8
  ins('N', 'Y', 'skip_other_traces',         'Skip Other Traces',              'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y'); -- 11.2.9.8
  ins('N', 'Y', 'dbms_sqltune_scope',        'DBMS_SQLTUNE Scope',             'C', 'COMPREHENSIVE',              'COMPREHENSIVE', NULL, NULL,'COMPREHENSIVE', 'LIMITED', NULL, NULL, NULL, 'COMPREHENSIVE, LIMITED');
  ins('N', 'Y', 'dbms_sqltune_time_limit',   'DBMS_SQLTUNE Time Limit Secs',   'N', '1800',                       '1800',      30,86400,      NULL, NULL, NULL, NULL, NULL, '1800: 30-86400');
  ins('N', 'Y', 'dbms_sqltune_report_level', 'DBMS_SQLTUNE Report Level',      'C', 'ALL',                        'ALL',     NULL, NULL,      'ALL', 'TYPICAL', 'BASIC', NULL, NULL, 'ALL, TYPICAL, BASIC');
  ins('N', 'Y', 'skip_awr_access',           'Skip AWR access',                'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_sql_monitor',          'Skip SQL Monitor',               'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'skip_test_case_builder',    'Skip Test Case Builder',         'C', 'N',                          'N',       NULL, NULL,      'N',  'Y',  NULL, NULL, NULL, 'N, Y');
  ins('N', 'Y', 'dbms_sqldiag_time_limit',   'DBMS_SQLDIAG Time Limit Secs',   'N', '1800',                       '1800',      30,86400,      NULL, NULL, NULL, NULL, NULL, '1800: 30-86400');
  ins('N', 'Y', 'days_show_cbo_stats',       'Days to report CBO Stats',       'N', '365',                        '365',        0, 9999,      NULL, NULL, NULL, NULL, NULL, '365: 0-9999');
  ins('N', 'Y', 'restrict_user',             'Restrict User',                  'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N');
  ins('N', 'Y', 'gather_cbo_stats',          'Gather CBO Stats Staging Objs',  'N', '100',                        '100',        0,  100,      NULL, NULL, NULL, NULL, NULL, '0-100');
  ins('N', 'Y', 'use_gv$sql_views',          'Use GV$SQL RAC Views',           'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N');
  ins('N', 'Y', 'linux_statistics_level',    'Statistics Level for Linux',     'C', 'TYPICAL',                    'TYPICAL', NULL, NULL,      'TYPICAL', 'ALL', 'BASIC', NULL, NULL, 'TYPICAL, ALL, BASIC');
  ins('N', 'Y', 'max_child_cursor_list',     'Max Num Child Cursors to List',  'N', '100',                        '100',        0, 999999999, NULL, NULL, NULL, NULL, NULL, '100: 0-999999999');
  ins('N', 'Y', 'create_file_hist',          'Create File History',            'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N');
  ins('N', 'Y', 'index_compare_by_columns',  'Index Compare by Columns',       'C', 'Y',                          'Y',       NULL, NULL,      'Y',  'N',  NULL, NULL, NULL, 'Y, N'); -- 11.2.9.6
  ins('N', 'Y', 'title_repeat_rate',         'Repeat Rate for Titles',         'N', '30',                         '30',        10, 1000,      NULL, NULL, NULL, NULL, NULL, '30: 10-1000'); -- 11.2.9.7

  ins('Y', 'N', 'trace_dir',                 'Trace Directory',                'C', 'SQLT$UDUMP',                 'UDUMP',   NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'UDUMP');
  ins('N', 'N', 'stage_dir',                 'Stage Directory',                'C', 'SQLT$STAGE',                 'UDUMP',   NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'Set by sqlt/install/sqcdirs.sql');
  ins('Y', 'N', 'input_directory',           'Input Directory Path',           'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'SQLT$UDUMP');
  ins('Y', 'N', 'output_directory',          'Output Directory Path',          'C', NULL,                         NULL,      NULL, NULL,      NULL, NULL, NULL, NULL, NULL, 'SQLT$STAGE');

  COMMIT;
END;
/

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_statement
( statement_id                 INTEGER NOT NULL,
  statid                       VARCHAR2(30) NOT NULL, -- 10.0
  sqlt_date                    DATE, -- 11.2.9
  method                       VARCHAR2(30), -- 10.7
  script                       VARCHAR2(64), -- 10.7
  execution_id                 INTEGER, -- 11.1 deprecated 11.2.8
  input_filename               VARCHAR2(128),
  /* Hidden parameters */
  host_name_short              VARCHAR2(128),
  cpu_count                    INTEGER,
  rac                          VARCHAR2(16), -- 11.2.3
  database_id                  INTEGER,
  database_name_short          VARCHAR2(128),
  instance_number              INTEGER,
  instance_name_short          VARCHAR2(128),
  platform                     VARCHAR2(256),
  product_version              VARCHAR2(256),
  rdbms_version                VARCHAR2(32),
  rdbms_version_short          VARCHAR2(32),
  rdbms_release                NUMBER,  -- 11.1
  language                     VARCHAR2(256),
  apps_release                 VARCHAR2(64),
  apps_system_name             VARCHAR2(32),
  apps_multi_org_flag          CHAR(1),
  /* SQL identification */
  hash_value                   NUMBER,
  inst_id                      NUMBER, -- 10.7
  address                      VARCHAR2(16),
  child_number                 NUMBER, -- 10.0
  sql_id                       VARCHAR2(13), -- 10.0
  signature                    VARCHAR2(64), -- 10.7 Stored Outlines
  stored_outline_name          VARCHAR2(32), -- 11.2.9
  signaturen                   NUMBER, -- 11.2.6 SQL Profile
  sql_profile_name             VARCHAR2(32), -- 11.2.9
  command_type                 NUMBER, -- 10.0
  plan_source                  VARCHAR2(30), -- 11.2.8 'DBA_HIST_SQLSTAT', 'GV$SQL', 'V$SQL', 'EXPLAIN PLAN FOR'
  plan_hash_value              NUMBER, -- 10.0
  sqlt_plan_hash_value         NUMBER, -- 10.7.4
  /* SQL Text */
  sql_source                   VARCHAR2(30), -- 11.2.5 'V$SQL', 'GV$SQL', 'DBA_HIST_SQLSTAT', 'FILE', 'UNKNOWN'
  sql_length                   INTEGER,
  sql_text                     VARCHAR2(4000),
  sql_text_clob                CLOB, -- 10.7
  sql_text_clob_stripped       CLOB, -- 11.2.9
  /* Plan */
  explained                    CHAR(1),
  explain_plan_error           VARCHAR2(4000),
  generated_10053              CHAR(1), -- 10.0
  rows_filled                  CHAR(1), -- 11.0.3
  cost                         NUMBER, -- 10.7.4
  cpu_cost                     NUMBER, -- 10.7.4
  io_cost                      NUMBER, -- 10.7.4
  cardinality                  NUMBER, -- 10.7.4
  bytes                        NUMBER, -- 10.7.4
  estimated_secs               NUMBER, -- 11.2.3
  actual_secs                  NUMBER, -- 11.2.3
  /* Audit Trail */
  user_id                      INTEGER,
  username                     VARCHAR2(30),
  temporary_tablespace         VARCHAR2(30), -- 10.2
  tool_start_date              DATE,
  tool_end_date                DATE,
  cbo_stats_schema_objects     INTEGER, -- 11.2.9.1
  cbo_stats_fixed_objects      INTEGER, -- 11.2.9.1
  fixed_objects_in_plan        INTEGER, -- 11.2.9.1
  /* DBMS_STATS Parameters */
  param_autostats_target       VARCHAR2(256), -- 11.2.9.6
  param_publish                VARCHAR2(256), -- 11.2.9.6
  param_incremental            VARCHAR2(256), -- 11.2.9.6
  param_stale_percent          VARCHAR2(256), -- 11.2.9.6
  param_estimate_percent       VARCHAR2(256), -- 10.3
  param_degree                 VARCHAR2(256), -- 10.3
  param_cascade                VARCHAR2(256), -- 10.3
  param_no_invalidate          VARCHAR2(256), -- 10.3
  param_method_opt             VARCHAR2(256), -- 10.3
  param_granularity            VARCHAR2(256), -- 10.3
  to_estimate_percent_type     NUMBER, -- 10.3
  to_degree_type               NUMBER, -- 10.3
  to_cascade_type              VARCHAR2(8), -- 10.3
  to_no_invalidate_type        VARCHAR2(8), -- 10.3
  /* DBA_SCHEDULER_JOBS GATHER_STATS_JOB 10g */
  job_name                     VARCHAR2(32), -- 10.3
  job_program_name             VARCHAR2(4000), -- 10.3
  job_schedule_name            VARCHAR2(4000), -- 10.3
  -- job_schedule_type            VARCHAR2(16),  -- 10.3 deprecated 10.7.2
  job_class                    VARCHAR2(32), -- 10.3
  job_enabled                  VARCHAR2(8), -- 10.3
  job_state                    VARCHAR2(16), -- 10.3
  job_priority                 NUMBER, -- 10.3
  job_run_count                NUMBER, -- 10.3
  job_retry_count              NUMBER, -- 10.3
  job_last_start_date          VARCHAR2(256), -- 10.3
  job_last_run_duration        VARCHAR2(256), -- 10.3
  job_stop_on_window_close     VARCHAR2(8), -- 10.3
  /* DBA_AUTOTASK_CLIENT "auto optimizer stats collection" 11g */
  client_name                  VARCHAR2(64), -- 11.2.9.6
  status                       VARCHAR2(8), -- 11.2.9.6
  consumer_group               VARCHAR2(30), -- 11.2.9.6
  client_tag                   VARCHAR2(2), -- 11.2.9.6
  priority_override            VARCHAR2(7), -- 11.2.9.6
  attributes                   VARCHAR2(4000), -- 11.2.9.6
  window_group                 VARCHAR2(64), -- 11.2.9.6
  service_name                 VARCHAR2(64), -- 11.2.9.6
  resource_percentage          NUMBER, -- 11.2.9.6
  use_resource_estimates       VARCHAR2(5), -- 11.2.9.6
  mean_job_duration            INTERVAL DAY(9) TO SECOND(9), -- 11.2.9.6
  mean_job_cpu                 INTERVAL DAY(9) TO SECOND(9), -- 11.2.9.6
  mean_job_attempts            NUMBER, -- 11.2.9.6
  mean_incoming_tasks_7_days   NUMBER, -- 11.2.9.6
  mean_incoming_tasks_30_days  NUMBER, -- 11.2.9.6
  total_cpu_last_7_days        INTERVAL DAY(9) TO SECOND(9), -- 11.2.9.6
  total_cpu_last_30_days       INTERVAL DAY(9) TO SECOND(9), -- 11.2.9.6
  max_duration_last_7_days     INTERVAL DAY(3) TO SECOND(0), -- 11.2.9.6
  max_duration_last_30_days    INTERVAL DAY(3) TO SECOND(0), -- 11.2.9.6
  window_duration_last_7_days  INTERVAL DAY(9) TO SECOND(9), -- 11.2.9.6
  window_duration_last_30_days INTERVAL DAY(9) TO SECOND(9), -- 11.2.9.6
 /* important CBO init.ora params */
  optimizer_features_enable    VARCHAR2(32), -- 11.2.4
  db_block_size                NUMBER, -- 11.1.5
  /* System Statistics */
  cpuspeednw                   NUMBER, -- 11.2.4
  cpuspeed                     NUMBER, -- 11.1.5
  ioseektim                    NUMBER, -- 11.1.5
  iotfrspeed                   NUMBER, -- 11.1.5
  mbrc                         NUMBER, -- 11.1.5
  sreadtim                     NUMBER, -- 11.1.5
  mreadtim                     NUMBER, -- 11.1.5
  maxthr                       NUMBER, -- 11.1.5
  slavethr                     NUMBER, -- 11.1.5
  cpu_cost_scaling_factor      NUMBER, -- 11.1.5
  /* File Names */
  file_sqlt_main               VARCHAR2(256), -- 11.2.0
  file_sqlt_frames             VARCHAR2(256), -- 11.2.0
  file_sqlt_metadata           VARCHAR2(256), -- 11.2.0
  file_sqlt_setenv             VARCHAR2(256), -- 11.2.4
  file_sqlt_readme             VARCHAR2(256), -- 11.2.4
  file_sqlt_trace_udump        VARCHAR2(256), -- 11.2.1
  file_sqlt_trace              VARCHAR2(256), -- 11.2.0
  file_sqlt_lite               VARCHAR2(256), -- 11.2.0
  file_sqlt_tcscript           VARCHAR2(256), -- 11.2.9
  file_sqlt_tcsql              VARCHAR2(256), -- 11.2.9
  file_sqlt_tcbuilder          VARCHAR2(256), -- 11.2.9
  file_sqlt_xplore_script      VARCHAR2(256), -- 11.2.9.5
  file_sqlt_xplore_report      VARCHAR2(256), -- 11.2.9.5
  file_trcanlzr_html           VARCHAR2(256), -- 11.2.0
  file_trcanlzr_txt            VARCHAR2(256), -- 11.2.0
  file_trcanlzr_log            VARCHAR2(256) -- 11.2.0
);

CREATE UNIQUE INDEX sqlt$_statement_pk ON sqlt$_statement(statement_id);
ALTER TABLE sqlt$_statement ADD (CONSTRAINT sqlt$_statement_pk PRIMARY KEY (statement_id));

GRANT SELECT, UPDATE ON sqlt$_statement TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_statement_tune (
  statement_id               VARCHAR2(30),
  report_text                CLOB,
  script                     CLOB
) ON COMMIT PRESERVE ROWS;

CREATE UNIQUE INDEX sqlg$_statement_tune_pk ON sqlg$_statement_tune(statement_id);
ALTER TABLE sqlg$_statement_tune ADD (CONSTRAINT sqlg$_statement_tune_pk PRIMARY KEY (statement_id));

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_error (
  error_id                   INTEGER NOT NULL,
  error_text                 VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

GRANT SELECT ON sqlg$_error TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_plan_table (
  statement_id               VARCHAR2(30) NOT NULL,
  plan_id                    NUMBER,
  timestamp                  DATE,
  remarks                    VARCHAR2(4000),
  operation                  VARCHAR2(30),
  options                    VARCHAR2(255),
  object_node                VARCHAR2(128),
  object_owner               VARCHAR2(30),
  object_name                VARCHAR2(30),
  object_alias               VARCHAR2(65),
  object_instance            NUMBER,
  object_type                VARCHAR2(30),
  optimizer                  VARCHAR2(255),
  search_columns             NUMBER,
  id                         NUMBER NOT NULL,
  parent_id                  NUMBER,
  depth                      NUMBER,
  position                   NUMBER,
  cost                       NUMBER,
  cardinality                NUMBER,
  bytes                      NUMBER,
  other_tag                  VARCHAR2(255),
  partition_start            VARCHAR2(255),
  partition_stop             VARCHAR2(255),
  partition_id               NUMBER,
  other                      CLOB,
  distribution               VARCHAR2(30),
  cpu_cost                   NUMBER,
  io_cost                    NUMBER,
  temp_space                 NUMBER,
  access_predicates          VARCHAR2(4000),
  filter_predicates          VARCHAR2(4000),
  projection                 VARCHAR2(4000),
  time                       NUMBER,
  qblock_name                VARCHAR2(30),
  other_xml                  CLOB, -- 10.0
  object#                    NUMBER, -- 10.0
  execution_order            NUMBER,
  indent                     NUMBER,
  last_output_rows           NUMBER, -- 11.0.3
  output_rows                NUMBER, -- 11.0.3
  last_starts                NUMBER, -- 11.2.7
  starts                     NUMBER, -- 11.2.7
  statid                     VARCHAR2(30) DEFAULT 'SQLT' -- 11.2.9
);

CREATE UNIQUE INDEX sqlt$_plan_table_pk ON sqlt$_plan_table(statement_id, id);
ALTER TABLE sqlt$_plan_table ADD (CONSTRAINT sqlt$_plan_table_pk PRIMARY KEY (statement_id, id));

CREATE INDEX sqlt$_plan_table_n1 ON sqlt$_plan_table(statement_id, parent_id);

GRANT SELECT, INSERT, DELETE ON sqlt$_plan_table TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_sql (
  statement_id               INTEGER NOT NULL,
  plan_hash_value            NUMBER,
  child_count                NUMBER,
  child_number               NUMBER,
  inst_id                    NUMBER,
  hash_value                 NUMBER,
  old_hash_value             NUMBER, -- 11.2.9.6
  type_chk_heap              VARCHAR2(16), -- 11.2.9.6
  address                    VARCHAR2(16),
  child_address              VARCHAR2(16),
  child_latch                NUMBER, -- 11.2.9.6
  sql_id                     VARCHAR2(13),
  command_type               NUMBER, -- 11.2.9.6
  sqltype                    NUMBER, -- 11.2.9.6
  remote                     VARCHAR2(1), -- 11.2.9.6
  object_status              VARCHAR2(19),
  literal_hash_value         NUMBER,
  optimizer_cost             NUMBER,
  optimizer_mode             VARCHAR2(10),
  optimizer_env_hash_value   NUMBER, -- 11.2.9.6
  cpu_time                   NUMBER,
  elapsed_time               NUMBER,
  application_wait_time      NUMBER,
  concurrency_wait_time      NUMBER,
  cluster_wait_time          NUMBER,
  user_io_wait_time          NUMBER,
  plsql_exec_time            NUMBER,
  java_exec_time             NUMBER,
  rows_processed             NUMBER,
  parse_calls                NUMBER,
  fetches                    NUMBER,
  end_of_fetch_count         NUMBER,
  executions                 NUMBER,
  px_servers_executions      NUMBER, -- 11.2.9.6
  buffer_gets                NUMBER,
  disk_reads                 NUMBER,
  direct_writes              NUMBER,
  sorts                      NUMBER,
  sharable_mem               NUMBER,
  persistent_mem             NUMBER,
  runtime_mem                NUMBER,
  service                    VARCHAR2(64), -- 11.2.9.6
  service_hash               NUMBER, -- 11.2.9.6
  module                     VARCHAR2(64),
  module_hash                NUMBER, -- 11.2.9.6
  action                     VARCHAR2(64),
  action_hash                NUMBER, -- 11.2.9.6
  program_id                 NUMBER, -- 11.2.9.6
  program_line#              NUMBER, -- 11.2.9.6
  serializable_aborts        NUMBER, -- 11.2.9.6
  outline_sid                NUMBER,
  outline_category           VARCHAR2(64),
  exact_matching_signature   NUMBER, -- 11.2.9.6
  force_matching_signature   NUMBER, -- 11.2.9.6
  sql_profile                VARCHAR2(64),
  sql_patch                  VARCHAR2(30), -- 11.2.9.6
  sql_plan_baseline          VARCHAR2(30), -- 11.2.9.6
  parsing_schema_id          NUMBER,
  parsing_schema_name        VARCHAR2(30), -- 11.2.9.6
  parsing_user_id            NUMBER,
  loaded_versions            NUMBER,
  open_versions              NUMBER,
  users_opening              NUMBER,
  users_executing            NUMBER,
  loads                      NUMBER,
  invalidations              NUMBER,
  kept_versions              NUMBER,
  first_load_time            VARCHAR2(19),
  last_load_time             VARCHAR2(19),
  last_active_time           DATE, -- 11.2.9.6
  is_obsolete                VARCHAR2(1), -- 11.2.9.6
  is_bind_sensitive          VARCHAR2(1), -- 11.2.9.6
  is_bind_aware              VARCHAR2(1), -- 11.2.9.6
  is_shareable               VARCHAR2(1), -- 11.2.9.6
  io_cell_offload_eligible_bytes NUMBER, -- 11.2.9.6
  io_interconnect_bytes      NUMBER, -- 11.2.9.6
  io_disk_bytes              NUMBER, -- 11.2.9.6
  typecheck_mem              NUMBER, -- 11.2.9.6
  most_expensive_child       CHAR(1)
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_sql_n1 ON sqlg$_sql(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_gv$sql_shared_cursor (
  statement_id               INTEGER NOT NULL,
  inst_id                    NUMBER,
  parent_address             VARCHAR2(16),
  child_address              VARCHAR2(16),
  child_number               NUMBER,
  column_name                VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_gv$sql_shared_cursor_n1 ON sqlg$_gv$sql_shared_cursor(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_sql_plan ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  1 plan_hash_value,
  1 child_count,
  inst_id,
  child_number,
  id,
  depth,
  operation,
  options,
  object_owner,
  object_name,
  RPAD('A', 30) object_type,
  object_node,
  cardinality,
  cost,
  search_columns,
  access_predicates,
  filter_predicates,
  object#,
  TO_CLOB(NULL) other_xml
FROM gv$sql_plan
WHERE 1 = 0;

CREATE INDEX sqlg$_sql_plan_n1 ON sqlg$_sql_plan(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_peeked_binds (
  statement_id               INTEGER NOT NULL,
  plan_hash_value            NUMBER,
  source                     VARCHAR2(32), -- V$, GV$, AWR, SP
  child_number               NUMBER,
  inst_id                    NUMBER,
  nam                        VARCHAR2(64),
  pos                        INTEGER,
  ppo                        INTEGER,
  dty                        INTEGER, -- 1:VARCHAR2, 2:NUMBER, 12:DATE, 96:CHAR, 112:CLOB
  csi                        INTEGER,
  frm                        INTEGER,
  pre                        INTEGER,
  scl                        INTEGER,
  mxl                        INTEGER,
  cap                        VARCHAR2(8),
  val                        VARCHAR2(2000)
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_peeked_binds_n1 ON sqlg$_peeked_binds(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_gv$sql_bind_capture (
  statement_id               INTEGER NOT NULL,
  plan_hash_value            NUMBER,
  inst_id                    INTEGER,
  address                    VARCHAR2(16),
  hash_value                 NUMBER,
  sql_id                     VARCHAR2(13),
  child_address              VARCHAR2(16),
  child_number               NUMBER,
  name                       VARCHAR2(90),
  position                   NUMBER,
  dup_position               NUMBER,
  datatype                   NUMBER,
  datatype_string            VARCHAR2(45),
  character_sid              NUMBER,
  precision                  NUMBER,
  scale                      NUMBER,
  max_length                 NUMBER,
  was_captured               VARCHAR2(3),
  last_captured              DATE,
  value_string               VARCHAR2(4000),
  value_anydata              SYS.ANYDATA
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_gv$sql_bind_capture_n1 ON sqlg$_gv$sql_bind_capture(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_sql_plan_statistics ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  1 plan_hash_value,
  1 child_count,
  inst_id,
  child_number,
  operation_id,
  executions,
  last_starts,
  starts,
  last_output_rows,
  output_rows,
  last_cr_buffer_gets,
  cr_buffer_gets,
  last_cu_buffer_gets,
  cu_buffer_gets,
  last_disk_reads,
  disk_reads,
  last_disk_writes,
  disk_writes,
  last_elapsed_time,
  elapsed_time
FROM gv$sql_plan_statistics
WHERE 1 = 0;

CREATE INDEX sqlg$_sql_plan_statistics_n1 ON sqlg$_sql_plan_statistics(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_sql_workarea ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  1 plan_hash_value,
  1 child_count,
  inst_id,
  child_number,
  workarea_address,
  operation_type,
  operation_id,
  policy,
  estimated_optimal_size,
  estimated_onepass_size,
  last_memory_used,
  last_execution,
  last_degree,
  total_executions,
  optimal_executions,
  onepass_executions,
  multipasses_executions,
  active_time,
  max_tempseg_size,
  last_tempseg_size
FROM gv$sql_workarea
WHERE 1 = 0;

CREATE INDEX sqlg$_sql_workarea_n1 ON sqlg$_sql_workarea(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_sql_monitor (
  statement_id               INTEGER NOT NULL,
  inst_id                    NUMBER,
  key                        NUMBER,
  status                     VARCHAR2(19),
  first_refresh_time         DATE,
  last_refresh_time          DATE,
  refresh_count              NUMBER,
  sid                        NUMBER,
  process_name               VARCHAR2(5),
  sql_id                     VARCHAR2(13),
  sql_exec_start             DATE,
  sql_exec_id                NUMBER,
  sql_plan_hash_value        NUMBER,
  sql_child_address          VARCHAR2(16),
  session_serial#            NUMBER,
  px_server#                 NUMBER,
  px_server_group            NUMBER,
  px_server_set              NUMBER,
  px_qcinst_id               NUMBER,
  px_qcsid                   NUMBER,
  elapsed_time               NUMBER,
  cpu_time                   NUMBER,
  fetches                    NUMBER,
  buffer_gets                NUMBER,
  disk_reads                 NUMBER,
  direct_writes              NUMBER,
  application_wait_time      NUMBER,
  concurrency_wait_time      NUMBER,
  cluster_wait_time          NUMBER,
  user_io_wait_time          NUMBER,
  plsql_exec_time            NUMBER,
  java_exec_time             NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_sql_monitor_n1 ON sqlg$_sql_monitor(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_sql_plan_monitor (
  statement_id               INTEGER NOT NULL,
  inst_id                    NUMBER,
  key                        NUMBER,
  status                     VARCHAR2(19),
  first_refresh_time         DATE,
  last_refresh_time          DATE,
  first_change_time          DATE,
  last_change_time           DATE,
  refresh_count              NUMBER,
  sid                        NUMBER,
  process_name               VARCHAR2(5),
  sql_id                     VARCHAR2(13),
  sql_exec_start             DATE,
  sql_exec_id                NUMBER,
  sql_plan_hash_value        NUMBER,
  sql_child_address          VARCHAR2(16),
  plan_line_id               NUMBER,
  plan_operation             VARCHAR2(30),
  plan_options               VARCHAR2(30),
  starts                     NUMBER,
  output_rows                NUMBER,
  workarea_mem               NUMBER,
  workarea_max_mem           NUMBER,
  workarea_tempseg           NUMBER,
  workarea_max_tempseg       NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_sql_plan_monitor_n1 ON sqlg$_sql_plan_monitor(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_object_dependency (
  statement_id               INTEGER NOT NULL,
  id                         INTEGER,
  inst_id                    INTEGER,
  to_owner                   VARCHAR2(64),
  to_name                    VARCHAR2(1000),
  to_address                 VARCHAR2(16),
  to_hash                    INTEGER,
  to_type                    INTEGER,
  type                       VARCHAR2(64),
  source                     VARCHAR2(32),
  by_owner                   VARCHAR2(64),
  by_name                    VARCHAR2(1000),
  by_address                 VARCHAR2(16),
  by_hash                    INTEGER,
  object_id                  INTEGER,
  script_version             VARCHAR2(128),
  metadata                   CLOB,
  metadata_transformed       CLOB,
  metadata_cleaned           CLOB
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_object_dependency_n1 ON sqlg$_object_dependency(statement_id, to_owner, to_name);

GRANT SELECT, UPDATE ON sqlg$_object_dependency TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_objects (
  statement_id               INTEGER NOT NULL,
  owner                      VARCHAR2(30),
  object_name                VARCHAR2(128),
  subobject_name             VARCHAR2(128),
  object_id                  NUMBER,
  data_object_id             NUMBER,
  object_type_id             INTEGER,
  object_type                VARCHAR2(32),
  created                    DATE,
  last_ddl_time              DATE,
  timestamp                  VARCHAR2(32),
  status                     VARCHAR2(8),
  temporary                  VARCHAR2(1),
  generated                  VARCHAR2(1),
  secondary                  VARCHAR2(1),
  segment_blocks             INTEGER,
  extents                    INTEGER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_objects_n1 ON sqlg$_objects(statement_id);

/* ------------------------------------------------------------------------- */

CREATE OR REPLACE VIEW sqlt$_dba_segments (
  owner,
  segment_name,
  partition_name,
  segment_type,
  blocks,
  extents
) AS
SELECT
  NVL(u.name, 'SYS') owner,
  o.name segment_name,
  o.subname partition_name,
  so.object_type segment_type,
  DECODE(BITAND(NVL(s.spare1,0), 131072), 131072, s.blocks,
  (DECODE(BITAND(NVL(s.spare1,0),1),1,
  sys.dbms_space_admin.segment_number_blocks(s.ts#, s.file#,
  s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
  o.dataobj#, s.blocks), s.blocks))) blocks,
  DECODE(BITAND(NVL(s.spare1,0), 131072), 131072, extents,
  (DECODE(BITAND(NVL(s.spare1,0),1),1,
  sys.dbms_space_admin.segment_number_extents(s.ts#, s.file#,
  s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
  o.dataobj#, s.extents) , s.extents))) extents
FROM
  sys.seg$        s,
  sys.sys_objects so,
  sys.obj$        o,
  sys.user$       u
WHERE bitand(NVL(s.spare1,0), 65536) = 0
  AND s.file#           = so.header_file
  AND s.block#          = so.header_block
  AND s.ts#             = so.ts_number
  AND s.type#           = so.segment_type_id
  AND s.ts#               = so.ts_number
  AND so.object_id      = o.obj#
  AND so.object_type_id = o.type#
  AND o.owner#          = u.user#(+);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqli$_segments AS
SELECT owner,
       segment_name,
       partition_name,
       segment_type,
       blocks,
       extents
  FROM sqlt$_dba_segments
 WHERE 1 = 0;

CREATE INDEX sqli$_segments_n1 ON sqli$_segments(owner, segment_name, segment_type);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_segment_statistics (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  id                         INTEGER NOT NULL, -- sid
  begin_end_flag             CHAR(1) NOT NULL, -- (B)egin, (E)nd
  statement_id               INTEGER,          -- unknown at insert time
  statid                     VARCHAR2(30),     -- 11.2.9
  inst_id                    NUMBER,
  owner                      VARCHAR2(30),
  object_name                VARCHAR2(128),
  subobject_name             VARCHAR2(128),
  tablespace_name            VARCHAR2(30),
  ts#                        NUMBER,
  obj#                       NUMBER,
  dataobj#                   NUMBER,
  object_type                VARCHAR2(32),
  statistic_name             VARCHAR2(128),
  statistic#                 NUMBER,
  value                      NUMBER
);

CREATE UNIQUE INDEX sqlt$_segment_statistics_pk ON sqlt$_segment_statistics(pk_id);
ALTER TABLE sqlt$_segment_statistics ADD (CONSTRAINT sqlt$_segment_statistics_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_segment_statistics_n1 ON sqlt$_segment_statistics(id);
CREATE INDEX sqlt$_segment_statistics_n2 ON sqlt$_segment_statistics(statement_id);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_sesstat (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  id                         INTEGER NOT NULL, -- sid
  begin_end_flag             CHAR(1) NOT NULL, -- (B)egin, (E)nd
  statement_id               INTEGER,          -- unknown at insert time
  statid                     VARCHAR2(30),     -- 11.2.9
  statistic#                 NUMBER,
  value                      NUMBER
);

CREATE UNIQUE INDEX sqlt$_sesstat_pk ON sqlt$_sesstat(pk_id);
ALTER TABLE sqlt$_sesstat ADD (CONSTRAINT sqlt$_sesstat_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_sesstat_n1 ON sqlt$_sesstat(id);
CREATE INDEX sqlt$_sesstat_n2 ON sqlt$_sesstat(statement_id);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_session_event (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  id                         INTEGER NOT NULL, -- sid
  begin_end_flag             CHAR(1) NOT NULL, -- (B)egin, (E)nd
  statement_id               INTEGER,          -- unknown at insert time
  statid                     VARCHAR2(30),     -- 11.2.9
  event                      VARCHAR2(64),
  total_waits                NUMBER,
  total_timeouts             NUMBER,
  time_waited                NUMBER,
  average_wait               NUMBER,
  max_wait                   NUMBER,
  time_waited_micro          NUMBER
);

CREATE UNIQUE INDEX sqlt$_session_event_pk ON sqlt$_session_event(pk_id);
ALTER TABLE sqlt$_session_event ADD (CONSTRAINT sqlt$_session_event_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_session_event_n1 ON sqlt$_session_event(id);
CREATE INDEX sqlt$_session_event_n2 ON sqlt$_session_event(statement_id);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_tables AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  owner,
  table_name,
  tablespace_name,
  ini_trans,
  max_trans,
  freelists,
  freelist_groups,
  logging,
  num_rows,
  blocks,
  empty_blocks,
  avg_space,
  chain_cnt,
  avg_row_len,
  degree,
  cache,
  sample_size,
  last_analyzed,
  partitioned,
  iot_type,
  temporary,
  global_stats,
  user_stats -- 11.2.9.6
FROM dba_tables
WHERE 1 = 0;

ALTER TABLE sqlt$_tables ADD (nested VARCHAR2(3));
ALTER TABLE sqlt$_tables ADD (duration VARCHAR2(15));
ALTER TABLE sqlt$_tables ADD (monitoring VARCHAR2(3));
ALTER TABLE sqlt$_tables ADD (data_stats_locked VARCHAR2(3));
ALTER TABLE sqlt$_tables ADD (cache_stats_locked VARCHAR2(3));
ALTER TABLE sqlt$_tables ADD (inserts INTEGER);
ALTER TABLE sqlt$_tables ADD (updates INTEGER);
ALTER TABLE sqlt$_tables ADD (deletes INTEGER);
ALTER TABLE sqlt$_tables ADD (timestamp DATE);
ALTER TABLE sqlt$_tables ADD (dependencies VARCHAR2(8));
ALTER TABLE sqlt$_tables ADD (compression VARCHAR2(8));
ALTER TABLE sqlt$_tables ADD (object_id INTEGER);
ALTER TABLE sqlt$_tables ADD (segment_blocks INTEGER);
ALTER TABLE sqlt$_tables ADD (count_star INTEGER);
ALTER TABLE sqlt$_tables ADD (depth INTEGER);
ALTER TABLE sqlt$_tables ADD (metadata CLOB);
ALTER TABLE sqlt$_tables ADD (metadata_transformed CLOB);
ALTER TABLE sqlt$_tables ADD (metadata_cleaned CLOB);
-- 11.2.9.6
ALTER TABLE sqlt$_tables ADD (cols_mutating_num_buckets INTEGER);

CREATE UNIQUE INDEX sqlt$_tables_pk ON sqlt$_tables(statid, owner, table_name);
ALTER TABLE sqlt$_tables ADD (CONSTRAINT sqlt$_tables_pk PRIMARY KEY (statid, owner, table_name));

CREATE INDEX sqlt$_tables_n1 ON sqlt$_tables(statement_id, owner, table_name);

GRANT SELECT, UPDATE ON sqlt$_tables TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_tab_statistics -- Fixed Objects, Tables, Partitions, Subpartitions
( statement_id               INTEGER      NOT NULL,
  statid                     VARCHAR2(30) NOT NULL,
  owner                      VARCHAR2(30) NOT NULL,
  table_name                 VARCHAR2(30) NOT NULL,
  partition_name             VARCHAR2(30),
  partition_position         NUMBER,
  subpartition_name          VARCHAR2(30),
  subpartition_position      NUMBER,
  object_type                VARCHAR2(12),
  num_rows                   NUMBER,
  blocks                     NUMBER,
  empty_blocks               NUMBER,
  avg_space                  NUMBER,
  chain_cnt                  NUMBER,
  avg_row_len                NUMBER,
  avg_space_freelist_blocks  NUMBER,
  num_freelist_blocks        NUMBER,
  avg_cached_blocks          NUMBER,
  avg_cache_hit_ratio        NUMBER,
  sample_size                NUMBER,
  last_analyzed              DATE,
  global_stats               VARCHAR2(3),
  user_stats                 VARCHAR2(3),
  stattype_locked            VARCHAR2(5),
  stale_stats                VARCHAR2(3)
);

CREATE INDEX sqlt$_tab_statistics_n1 ON sqlt$_tab_statistics(statid, owner, table_name);

CREATE INDEX sqlt$_tab_statistics_n2 ON sqlt$_tab_statistics(statement_id, owner, table_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_indexes AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  owner,
  index_name,
  tablespace_name,
  index_type,
  table_owner,
  table_name,
  uniqueness,
  ini_trans,
  max_trans,
  freelists,
  freelist_groups,
  logging,
  blevel,
  leaf_blocks,
  distinct_keys,
  avg_leaf_blocks_per_key,
  avg_data_blocks_per_key,
  clustering_factor,
  status,
  num_rows,
  sample_size,
  last_analyzed,
  degree,
  partitioned,
  temporary,
  global_stats,
  user_stats, -- 11.2.9.6
  domidx_status,
  funcidx_status
FROM dba_indexes
WHERE 1 = 0;

ALTER TABLE sqlt$_indexes ADD (object_id INTEGER);
ALTER TABLE sqlt$_indexes ADD (segment_blocks INTEGER);
-- max_ix_sel = ((#Blks / MBRC) * (mreadtim / sreadtim) - LVLS)/(#LB + CLUF)
ALTER TABLE sqlt$_indexes ADD (max_ix_sel NUMBER);
ALTER TABLE sqlt$_indexes ADD (columns_count INTEGER);
ALTER TABLE sqlt$_indexes ADD (leading_col_id INTEGER);
ALTER TABLE sqlt$_indexes ADD (indexed_col_ids VARCHAR2(4000));
ALTER TABLE sqlt$_indexes ADD (indexed_col_ids2 VARCHAR2(4000));
ALTER TABLE sqlt$_indexes ADD (indexed_columns VARCHAR2(4000));
ALTER TABLE sqlt$_indexes ADD (in_operations VARCHAR2(4000));
ALTER TABLE sqlt$_indexes ADD (metadata CLOB);
ALTER TABLE sqlt$_indexes ADD (metadata_transformed CLOB);
ALTER TABLE sqlt$_indexes ADD (metadata_cleaned CLOB);

CREATE UNIQUE INDEX sqlt$_indexes_pk ON sqlt$_indexes(statid, owner, index_name);
ALTER TABLE sqlt$_indexes ADD (CONSTRAINT sqlt$_indexes_pk PRIMARY KEY (statid, owner, index_name));

CREATE INDEX sqlt$_indexes_n1 ON sqlt$_indexes(statement_id, owner, index_name);

GRANT SELECT, UPDATE ON sqlt$_indexes TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_ind_statistics -- Indexes, Partitions, Subpartitions
( statement_id               INTEGER      NOT NULL,
  statid                     VARCHAR2(30) NOT NULL,
  owner                      VARCHAR2(30) NOT NULL,
  index_name                 VARCHAR2(30) NOT NULL,
  table_owner                VARCHAR2(30) NOT NULL,
  table_name                 VARCHAR2(30) NOT NULL,
  partition_name             VARCHAR2(30),
  partition_position         NUMBER,
  subpartition_name          VARCHAR2(30),
  subpartition_position      NUMBER,
  object_type                VARCHAR2(12),
  blevel                     NUMBER,
  leaf_blocks                NUMBER,
  distinct_keys              NUMBER,
  avg_leaf_blocks_per_key    NUMBER,
  avg_data_blocks_per_key    NUMBER,
  clustering_factor          NUMBER,
  num_rows                   NUMBER,
  avg_cached_blocks          NUMBER,
  avg_cache_hit_ratio        NUMBER,
  sample_size                NUMBER,
  last_analyzed              DATE,
  global_stats               VARCHAR2(3),
  user_stats                 VARCHAR2(3),
  stattype_locked            VARCHAR2(5),
  stale_stats                VARCHAR2(3)
);

CREATE INDEX sqlt$_ind_statistics_n1 ON sqlt$_ind_statistics(statid, owner, index_name);

CREATE INDEX sqlt$_ind_statistics_n2 ON sqlt$_ind_statistics(statement_id, owner, index_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_constraints AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  owner,
  constraint_name,
  constraint_type,
  table_name,
  TO_CLOB(NULL) search_condition,
  r_owner,
  r_constraint_name,
  delete_rule,
  status,
  deferrable,
  deferred,
  validated,
  generated,
  bad,
  rely,
  last_change,
  index_owner,
  index_name,
  invalid,
  view_related
FROM dba_constraints
WHERE 1 = 0;

CREATE UNIQUE INDEX sqlt$_constraints_pk ON sqlt$_constraints(statid, owner, constraint_name);
ALTER TABLE sqlt$_constraints ADD (CONSTRAINT sqlt$_constraints_pk PRIMARY KEY (statid, owner, constraint_name));

CREATE INDEX sqlt$_constraints_n1 ON sqlt$_constraints(statement_id, owner, constraint_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_tab_columns AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  owner,
  table_name,
  column_name,
  column_id,
  data_type,
  data_type_mod,
  data_type_owner,
  data_length,
  data_precision,
  data_scale,
  nullable,
  character_set_name,
  char_length,
  char_used,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats,
  num_nulls,
  num_distinct,
  density,
  avg_col_len,
  low_value,
  high_value,
  num_buckets
FROM dba_tab_columns
WHERE 1 = 0;

ALTER TABLE sqlt$_tab_columns ADD (index_count INTEGER);
ALTER TABLE sqlt$_tab_columns ADD (leading_index_count INTEGER);
ALTER TABLE sqlt$_tab_columns ADD (hidden_column VARCHAR2(3));
ALTER TABLE sqlt$_tab_columns ADD (histogram VARCHAR2(16));
ALTER TABLE sqlt$_tab_columns ADD (qualified_col_name VARCHAR2(4000));
ALTER TABLE sqlt$_tab_columns ADD (data_default VARCHAR2(4000));
ALTER TABLE sqlt$_tab_columns ADD (low_value_boiled VARCHAR2(128));
ALTER TABLE sqlt$_tab_columns ADD (high_value_boiled VARCHAR2(128));
ALTER TABLE sqlt$_tab_columns ADD (low_endpoint_number NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (high_endpoint_number NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (popular_value_count INTEGER);
ALTER TABLE sqlt$_tab_columns ADD (buckets_popular_value INTEGER);
ALTER TABLE sqlt$_tab_columns ADD (new_density NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (equality_preds NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (equijoin_preds NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (nonequijoin_preds NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (range_preds NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (like_preds NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (null_preds NUMBER);
ALTER TABLE sqlt$_tab_columns ADD (timestamp DATE);
ALTER TABLE sqlt$_tab_columns ADD (predicates NUMBER);
-- 11.2.9.6
ALTER TABLE sqlt$_tab_columns ADD (mutating_num_buckets VARCHAR2(3));
-- 11.2.9.6
ALTER TABLE sqlt$_tab_columns ADD (buckets_history VARCHAR2(4000));

CREATE UNIQUE INDEX sqlt$_tab_columns_pk ON sqlt$_tab_columns(statid, owner, table_name, column_name);
ALTER TABLE sqlt$_tab_columns ADD (CONSTRAINT sqlt$_tab_columns_pk PRIMARY KEY (statid, owner, table_name, column_name));

CREATE INDEX sqlt$_tab_columns_n1 ON sqlt$_tab_columns(statement_id, owner, table_name, column_name);

GRANT SELECT ON sqlt$_tab_columns TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_tab_col_statistics -- Fixed Objects
( statement_id               INTEGER      NOT NULL,
  statid                     VARCHAR2(30) NOT NULL,
  owner                      VARCHAR2(30) NOT NULL,
  table_name                 VARCHAR2(30) NOT NULL,
  column_name                VARCHAR2(30) NOT NULL,
  num_distinct               NUMBER,
  low_value                  RAW(32),
  high_value                 RAW(32),
  density                    NUMBER,
  num_nulls                  NUMBER,
  num_buckets                NUMBER,
  last_analyzed              DATE,
  sample_size                NUMBER,
  global_stats               VARCHAR2(3),
  user_stats                 VARCHAR2(3),
  avg_col_len                NUMBER,
  histogram                  VARCHAR2(15)
);

CREATE INDEX sqlt$_tab_col_statistics_n1 ON sqlt$_tab_col_statistics(statid, owner, table_name, column_name);

CREATE INDEX sqlt$_tab_col_statistics_n2 ON sqlt$_tab_col_statistics(statement_id, owner, table_name, column_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_ind_columns AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  index_owner,
  index_name,
  table_owner,
  table_name,
  column_name,
  column_position,
  column_length,
  char_length,
  descend
FROM dba_ind_columns
WHERE 1 = 0;

CREATE UNIQUE INDEX sqlt$_ind_columns_pk ON sqlt$_ind_columns(statid, index_owner, index_name, column_position);
ALTER TABLE sqlt$_ind_columns ADD (CONSTRAINT sqlt$_ind_columns_pk PRIMARY KEY (statid, index_owner, index_name, column_position));

CREATE INDEX sqlt$_ind_columns_n1 ON sqlt$_ind_columns(statement_id, index_owner, index_name, column_position);

GRANT SELECT ON sqlt$_ind_columns TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_histogram_cols (
  statement_id               INTEGER NOT NULL,
  application_id             INTEGER NOT NULL,
  table_name                 VARCHAR2(30) NOT NULL,
  column_name                VARCHAR2(30) NOT NULL,
  partition                  VARCHAR2(30),
  hsize                      INTEGER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_histogram_cols_n1 ON sqlg$_histogram_cols(statement_id, table_name, column_name);

GRANT INSERT ON sqlg$_histogram_cols TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_tab_histograms ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  owner,
  table_name,
  column_name,
  endpoint_number,
  endpoint_value,
  endpoint_actual_value
FROM dba_tab_histograms
WHERE 1 = 0;

CREATE INDEX sqlg$_dba_tab_histograms_n1 ON sqlg$_dba_tab_histograms(statement_id, owner, table_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_tab_partitions AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  table_owner,
  table_name,
  composite,
  partition_name,
  tablespace_name,
  subpartition_count,
  partition_position,
  ini_trans,
  max_trans,
  freelists,
  freelist_groups,
  logging,
  num_rows,
  blocks,
  empty_blocks,
  avg_space,
  chain_cnt,
  avg_row_len,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats -- 11.2.9.6
FROM dba_tab_partitions
WHERE 1 = 0;

ALTER TABLE sqlt$_tab_partitions ADD (object_id INTEGER);
ALTER TABLE sqlt$_tab_partitions ADD (segment_blocks INTEGER);
ALTER TABLE sqlt$_tab_partitions ADD (data_stats_locked VARCHAR2(3));
ALTER TABLE sqlt$_tab_partitions ADD (cache_stats_locked VARCHAR2(3));
ALTER TABLE sqlt$_tab_partitions ADD (inserts INTEGER);
ALTER TABLE sqlt$_tab_partitions ADD (updates INTEGER);
ALTER TABLE sqlt$_tab_partitions ADD (deletes INTEGER);
ALTER TABLE sqlt$_tab_partitions ADD (timestamp DATE);

CREATE UNIQUE INDEX sqlt$_tab_partitions_pk ON sqlt$_tab_partitions(statid, table_owner, table_name, partition_name);
ALTER TABLE sqlt$_tab_partitions ADD (CONSTRAINT sqlt$_tab_partitions_pk PRIMARY KEY (statid, table_owner, table_name, partition_name));

CREATE INDEX sqlt$_tab_partitions_n1 ON sqlt$_tab_partitions(statement_id, table_owner, table_name, partition_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_ind_partitions AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  index_owner,
  index_name,
  composite,
  partition_name,
  tablespace_name,
  subpartition_count,
  partition_position,
  status,
  ini_trans,
  max_trans,
  freelists,
  freelist_groups,
  logging,
  blevel,
  leaf_blocks,
  distinct_keys,
  avg_leaf_blocks_per_key,
  avg_data_blocks_per_key,
  clustering_factor,
  num_rows,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats -- 11.2.9.6
FROM dba_ind_partitions
WHERE 1 = 0;

ALTER TABLE sqlt$_ind_partitions ADD (object_id INTEGER);
ALTER TABLE sqlt$_ind_partitions ADD (segment_blocks INTEGER);

CREATE UNIQUE INDEX sqlt$_ind_partitions_pk ON sqlt$_ind_partitions(statid, index_owner, index_name, partition_name);
ALTER TABLE sqlt$_ind_partitions ADD (CONSTRAINT sqlt$_ind_partitions_pk PRIMARY KEY (statid, index_owner, index_name, partition_name));

CREATE UNIQUE INDEX sqlt$_ind_partitions_n1 ON sqlt$_ind_partitions(statement_id, index_owner, index_name, partition_name);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_tab_part_columns ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  owner,
  table_name,
  partition_name,
  column_name,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats,
  num_nulls,
  num_distinct,
  density,
  avg_col_len,
  low_value,
  high_value,
  num_buckets
FROM dba_part_col_statistics
WHERE 1 = 0;

ALTER TABLE sqlg$_tab_part_columns ADD (column_id NUMBER);
ALTER TABLE sqlg$_tab_part_columns ADD (data_type VARCHAR2(128));
ALTER TABLE sqlg$_tab_part_columns ADD (histogram VARCHAR2(16));
ALTER TABLE sqlg$_tab_part_columns ADD (low_value_boiled VARCHAR2(128));
ALTER TABLE sqlg$_tab_part_columns ADD (high_value_boiled VARCHAR2(128));
ALTER TABLE sqlg$_tab_part_columns ADD (low_endpoint_number NUMBER);
ALTER TABLE sqlg$_tab_part_columns ADD (high_endpoint_number NUMBER);

CREATE INDEX sqlg$_tab_part_columns_n1 ON sqlg$_tab_part_columns(statement_id, owner, table_name, partition_name);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_part_histograms ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  owner,
  table_name,
  partition_name,
  column_name,
  bucket_number,
  endpoint_value,
  endpoint_actual_value
FROM dba_part_histograms
WHERE 1 = 0;

CREATE INDEX sqlg$_dba_part_histograms_n1 ON sqlg$_dba_part_histograms(statement_id, owner, table_name, partition_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_tab_subpartitions AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  table_owner,
  table_name,
  partition_name,
  subpartition_name,
  tablespace_name,
  subpartition_position,
  ini_trans,
  max_trans,
  freelists,
  freelist_groups,
  logging,
  num_rows,
  blocks,
  empty_blocks,
  avg_space,
  chain_cnt,
  avg_row_len,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats -- 11.2.9.6
FROM dba_tab_subpartitions
WHERE 1 = 0;

ALTER TABLE sqlt$_tab_subpartitions ADD (object_id INTEGER);
ALTER TABLE sqlt$_tab_subpartitions ADD (segment_blocks INTEGER);
ALTER TABLE sqlt$_tab_subpartitions ADD (data_stats_locked VARCHAR2(3));
ALTER TABLE sqlt$_tab_subpartitions ADD (cache_stats_locked VARCHAR2(3));
ALTER TABLE sqlt$_tab_subpartitions ADD (inserts INTEGER);
ALTER TABLE sqlt$_tab_subpartitions ADD (updates INTEGER);
ALTER TABLE sqlt$_tab_subpartitions ADD (deletes INTEGER);
ALTER TABLE sqlt$_tab_subpartitions ADD (timestamp DATE);

CREATE UNIQUE INDEX sqlt$_tab_subpartitions_pk ON sqlt$_tab_subpartitions(statid, table_owner, table_name, partition_name, subpartition_name);
ALTER TABLE sqlt$_tab_subpartitions ADD (CONSTRAINT sqlt$_tab_subpartitions_pk PRIMARY KEY (statid, table_owner, table_name, partition_name, subpartition_name));

CREATE INDEX sqlt$_tab_subpartitions_n1 ON sqlt$_tab_subpartitions(statement_id, table_owner, table_name, partition_name, subpartition_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_ind_subpartitions AS
SELECT
  1 statement_id,
  RPAD('A', 30) statid,
  index_owner,
  index_name,
  partition_name,
  subpartition_name,
  tablespace_name,
  subpartition_position,
  status,
  ini_trans,
  max_trans,
  freelists,
  freelist_groups,
  logging,
  blevel,
  leaf_blocks,
  distinct_keys,
  avg_leaf_blocks_per_key,
  avg_data_blocks_per_key,
  clustering_factor,
  num_rows,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats -- 11.2.9.6
FROM dba_ind_subpartitions
WHERE 1 = 0;

ALTER TABLE sqlt$_ind_subpartitions ADD (object_id INTEGER);
ALTER TABLE sqlt$_ind_subpartitions ADD (segment_blocks INTEGER);

CREATE UNIQUE INDEX sqlt$_ind_subpartitions_pk ON sqlt$_ind_subpartitions(statid, index_owner, index_name, partition_name, subpartition_name);
ALTER TABLE sqlt$_ind_subpartitions ADD (CONSTRAINT sqlt$_ind_subpartitions_pk PRIMARY KEY (statid, index_owner, index_name, partition_name, subpartition_name));

CREATE INDEX sqlt$_ind_subpartitions_n1 ON sqlt$_ind_subpartitions(statement_id, index_owner, index_name, partition_name, subpartition_name);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_tab_subpart_columns ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  owner,
  table_name,
  subpartition_name,
  column_name,
  sample_size,
  last_analyzed,
  global_stats,
  user_stats,
  num_nulls,
  num_distinct,
  density,
  avg_col_len,
  low_value,
  high_value,
  num_buckets
FROM dba_subpart_col_statistics
WHERE 1 = 0;

ALTER TABLE sqlg$_tab_subpart_columns ADD (column_id NUMBER);
ALTER TABLE sqlg$_tab_subpart_columns ADD (data_type VARCHAR2(128));
ALTER TABLE sqlg$_tab_subpart_columns ADD (histogram VARCHAR2(16));
ALTER TABLE sqlg$_tab_subpart_columns ADD (low_value_boiled VARCHAR2(128));
ALTER TABLE sqlg$_tab_subpart_columns ADD (high_value_boiled VARCHAR2(128));
ALTER TABLE sqlg$_tab_subpart_columns ADD (low_endpoint_number NUMBER);
ALTER TABLE sqlg$_tab_subpart_columns ADD (high_endpoint_number NUMBER);

CREATE INDEX sqlg$_tab_subpart_columns_n1 ON sqlg$_tab_subpart_columns(statement_id, owner, table_name, subpartition_name);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_subpart_histograms ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  owner,
  table_name,
  subpartition_name,
  column_name,
  bucket_number,
  endpoint_value,
  endpoint_actual_value
FROM dba_subpart_histograms
WHERE 1 = 0;

CREATE INDEX sqlg$_dba_subpart_hist_n1 ON sqlg$_dba_subpart_histograms(statement_id, owner, table_name, subpartition_name);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_parameter2 ( -- 10.6.3
  statement_id               VARCHAR2(30) NOT NULL,
  source                     VARCHAR2(32) NOT NULL,
  this_instance              CHAR(1) NOT NULL,
  inst_id                    INTEGER NOT NULL,
  num                        INTEGER NOT NULL,
  ordinal                    INTEGER NOT NULL,
  name                       VARCHAR2(80) NOT NULL,
  type                       INTEGER,
  value                      VARCHAR2(512),
  display_value              VARCHAR2(512),
  is_default                 VARCHAR2(16),
  is_system_modifiable       VARCHAR2(16),
  is_instance_modifiable     VARCHAR2(16),
  is_session_modifiable      VARCHAR2(16),
  is_modified                VARCHAR2(16),
  is_adjusted                VARCHAR2(16),
  is_deprecated              VARCHAR2(16),
  description                VARCHAR2(256),
  child_number               INTEGER, -- deprecated on 11.2.4
  optimizer_feature_enable   VARCHAR2(32), -- 11.2.4
  statid                     VARCHAR2(30) -- 11.2.9
);

CREATE INDEX sqlt$_parameter2_pk ON sqlt$_parameter2 (statement_id, source, inst_id, num, ordinal, name);
ALTER TABLE sqlt$_parameter2 ADD (CONSTRAINT sqlt$_parameter2_pk PRIMARY KEY (statement_id, source, inst_id, num, ordinal, name));

CREATE INDEX sqlt$_parameter2_n2 ON sqlt$_parameter2 (statement_id, source, name);

GRANT SELECT ON sqlt$_parameter2 TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqli$_parameter_apps (
  release                    VARCHAR2(64) NOT NULL,
  version                    VARCHAR2(32) NOT NULL,
  id                         INTEGER NOT NULL,
  name                       VARCHAR2(128) NOT NULL,
  set_flag                   CHAR(1) NOT NULL,
  mp_flag                    CHAR(1) NOT NULL,
  sz_flag                    CHAR(1) NOT NULL,
  value                      VARCHAR2(512)
);

CREATE UNIQUE INDEX sqli$_parameter_apps_pk ON sqli$_parameter_apps(name);
ALTER TABLE sqli$_parameter_apps ADD (CONSTRAINT sqli$_parameter_apps_pk PRIMARY KEY (name));

/* ------------------------------------------------------------------------- */

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE (
    ownname  => USER,
    stattab  => 'sqlt$_stattab' );
END;
/

CREATE INDEX sqlt$_stattab_n1 ON sqlt$_stattab (statid, type, c5, c1, c4);

GRANT SELECT ON sqlt$_stattab TO PUBLIC;

/* ------------------------------------------------------------------------- */

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE (
    ownname  => USER,
    stattab  => 'sqli$_stattab_temp' );
END;
/

CREATE INDEX sqli$_stattab_temp_n1 ON sqli$_stattab_temp (statid, type, c5, c1, c4);

GRANT SELECT ON sqli$_stattab_temp TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_warning (
  statement_id               INTEGER NOT NULL,
  importance                 NUMBER NOT NULL,
  subimportance              NUMBER NOT NULL,
  object_type                VARCHAR2(30) NOT NULL,
  object_name                VARCHAR2(4000) NOT NULL,
  warning                    VARCHAR2(4000) NOT NULL
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_warning_n1 ON sqlg$_warning(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_tablespaces ON COMMIT PRESERVE ROWS AS
SELECT
  1 statement_id,
  tablespace_name,
  block_size,
  initial_extent,
  next_extent,
  min_extents,
  max_extents,
  pct_increase,
  min_extlen,
  status,
  contents,
  logging,
  force_logging,
  extent_management,
  allocation_type,
  plugged_in,
  segment_space_management,
  def_tab_compression
FROM dba_tablespaces
WHERE 1 = 0;

ALTER TABLE sqlg$_tablespaces ADD (retention VARCHAR2(16));
ALTER TABLE sqlg$_tablespaces ADD (bigfile VARCHAR2(3));

CREATE UNIQUE INDEX sqlg$_tablespaces_pk ON sqlg$_tablespaces(statement_id, tablespace_name);
ALTER TABLE sqlg$_tablespaces ADD (CONSTRAINT sqlg$_tablespaces_pk PRIMARY KEY (statement_id, tablespace_name));

/* ------------------------------------------------------------------------- */

DECLARE
  rdbms_version VARCHAR2(32);
  rdbms_release NUMBER;
  new_columns0 VARCHAR2(32767);
  new_columns1 VARCHAR2(32767);
  new_columns2 VARCHAR2(32767);
  new_columns3 VARCHAR2(32767);
  new_columns4 VARCHAR2(32767);
  new_columns5 VARCHAR2(32767);
  new_columns6 VARCHAR2(32767);
BEGIN
  SELECT SUBSTR(version, 1, 32), TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_version, rdbms_release
    FROM v$instance;

  IF rdbms_release >= 10 THEN
    new_columns0 := NULL;
    IF rdbms_release = 10.1 THEN
      --new_columns1 := 'TO_CHAR(NULL) object_type, TO_CLOB(NULL) other_xml, ';
      new_columns1 := 'TO_CLOB(NULL) other_xml, '; -- 10.1.0.3 includes object_type
    ELSE
      new_columns1 := NULL;
    END IF;
    new_columns2 := NULL;
    new_columns3 := NULL;
    new_columns4 := NULL;
    new_columns5 := NULL;
    new_columns6 := 'prev_sql_id, prev_child_number, ';
  ELSE -- 9i
    new_columns0 := 'TO_CHAR(NULL) sql_id, '; -- 11.2.9.6
    new_columns1 := 'TO_CHAR(NULL) sql_id, TO_CHAR(NULL) object_type, TO_CLOB(NULL) other_xml, ';
    new_columns2 := 'TO_CHAR(NULL) sql_id, ';
    new_columns3 := 'TO_CHAR(NULL) histogram, ';
    new_columns4 := 'TO_CHAR(NULL) histogram, TO_CHAR(NULL) qualified_col_name, ';
    new_columns5 := 'TO_CHAR(NULL) display_value, TO_CHAR(NULL) isinstance_modifiable, TO_CHAR(NULL) isdeprecated, ';
    new_columns6 := 'TO_CHAR(NULL) prev_sql_id, TO_NUMBER(NULL) prev_child_number, ';
  END IF;

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$sql AS SELECT '||new_columns0||'v.* FROM gv$sql v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$sql_plan AS SELECT '||new_columns1||'v.* FROM gv$sql_plan v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$sql_plan_statistics AS SELECT '||new_columns2||'v.* FROM gv$sql_plan_statistics v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$sql_workarea AS SELECT '||new_columns2||'v.* FROM gv$sql_workarea v';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_v$sql AS SELECT '||new_columns0||'v.* FROM v$sql v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_v$sql_plan AS SELECT '||new_columns1||'v.* FROM v$sql_plan v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_v$sql_plan_statistics AS SELECT '||new_columns2||'v.* FROM v$sql_plan_statistics v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_v$sql_workarea AS SELECT '||new_columns2||'v.* FROM v$sql_workarea v';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$sqltext_with_newlines AS SELECT '||new_columns2||'v.* FROM gv$sqltext_with_newlines v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_v$sqltext_with_newlines AS SELECT '||new_columns2||'v.* FROM v$sqltext_with_newlines v';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_dba_part_col_stats AS SELECT '||new_columns3||'v.* FROM dba_part_col_statistics v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_dba_subpart_col_stats AS SELECT '||new_columns3||'v.* FROM dba_subpart_col_statistics v';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_dba_tab_cols AS SELECT '||new_columns4||'v.* FROM dba_tab_cols v';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$parameter2 AS SELECT '||new_columns5||'v.* FROM gv$parameter2 v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_gv$system_parameter2 AS SELECT '||new_columns5||'v.* FROM gv$system_parameter2 v';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW sqlt$_v$session AS SELECT '||new_columns6||'v.audsid, v.username, v.prev_hash_value FROM v$session v';
END;
/

GRANT SELECT ON sqlt$_v$session TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE OR REPLACE VIEW sqlt$_dba_outlines AS
SELECT signature,
       creator owner,
       ol_name name,
       category,
       version,
       timestamp,
       sql_text,
       DECODE(BITAND(flags, 1), 0, 'UNUSED',     1, 'USED')         used,
       DECODE(BITAND(flags, 2), 0, 'COMPATIBLE', 2, 'INCOMPATIBLE') compatible,
       DECODE(BITAND(flags, 4), 0, 'ENABLED',    4, 'DISABLED')     enabled,
       DECODE(BITAND(flags, 8), 0, 'NORMAL',     8, 'LOCAL')        format
  FROM outln.ol$;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_outlines (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),     -- 11.2.9
  signature                  VARCHAR2(64),
  owner                      VARCHAR2(30),
  name                       VARCHAR2(30),
  category                   VARCHAR2(30),
  version                    VARCHAR2(64),
  timestamp                  DATE,
  sql_text                   CLOB,
  used                       VARCHAR2(16),
  compatible                 VARCHAR2(16),
  enabled                    VARCHAR2(16),
  format                     VARCHAR2(16)
);

CREATE UNIQUE INDEX sqlt$_outlines_pk ON sqlt$_outlines(pk_id);
ALTER TABLE sqlt$_outlines ADD (CONSTRAINT sqlt$_outlines_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_outlines_n1 ON sqlt$_outlines(statement_id);

/* ------------------------------------------------------------------------- */

DECLARE
  rdbms_release NUMBER;
  my_sql0 VARCHAR2(32767);
  my_sql1 VARCHAR2(32767);
BEGIN
  my_sql0 :=
  'CREATE OR REPLACE VIEW sqlt$_dba_outline_hints AS '||
  'SELECT o.signature, '||
  '       o.creator owner, '||
  '       o.ol_name name, '||
  '       o.category, '||
  '       hint# hintno, '||
  '       HINT hint, '||
  '       h.hint_type, '||
  '       h.stage# stage,  '||
  '       h.node# node,  '||
  '       h.table_pos, '||
  '       h.table_name, '||
  '       h.user_table_name, '||
  '       h.cost, '||
  '       h.cardinality, '||
  '       h.bytes, '||
  '       h.join_pred '||
  '  FROM outln.ol$ o, '||
  '       outln.ol$hints h '||
  ' WHERE o.ol_name = h.ol_name '||
  '   AND o.category = h.category ';

  my_sql1 :=
  'CREATE OR REPLACE VIEW sqlt$_dba_outline_nodes AS '||
  'SELECT o.signature, '||
  '       o.creator owner, '||
  '       o.ol_name name, '||
  '       o.category, '||
  '       n.node_id node, '||
  '       NODE_NAME, '||
  '       n.node_type, '||
  '       n.parent_id '||
  '  FROM outln.ol$ o, '||
  '       outln.ol$nodes n '||
  ' WHERE o.ol_name = n.ol_name '||
  '   AND o.category = n.category ';

  SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_release
    FROM v$instance;

  IF rdbms_release >= 10.2 THEN
    my_sql0 := REPLACE(my_sql0, 'HINT', 'NVL(h.hint_string, h.hint_text)');
    my_sql1 := REPLACE(my_sql1, 'NODE_NAME', 'n.node_name');
  ELSE
    my_sql0 := REPLACE(my_sql0, 'HINT', 'h.hint_text');
    my_sql1 := REPLACE(my_sql1, 'NODE_NAME', 'TO_CHAR(NULL) node_name');
  END IF;

  EXECUTE IMMEDIATE my_sql0;
  EXECUTE IMMEDIATE my_sql1;
END;
/
--if you get ORA-00904: "H"."HINT_STRING": invalid identifier, see then 422983.1

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_outline_hints (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),     -- 11.2.9
  signature                  VARCHAR2(64),
  owner                      VARCHAR2(30),
  name                       VARCHAR2(30),
  category                   VARCHAR2(30),
  hintno                     NUMBER,
  hint                       CLOB,
  hint_type                  NUMBER,
  stage                      NUMBER,
  node                       NUMBER,
  table_pos                  NUMBER,
  table_name                 VARCHAR2(30),
  user_table_name            VARCHAR2(64),
  cost                       NUMBER,
  cardinality                NUMBER,
  bytes                      NUMBER,
  join_pred                  VARCHAR2(2000)
);

CREATE UNIQUE INDEX sqlt$_outline_hints_pk ON sqlt$_outline_hints(pk_id);
ALTER TABLE sqlt$_outline_hints ADD (CONSTRAINT sqlt$_outline_hints_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_outline_hints_n1 ON sqlt$_outline_hints(statement_id);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_sql_profiles (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),     -- 11.2.9
  name                       VARCHAR2(30),
  category                   VARCHAR2(30),
  signature                  NUMBER,
  sql_text                   CLOB,
  created                    DATE,
  last_modified              DATE,
  description                VARCHAR2(500),
  type                       VARCHAR2(9),
  status                     VARCHAR2(8)
);

CREATE UNIQUE INDEX sqlt$_sql_profiles_pk ON sqlt$_sql_profiles(pk_id);
ALTER TABLE sqlt$_sql_profiles ADD (CONSTRAINT sqlt$_sql_profiles_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_sql_profiles_n1 ON sqlt$_sql_profiles(statement_id);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_sql_profile_hints (
  pk_id                      INTEGER NOT NULL, -- sqlt$_pk_id_s
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),     -- 11.2.9
  source                     VARCHAR2(32),
  line_num                   INTEGER,
  err                        INTEGER,
  resol                      INTEGER,
  used                       INTEGER,
  token                      INTEGER,
  org                        INTEGER,
  lvl                        INTEGER,
  category                   VARCHAR2(30),
  hint                       VARCHAR2(512)
);

CREATE UNIQUE INDEX sqlt$_sql_profile_hints_pk ON sqlt$_sql_profile_hints(pk_id);
ALTER TABLE sqlt$_sql_profile_hints ADD (CONSTRAINT sqlt$_sql_profile_hints_pk PRIMARY KEY (pk_id));

CREATE INDEX sqlt$_sql_profile_hints_n1 ON sqlt$_sql_profile_hints(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_adv_rationale (
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),     -- 11.2.9
  id                         NUMBER NOT NULL,
  task_id                    NUMBER NOT NULL,
  type                       VARCHAR2(30),
  rec_id                     NUMBER,
  impact_msg_id              NUMBER,
  impact_val                 NUMBER,
  obj_id                     NUMBER,
  msg_id                     NUMBER,
  attr1                      VARCHAR2(4000),
  attr2                      VARCHAR2(4000),
  attr3                      VARCHAR2(4000),
  attr4                      VARCHAR2(4000),
  attr5                      CLOB
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_adv_rationale_n1 ON sqlg$_adv_rationale(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dbms_xplan (
  statement_id               INTEGER NOT NULL,
  module                     VARCHAR2(64) NOT NULL,
  child_number               INTEGER,
  format                     VARCHAR2(4000),
  line_id                    INTEGER NOT NULL,
  plan_table_output          VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_dbms_xplan_n1 ON sqlg$_dbms_xplan(statement_id, child_number);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_optstat_tab_history (
  statement_id               INTEGER NOT NULL,
  obj#                       NUMBER,
  savtime_char               VARCHAR2(64),
  savtime_date               DATE,
  flags                      NUMBER,
  rowcnt                     NUMBER,
  blkcnt                     NUMBER,
  avgrln                     NUMBER,
  samplesize                 NUMBER,
  analyzetime                DATE,
  cachedblk                  NUMBER,
  cachehit                   NUMBER,
  logicalread                NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_optstat_tab_history_n1 ON sqlg$_optstat_tab_history(statement_id, obj#);

/* ------------------------------------------------------------------------- */

-- 11.2.9.6
CREATE GLOBAL TEMPORARY TABLE sqlg$_optstat_histhead_history (
  statement_id               INTEGER NOT NULL,
  obj#                       NUMBER,
  intcol#                    NUMBER,
  savtime_char               VARCHAR2(64),
  savtime_date               DATE,
  flags                      NUMBER,
  null_cnt                   NUMBER,
  minimum                    NUMBER,
  maximum                    NUMBER,
  distcnt                    NUMBER,
  density                    NUMBER,
  lowval                     RAW(32),
  hival                      RAW(32),
  avgcln                     NUMBER,
  sample_distcnt             NUMBER,
  sample_size                NUMBER,
  timestamp#                 DATE,
  endpoints                  INTEGER,
  low_value_boiled           VARCHAR2(128),
  high_value_boiled          VARCHAR2(128)
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_optstat_histhead_hist_n1 ON sqlg$_optstat_histhead_history(statement_id, obj#, intcol#, savtime_date);

/* ------------------------------------------------------------------------- */

-- 11.2.9.6
CREATE GLOBAL TEMPORARY TABLE sqlg$_optstat_histgrm_history (
  statement_id               INTEGER NOT NULL,
  obj#                       NUMBER,
  intcol#                    NUMBER,
  savtime_char               VARCHAR2(64),
  savtime_date               DATE,
  bucket                     NUMBER,
  endpoint                   NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_optstat_histgrm_hist_n1 ON sqlg$_optstat_histgrm_history(statement_id, obj#, intcol#, savtime_date);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_optstat_ind_history (
  statement_id               INTEGER NOT NULL,
  obj#                       NUMBER,
  savtime_char               VARCHAR2(64),
  savtime_date               DATE,
  flags                      NUMBER,
  rowcnt                     NUMBER,
  blevel                     NUMBER,
  leafcnt                    NUMBER,
  distkey                    NUMBER,
  lblkkey                    NUMBER,
  dblkkey                    NUMBER,
  clufac                     NUMBER,
  samplesize                 NUMBER,
  analyzetime                DATE,
  guessq                     NUMBER,
  cachedblk                  NUMBER,
  cachehit                   NUMBER,
  logicalread                NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_optstat_ind_history_n1 ON sqlg$_optstat_ind_history(statement_id, obj#);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_optstat_aux_history (
  statement_id               INTEGER NOT NULL,
  savtime                    TIMESTAMP (6) WITH TIME ZONE,
  cpuspeednw                 NUMBER,
  ioseektim                  NUMBER,
  iotfrspeed                 NUMBER,
  cpuspeed                   NUMBER,
  mbrc                       NUMBER,
  sreadtim                   NUMBER,
  mreadtim                   NUMBER,
  maxthr                     NUMBER,
  slavethr                   NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_optstat_aux_history_n1 ON sqlg$_optstat_aux_history(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_optstat_opr (
  statement_id               INTEGER NOT NULL,
  operation                  VARCHAR2(64),
  start_time                 TIMESTAMP (6) WITH TIME ZONE,
  end_time                   TIMESTAMP (6) WITH TIME ZONE
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_optstat_opr_n1 ON sqlg$_optstat_opr(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_10053_parse (
  statement_id               INTEGER      NOT NULL,
  section                    VARCHAR2(32) NOT NULL,
  line_num                   INTEGER      NOT NULL,
  c1                         VARCHAR2(128),
  c2                         VARCHAR2(128),
  c3                         VARCHAR2(128),
  c4                         VARCHAR2(128),
  c5                         VARCHAR2(128),
  c6                         VARCHAR2(128),
  c7                         VARCHAR2(512)
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_10053_parse_n1 ON sqlg$_10053_parse(statement_id, section);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_hist_sqlstat (
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),
  snap_id                    NUMBER,
  dbid                       NUMBER,
  instance_number            NUMBER,
  sql_id                     VARCHAR2(13),
  plan_hash_value            NUMBER,
  optimizer_cost             NUMBER,
  optimizer_mode             VARCHAR2(10),
  optimizer_env_hash_value   NUMBER,
  sharable_mem               NUMBER,
  loaded_versions            NUMBER,
  version_count              NUMBER,
  module                     VARCHAR2(64),
  action                     VARCHAR2(64),
  sql_profile                VARCHAR2(64),
  force_matching_signature   NUMBER,
  parsing_schema_id          NUMBER,
  parsing_schema_name        VARCHAR2(30),
  parsing_user_id            NUMBER,
  fetches_total              NUMBER,
  fetches_delta              NUMBER,
  end_of_fetch_count_total   NUMBER,
  end_of_fetch_count_delta   NUMBER,
  sorts_total                NUMBER,
  sorts_delta                NUMBER,
  executions_total           NUMBER,
  executions_delta           NUMBER,
  px_servers_execs_total     NUMBER,
  px_servers_execs_delta     NUMBER,
  loads_total                NUMBER,
  loads_delta                NUMBER,
  invalidations_total        NUMBER,
  invalidations_delta        NUMBER,
  parse_calls_total          NUMBER,
  parse_calls_delta          NUMBER,
  disk_reads_total           NUMBER,
  disk_reads_delta           NUMBER,
  buffer_gets_total          NUMBER,
  buffer_gets_delta          NUMBER,
  rows_processed_total       NUMBER,
  rows_processed_delta       NUMBER,
  cpu_time_total             NUMBER,
  cpu_time_delta             NUMBER,
  elapsed_time_total         NUMBER,
  elapsed_time_delta         NUMBER,
  iowait_total               NUMBER,
  iowait_delta               NUMBER,
  clwait_total               NUMBER,
  clwait_delta               NUMBER,
  apwait_total               NUMBER,
  apwait_delta               NUMBER,
  ccwait_total               NUMBER,
  ccwait_delta               NUMBER,
  direct_writes_total        NUMBER,
  direct_writes_delta        NUMBER,
  plsexec_time_total         NUMBER,
  plsexec_time_delta         NUMBER,
  javexec_time_total         NUMBER,
  javexec_time_delta         NUMBER,
  flag                       NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_dba_hist_sqlstat_n1 ON sqlg$_dba_hist_sqlstat(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_hist_snapshot (
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),
  snap_id                    NUMBER,
  dbid                       NUMBER,
  instance_number            NUMBER,
  startup_time               TIMESTAMP(3),
  begin_interval_time        TIMESTAMP(3),
  end_interval_time          TIMESTAMP(3),
  flush_elapsed              INTERVAL DAY(5) TO SECOND(1),
  snap_level                 NUMBER,
  error_count                NUMBER,
  snap_flag                  NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_dba_hist_snapshot_n1 ON sqlg$_dba_hist_snapshot(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_hist_sqltext (
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),
  dbid                       NUMBER,
  sql_id                     VARCHAR2(13),
  sql_text                   CLOB,
  command_type               NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_dba_hist_sqltext_n1 ON sqlg$_dba_hist_sqltext(statement_id);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_dba_hist_sql_plan (
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),
  dbid                       NUMBER,
  sql_id                     VARCHAR2(13),
  plan_hash_value            NUMBER,
  id                         NUMBER,
  operation                  VARCHAR2(30),
  options                    VARCHAR2(30),
  object_node                VARCHAR2(128),
  object#                    NUMBER,
  object_owner               VARCHAR2(30),
  object_name                VARCHAR2(31),
  object_alias               VARCHAR2(65),
  object_type                VARCHAR2(20),
  optimizer                  VARCHAR2(20),
  parent_id                  NUMBER,
  depth                      NUMBER,
  position                   NUMBER,
  search_columns             NUMBER,
  cost                       NUMBER,
  cardinality                NUMBER,
  bytes                      NUMBER,
  other_tag                  VARCHAR2(35),
  partition_start            VARCHAR2(64),
  partition_stop             VARCHAR2(64),
  partition_id               NUMBER,
  other                      VARCHAR2(4000),
  distribution               VARCHAR2(20),
  cpu_cost                   NUMBER,
  io_cost                    NUMBER,
  temp_space                 NUMBER,
  access_predicates          VARCHAR2(4000),
  filter_predicates          VARCHAR2(4000),
  projection                 VARCHAR2(4000),
  time                       NUMBER,
  qblock_name                VARCHAR2(31),
  remarks                    VARCHAR2(4000),
  timestamp                  DATE,
  other_xml                  CLOB
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_dba_hist_sql_plan_n1 ON sqlg$_dba_hist_sql_plan(statement_id);

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_hist_files (
  file_type                  VARCHAR2(24)  NOT NULL,
  filename                   VARCHAR2(64)  NOT NULL,
  file_date                  DATE          NOT NULL,
  file_size                  INTEGER       NOT NULL,
  statement_id               INTEGER,
  statid                     VARCHAR2(30),
  file_text                  CLOB
);

CREATE UNIQUE INDEX sqlt$_hist_files_pk ON sqlt$_hist_files(filename);
ALTER TABLE sqlt$_hist_files ADD (CONSTRAINT sqlt$_hist_files_pk PRIMARY KEY (filename));

GRANT SELECT ON sqlt$_hist_files TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_other_xml (
  statement_id               INTEGER        NOT NULL,
  statid                     VARCHAR2(30)   NOT NULL,
  plan_hash_value            NUMBER         NOT NULL,
  plan_source                VARCHAR2(30)   NOT NULL, -- 'DBA_HIST_SQLSTAT', 'GV$SQL', 'V$SQL', 'EXPLAIN PLAN FOR'
  sql_id                     VARCHAR2(13)   NOT NULL,
  hash_value                 NUMBER,                  -- plans out of DBA_HIST_SQLSTAT have no hash_value
  cost                       NUMBER,                  -- 11.2.9.6
  signature                  NUMBER,                  -- in case signature could not be generated
  sql_text                   VARCHAR2(4000) NOT NULL,
  sql_text_clob              CLOB           NOT NULL,
  other_xml                  CLOB           NOT NULL
);

CREATE UNIQUE INDEX sqlt$_other_xml_pk ON sqlt$_other_xml(statid, plan_hash_value);
ALTER TABLE sqlt$_other_xml ADD (CONSTRAINT sqlt$_other_xml_pk PRIMARY KEY (statid, plan_hash_value));

CREATE INDEX sqlt$_other_xml_n1 ON sqlt$_other_xml(statement_id, plan_hash_value);

GRANT SELECT ON sqlt$_other_xml TO PUBLIC;

/* ------------------------------------------------------------------------- */

CREATE TABLE sqlt$_other_xml_hints (
  statement_id               INTEGER        NOT NULL,
  statid                     VARCHAR2(30)   NOT NULL,
  plan_hash_value            NUMBER         NOT NULL,
  line_num                   INTEGER        NOT NULL,
  hint                       VARCHAR2(2000) NOT NULL
);

CREATE UNIQUE INDEX sqlt$_other_xml_hints_pk ON sqlt$_other_xml_hints(statid, plan_hash_value, line_num);
ALTER TABLE sqlt$_other_xml_hints ADD (CONSTRAINT sqlt$_other_xml_hints_pk PRIMARY KEY (statid, plan_hash_value, line_num));

CREATE INDEX sqlt$_other_xml_hints_n1 ON sqlt$_other_xml_hints(statement_id, plan_hash_value);

/* ------------------------------------------------------------------------- */

CREATE GLOBAL TEMPORARY TABLE sqlg$_xplore_test (
  statement_id               INTEGER        NOT NULL,
  test_id                    VARCHAR2(16)   NOT NULL,
  unique_id                  VARCHAR2(32)   NOT NULL,
  test_type                  VARCHAR2(16)   NOT NULL, -- BASELINE, INIT.ORA, FIX_CTRL
  test                       VARCHAR2(256)  NOT NULL,
  error                      VARCHAR2(512),
  sqlt_plan_hash_value       NUMBER,
  hash_value                 NUMBER,
  address                    VARCHAR2(16),
  plan_hash_value            INTEGER,
  optimizer_cost             NUMBER,
  buffer_gets                NUMBER,
  cpu_time                   NUMBER,
  disk_reads                 NUMBER,
  elapsed_time               NUMBER,
  reverse_test               VARCHAR2(256)
) ON COMMIT PRESERVE ROWS;

CREATE UNIQUE INDEX sqlg$_xplore_test_pk ON sqlg$_xplore_test(statement_id, test_id);
ALTER TABLE sqlg$_xplore_test ADD (CONSTRAINT sqlg$_xplore_test_pk PRIMARY KEY (statement_id, test_id));

/* ------------------------------------------------------------------------- */

-- 11.2.9.6
CREATE OR REPLACE VIEW sqlt$_dba_tab_statistics AS
SELECT SUBSTR(statid, 2, 4) statement_id,
       statid,
       c5 owner,
       c1 table_name,
       c2 partition_name,
       c3 subpartition_name,
       CASE
       WHEN SUBSTR(statid, 1, 1) = 'f' THEN 'FIXED TABLE'
       WHEN c2 IS NULL THEN 'TABLE'
       WHEN c3 IS NULL THEN 'PARTITION'
       ELSE 'SUBPARTITION'
       END object_type,
       n1 num_rows,
       n2 blocks,
       n3 avg_row_len,
       n4 sample_size,
       d1 last_analyzed,
       DECODE(BITAND(flags, 2), 0, 'NO', 'YES') global_stats,
       DECODE(BITAND(flags, 1), 0, 'NO', 'YES') user_stats
  FROM sqlt$_stattab
 WHERE type = 'T'
   AND SUBSTR(statid, 1, 1) IN ('s', 'f');

/* ------------------------------------------------------------------------- */

-- 11.2.9.7
CREATE GLOBAL TEMPORARY TABLE sqlg$_join_order (
  statement_id               INTEGER NOT NULL,
  statid                     VARCHAR2(30),
  join_order                 NUMBER,
  qblock_name                VARCHAR2(30),
  object_alias               VARCHAR2(65),
  table_alias                VARCHAR2(65),
  access_type                VARCHAR2(30),
  index_exec_order           NUMBER,
  index_oper_id              NUMBER,
  index_owner                VARCHAR2(30),
  index_name                 VARCHAR2(30),
  index_access               VARCHAR2(255),
  index_cost                 NUMBER,
  index_estim_card           NUMBER,
  index_starts               NUMBER,
  index_actual_rows          NUMBER,
  table_exec_order           NUMBER,
  table_oper_id              NUMBER,
  table_owner                VARCHAR2(30),
  table_name                 VARCHAR2(30),
  table_access               VARCHAR2(255),
  table_cost                 NUMBER,
  table_orig_card            NUMBER,
  table_estim_card           NUMBER,
  table_starts               NUMBER,
  table_actual_rows          NUMBER
) ON COMMIT PRESERVE ROWS;

CREATE INDEX sqlg$_join_order_n1 ON sqlg$_join_order(statement_id, join_order);
CREATE INDEX sqlg$_join_order_n2 ON sqlg$_join_order(statid, join_order);

/* ------------------------------------------------------------------------- */

-- 11.2.9.7
CREATE OR REPLACE VIEW sqlg$_join_order_v1 AS
SELECT statement_id,
       statid,
       join_order,
       qblock_name,
       TRIM(',' FROM index_oper_id||','||table_oper_id) oper_id,
       TRIM('.' FROM table_owner||'.'||table_name) table_name,
       object_alias,
       table_alias,
       access_type,
       table_access,
       table_cost,
       table_orig_card,
       table_estim_card,
       table_starts,
       table_actual_rows,
       TRIM('.' FROM index_owner||'.'||index_name) index_name,
       index_access,
       index_cost,
       index_estim_card,
       index_starts,
       index_actual_rows
  FROM sqlg$_join_order;

/* ------------------------------------------------------------------------- */

-- 11.2.9.7
CREATE OR REPLACE VIEW sqlg$_join_order_v2 AS
SELECT statement_id,
       statid,
       join_order,
       NVL(table_alias, table_name) table_name,
       access_type,
       index_name
  FROM sqlg$_join_order;

/* ------------------------------------------------------------------------- */

SPOOL OFF;
SET ECHO OFF TERM ON;
PRO SQCTAB completed.
