SET ECHO ON TERM ON NUMF "";
SPOOL sqcpkg.lis;
REM
REM $Header: 215187.1 sqcpkg.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   sqcpkg.sql
REM
REM DESCRIPTION
REM   This script creates the packages owned by schema 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 sqcpkg.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus SQLTXPLAN/[pwd]
REM   SQL> start sqcpkg.sql
REM
REM NOTES
REM   1. This script is executed automatically by sqcreate.sql
REM   2. For possible errors see sqcpkg.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 = ".,";
DECLARE
  rdbms_release NUMBER;
  my_sql VARCHAR2(32767);
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;
  IF rdbms_release >= 10.2 THEN
    my_sql := 'DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name)';
  ELSE
    my_sql := 'NULL';
  END IF;
  EXECUTE IMMEDIATE
'CREATE OR REPLACE PROCEDURE sqlt$_execute_tuning_task (
  task_name IN VARCHAR2 )
IS
/* $Header: 215187.1 sqcpkg.sql 11.2.9.6 2009/07/23 csierra $ */
BEGIN
  IF task_name IS NOT NULL THEN
    '||my_sql||';
  END IF;
END sqlt$_execute_tuning_task;';
END;
/
GRANT EXECUTE ON sqlt$_execute_tuning_task TO PUBLIC;

SET ECHO OFF TERM ON;

PRO
PRO Creating Package Specs SQLT$D...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgd.pks
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Package Specs SQLT$P...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgp.pks
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Package Specs SQLT$R...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgr.pks
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Package Specs SQLT$I...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgi.pks
SET ECHO OFF TERM ON;
SHOW ERRORS;

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

-- 11.2.9.6
CREATE OR REPLACE VIEW sqlt$_dba_ind_statistics AS
SELECT SUBSTR(statid, 2, 4) statement_id,
       statid,
       sqlt$d.index_compare_key(statid, c5, c1) index_compare_key,
       c5 owner,
       c1 index_name,
       c2 partition_name,
       c3 subpartition_name,
       CASE
       WHEN c2 IS NULL THEN 'INDEX'
       WHEN c3 IS NULL THEN 'PARTITION'
       ELSE 'SUBPARTITION'
       END object_type,
       n7 blevel,
       n2 leaf_blocks,
       n3 distinct_keys,
       n4 avg_leaf_blocks_per_key,
       n5 avg_data_blocks_per_key,
       n6 clustering_factor,
       n1 num_rows,
       n8 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 = 'I'
   AND SUBSTR(statid, 1, 1) = 's';

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

-- 11.2.9.6
CREATE OR REPLACE VIEW sqlt$_dba_tab_col_statistics AS
SELECT SUBSTR(statid, 2, 4) statement_id,
       statid,
       c5 owner,
       c1 table_name,
       c2 partition_name,
       c3 subpartition_name,
       c4 column_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_distinct,
       sqlt$r.get_external_value(n6) low_value,
       sqlt$r.get_external_value(n7) high_value,
       n2 density,
       n5 num_nulls,
       COUNT(*) num_buckets,
       d1 last_analyzed,
       n4 sample_size,
       DECODE(BITAND(flags, 2), 0, 'NO', 'YES') global_stats,
       DECODE(BITAND(flags, 1), 0, 'NO', 'YES') user_stats,
       n8 avg_col_len
  FROM sqlt$_stattab
 WHERE type = 'C'
   AND SUBSTR(statid, 1, 1) IN ('s', 'f')
 GROUP BY
       statid,
       c5,
       c1,
       c2,
       c3,
       c4,
       n1,
       n6,
       n7,
       n2,
       n5,
       d1,
       n4,
       flags,
       n8;

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

PRO
PRO Creating Package Body SQLT$D...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgd.pkb
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Package Body SQLT$P...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgp.pkb
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Package Body SQLT$R...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgr.pkb
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Package Body SQLT$I...
PRO
SET ECHO ON TERM OFF;
@@sqcpkgi.pkb
SET ECHO OFF TERM ON;
SHOW ERRORS;

PRO
PRO Creating Grants on Packages...
PRO
SET ECHO ON TERM OFF;
GRANT EXECUTE ON sqlt$i TO PUBLIC;
GRANT EXECUTE ON sqlt$d TO PUBLIC;
GRANT EXECUTE ON sqlt$r TO PUBLIC;
GRANT EXECUTE ON sqlt$p TO PUBLIC;

SET HEA ON LIN 2000 PAGES 1000 TRIMS ON TIM OFF SERVEROUT ON SIZE 1000000;
SHOW PARAMETERS NLS;
EXEC sqlt$r.initialization;

SET ECHO OFF TERM ON;
PRO
PRO Taking a snapshot of DBA_SEGMENTS, please wait...
PRO
EXEC sqlt$d.refresh_sqli$_segments;
PRO
PRO Snapshot of DBA_SEGMENTS completed
PRO This was the last step of the installation.
PRO
SET HEA ON LIN 2000 PAGES 1000 TRIMS ON TIM OFF;
COL tool_version FOR A16 HEA 'Tool Version';
SELECT sqlt$d.get_param('tool_version', 'I') tool_version FROM DUAL;
COL install_date FOR A16 HEA 'Install Date';
SELECT sqlt$d.get_param('install_date', 'I') install_date FROM DUAL;
COL column_value FOR A128 HEA 'Directories';
SELECT column_value FROM TABLE(sqlt$d.directories);
COL column_value FOR A128 HEA 'Libraries';
SELECT column_value FROM TABLE(sqlt$d.packages);
SPOOL OFF;
WHENEVER SQLERROR CONTINUE;
PRO SQCPKG completed.
