SET ECHO ON TERM ON NUMF "";
SPOOL sqcusr.lis;
REM
REM $Header: 215187.1 sqcusr.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   sqcusr.sql
REM
REM DESCRIPTION
REM   This script creates user SQLTXPLAIN and the grants it needs
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected INTERNAL (SYS) as
REM      SYSDBA
REM   2. The following 5 utilities must be pre-installed:
REM        o  sys.dbms_metadata
REM        o  sys.ctx_report
REM        o  sys.dbms_random
REM        o  sys.utl_file
REM        o  sys.dbms_shared_pool
REM           To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL
REM           script. The PRVTPOOL.PLB script is automatically
REM           executed after DBMSPOOL.SQL runs. These scripts are
REM           not run by CATPROC.SQL (Review Note:91229.1)
REM   3. During the installation you will be asked to enter the
REM      follwing:
REM        o  SQLTXPLAIN password - Required and it has no default
REM        o  Optional Host String (TNS Alias) - Optional parameter
REM           to specify TNS alias connection including "@", for
REM           example @PROD. If not applicable, just hit enter when
REM           asked for this value.
REM        o  Schema name of main application that will use the
REM           SQLTXPLAIN tool. For example APPS. If there are more
REM           than one application schema owners, enter the main one
REM        o  SQLTXPLAIN default tablespace - You will be presented
REM           with a list, then you will have to enter one tablespace
REM           name from that list
REM        o  SQLTXPLAIN temporary tablespace - Similar as above
REM
REM PARAMETERS
REM   1. None inline. During the installation you will be asked for
REM      the values of the 5 parameters described under
REM      pre-requisites section above
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
REM   3. Execute script sqcusr.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus /nolog
REM   SQL> connect / as sysdba
REM   SQL> start sqcusr.sql
REM
REM NOTES
REM   1. This script is executed automatically by sqcreate.sql
REM   2. For possible errors see sqcusr.lis file
REM
SET ECHO OFF TERM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";
DECLARE
  rdbms_release NUMBER;
BEGIN
  IF USER != 'SYS' THEN
    RAISE_APPLICATION_ERROR(-20100, 'Install failed - SQLTXPLAIN should be installed connected as SYS, 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 TERM ON VER ON;
PRO
PRO ... Creating SQLT$ Directories (default to udump)
PRO
COL p_udump NOPRINT NEW_VALUE p_udump FORMAT A1000;
SELECT value p_udump FROM v$parameter WHERE name = 'user_dump_dest';
PRO user_dump_dest: &&p_udump
BEGIN
  IF '&&p_udump' LIKE '%?%' OR '&&p_udump' LIKE '%*%' OR '&&p_udump' LIKE '%$%' THEN
    RAISE_APPLICATION_ERROR(-20101, 'Install failed - Directory &&p_udump cannot contain "?", "*" or "$" symbols');
  END IF;
END;
/
CREATE OR REPLACE DIRECTORY SQLT$UDUMP AS '&&p_udump';
CREATE OR REPLACE DIRECTORY SQLT$STAGE AS '&&p_udump';

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

PRO
PRO ... Creating SQLTXPLAIN user ...
PRO
PRO Choose the SQLTXPLAIN user password.
PRO
PRO Not specifying a password will result in the installation FAILING
PRO
ACC sqltxplain_password PROMPT 'Specify SQLTXPLAIN password: ' HIDE;
PRO

SET VER OFF;
BEGIN
  IF '&&sqltxplain_password' IS NULL THEN
    RAISE_APPLICATION_ERROR(-20102, 'Install failed - No password specified for SQLTXPLAIN user');
  END IF;
  IF '&&sqltxplain_password' LIKE '% %' THEN
    RAISE_APPLICATION_ERROR(-20102, 'Install failed - Password for SQLTXPLAIN user cannot contain spaces');
  END IF;
END;
/
CREATE USER sqltxplain IDENTIFIED BY "&&sqltxplain_password";
SET VER ON;

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

PRO
PRO ... Getting Host String ...
PRO
PRO Specify optional Host String (TNS Alias)
PRO Include @ symbol, i.e. @PROD
PRO If not applicable, just enter nothing and hit enter
PRO
PRO Specify OPTIONAL Host String (i.e.: @PROD, or just hit enter)
PRO
PRO &&host_string
PRO
BEGIN
  IF '&&host_string' IS NOT NULL AND '&&host_string' NOT LIKE '@%' THEN
    RAISE_APPLICATION_ERROR(-20103, 'Install failed - Incorrect Host String, it must contain @: &&host_string');
  END IF;
END;
/

SET ECHO ON;
/* ---------------------------------------------------------------------- */

PRO
PRO  Create SQLTXPLAIN grant privileges
PRO

PRO
PRO System privileges
PRO
GRANT ALTER SESSION                            TO sqltxplain;
GRANT ANALYZE ANY                              TO sqltxplain;
GRANT CREATE PROCEDURE                         TO sqltxplain;
GRANT CREATE SEQUENCE                          TO sqltxplain;
GRANT CREATE SESSION                           TO sqltxplain;
GRANT CREATE TABLE                             TO sqltxplain;
GRANT CREATE TYPE                              TO sqltxplain;
GRANT CREATE VIEW                              TO sqltxplain;
GRANT SELECT ANY DICTIONARY                    TO sqltxplain;

PRO
PRO DIRECTORY privileges
PRO
GRANT READ  ON DIRECTORY SQLT$STAGE            TO sqltxplain;
GRANT READ  ON DIRECTORY SQLT$UDUMP            TO sqltxplain;
GRANT WRITE ON DIRECTORY SQLT$STAGE            TO sqltxplain;
GRANT WRITE ON DIRECTORY SQLT$UDUMP            TO sqltxplain;
WHENEVER SQLERROR CONTINUE;
PRO
PRO NOTE:
PRO Some errors are expected in this DIRECTORY section below
PRO
GRANT READ  ON DIRECTORY TRCA$OUTPUT           TO sqltxplain;
GRANT WRITE ON DIRECTORY TRCA$OUTPUT           TO sqltxplain;
GRANT READ  ON DIRECTORY SQLT$STAGE            TO trcanlzr;
GRANT WRITE ON DIRECTORY SQLT$STAGE            TO trcanlzr;
PRO
PRO NOTE:
PRO Some errors are expected in this DIRECTORY section above
PRO
WHENEVER SQLERROR EXIT SQL.SQLCODE;

PRO
PRO Roles privileges
PRO
GRANT EXECUTE_CATALOG_ROLE                     TO sqltxplain;
GRANT GATHER_SYSTEM_STATISTICS                 TO sqltxplain;
GRANT SELECT_CATALOG_ROLE                      TO sqltxplain;

PRO
PRO Utilities privileges
PRO
GRANT EXECUTE ON sys.dbms_lob                  TO sqltxplain;
GRANT EXECUTE ON sys.dbms_random               TO sqltxplain;
GRANT EXECUTE ON sys.dbms_space_admin          TO sqltxplain;
GRANT EXECUTE ON sys.dbms_stats                TO sqltxplain;
DECLARE
  my_count INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO my_count
    FROM dba_tab_privs
   WHERE owner      = 'SYS'
     AND table_name = 'UTL_FILE'
     AND privilege  = 'EXECUTE'
     AND grantee    = 'PUBLIC';
   IF my_count = 0 THEN
     EXECUTE IMMEDIATE 'GRANT EXECUTE ON sys.utl_file TO sqltxplain';
   END IF;
END;
/

PRO
PRO Privs for catalog objects
PRO
GRANT SELECT ON sys.col_usage$                 TO sqltxplain;
GRANT SELECT ON sys.obj$                       TO sqltxplain;
GRANT SELECT ON sys.seg$                       TO sqltxplain;
GRANT SELECT ON sys.sys_objects                TO sqltxplain;
GRANT SELECT ON sys.tab$                       TO sqltxplain;
GRANT SELECT ON sys.ts$                        TO sqltxplain;
GRANT SELECT ON sys.user$                      TO sqltxplain;

PRO
PRO V$ views
PRO
GRANT SELECT ON sys.v_$database                TO sqltxplain;
GRANT SELECT ON sys.v_$instance                TO sqltxplain;
GRANT SELECT ON sys.v_$object_dependency       TO sqltxplain;
GRANT SELECT ON sys.v_$parameter2              TO sqltxplain;
GRANT SELECT ON sys.v_$process                 TO sqltxplain;
GRANT SELECT ON sys.v_$px_process              TO sqltxplain;
GRANT SELECT ON sys.v_$session                 TO sqltxplain WITH GRANT OPTION;
GRANT SELECT ON sys.v_$session_event           TO sqltxplain;
GRANT SELECT ON sys.v_$sesstat                 TO sqltxplain;
GRANT SELECT ON sys.v_$shared_server           TO sqltxplain;
GRANT SELECT ON sys.v_$sql                     TO sqltxplain;
GRANT SELECT ON sys.v_$sql_plan                TO sqltxplain;
GRANT SELECT ON sys.v_$sql_plan_statistics     TO sqltxplain;
GRANT SELECT ON sys.v_$sql_workarea            TO sqltxplain;
GRANT SELECT ON sys.v_$sqltext_with_newlines   TO sqltxplain;
GRANT SELECT ON sys.v_$statname                TO sqltxplain;

PRO
PRO GV$ views
PRO
GRANT SELECT ON sys.gv_$object_dependency      TO sqltxplain;
GRANT SELECT ON sys.gv_$parameter2             TO sqltxplain;
GRANT SELECT ON sys.gv_$segment_statistics     TO sqltxplain;
GRANT SELECT ON sys.gv_$sql                    TO sqltxplain;
GRANT SELECT ON sys.gv_$sql_plan               TO sqltxplain;
GRANT SELECT ON sys.gv_$sql_plan_statistics    TO sqltxplain;
GRANT SELECT ON sys.gv_$sql_shared_cursor      TO sqltxplain;
GRANT SELECT ON sys.gv_$sql_workarea           TO sqltxplain;
GRANT SELECT ON sys.gv_$sqltext_with_newlines  TO sqltxplain;
GRANT SELECT ON sys.gv_$system_parameter2      TO sqltxplain;

PRO
PRO DBA views
PRO
GRANT SELECT ON sys.dba_constraints            TO sqltxplain;
GRANT SELECT ON sys.dba_dependencies           TO sqltxplain;
GRANT SELECT ON sys.dba_directories            TO sqltxplain;
GRANT SELECT ON sys.dba_ind_columns            TO sqltxplain;
GRANT SELECT ON sys.dba_ind_partitions         TO sqltxplain;
GRANT SELECT ON sys.dba_ind_subpartitions      TO sqltxplain;
GRANT SELECT ON sys.dba_indexes                TO sqltxplain;
GRANT SELECT ON sys.dba_objects                TO sqltxplain;
GRANT SELECT ON sys.dba_part_col_statistics    TO sqltxplain;
GRANT SELECT ON sys.dba_part_histograms        TO sqltxplain;
GRANT SELECT ON sys.dba_source                 TO sqltxplain;
GRANT SELECT ON sys.dba_subpart_col_statistics TO sqltxplain;
GRANT SELECT ON sys.dba_subpart_histograms     TO sqltxplain;
GRANT SELECT ON sys.dba_tab_cols               TO sqltxplain;
GRANT SELECT ON sys.dba_tab_col_statistics     TO sqltxplain;
GRANT SELECT ON sys.dba_tab_columns            TO sqltxplain;
GRANT SELECT ON sys.dba_tab_histograms         TO sqltxplain;
GRANT SELECT ON sys.dba_tab_modifications      TO sqltxplain;
GRANT SELECT ON sys.dba_tab_partitions         TO sqltxplain;
GRANT SELECT ON sys.dba_tab_subpartitions      TO sqltxplain;
GRANT SELECT ON sys.dba_tables                 TO sqltxplain;
GRANT SELECT ON sys.dba_tablespaces            TO sqltxplain;
GRANT SELECT ON sys.dba_users                  TO sqltxplain;

PRO
PRO OL$ views
PRO
GRANT SELECT ON outln.ol$                      TO sqltxplain;
GRANT SELECT ON outln.ol$hints                 TO sqltxplain;
GRANT SELECT ON outln.ol$nodes                 TO sqltxplain;

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

SET ECHO OFF;
PRO
PRO ... Granting system privileges and roles to main application schema
PRO
PRO Specify schema name of main application that will use SQLTXPLAIN.
PRO For example, if installing in an Oracle APPS instance, enter: APPS
PRO Note: You wont be asked for the application password
PRO
PRO &&application_schema
PRO
@@sqguser.sql &&application_schema

SET ECHO ON;

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

PRO
PRO ... Grants for 10g and 11g
PRO
DECLARE
  rdbms_version v$instance.version%TYPE;
  rdbms_release NUMBER;
BEGIN
  SELECT version INTO rdbms_version FROM v$instance;
  rdbms_release := TO_NUMBER(SUBSTR(rdbms_version, 1, INSTR(rdbms_version, '.', 1, 2) - 1));
  IF rdbms_release >= 10 THEN
    EXECUTE IMMEDIATE 'GRANT ADMINISTER ANY SQL TUNING SET               TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT ADVISOR                                     TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT ALTER ANY SQL PROFILE                       TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT CREATE ANY SQL PROFILE                      TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT DROP ANY SQL PROFILE                        TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT ANALYZE ANY DICTIONARY                      TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT CREATE JOB                                  TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.dba_scheduler_jobs            TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.dba_tab_statistics            TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.dba_ind_statistics            TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.gv_$sql_bind_capture          TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.v_$sql_bind_capture           TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.dba_sql_profiles              TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_adv_rationale            TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_adv_tasks                TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_optstat_ind_history      TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_optstat_tab_history      TO sqltxplain';
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_optstat_histhead_history TO sqltxplain'; -- 11.2.9.6
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_optstat_histgrm_history  TO sqltxplain'; -- 11.2.9.6
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_optstat_opr              TO sqltxplain'; -- 11.2.9.6
    EXECUTE IMMEDIATE 'GRANT SELECT ON sys.wri$_optstat_aux_history      TO sqltxplain'; -- 11.2.9.6
    IF rdbms_release >= 10.2 AND rdbms_version >= '10.2.0.2' THEN
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.v_$session_fix_control      TO sqltxplain';
    END IF;
    IF rdbms_release >= 10 AND rdbms_release < 11 THEN
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.sqlprof$attr                TO sqltxplain';
    END IF;
    IF rdbms_release >= 11 THEN
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.dba_autotask_client         TO sqltxplain'; -- 11.2.9.6
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.v_$diag_info                TO sqltxplain';
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.gv_$sql_monitor             TO sqltxplain';
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.gv_$sql_plan_monitor        TO sqltxplain';
      EXECUTE IMMEDIATE 'GRANT SELECT ON sys.sqlobj$data                 TO sqltxplain';
      EXECUTE IMMEDIATE 'GRANT ADMINISTER SQL MANAGEMENT OBJECT          TO sqltxplain';
    END IF;
  END IF;
END;
/
SET ECHO OFF;

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

PRO
PRO ... Grants that may fail if object is not installed
PRO
WHENEVER SQLERROR CONTINUE;
GRANT EXECUTE ON sys.dbms_shared_pool          TO sqltxplain;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

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

PRO
PRO Set up SQLTXPLAIN temporary and default tablespaces;
PRO
PRO Below are the list of online tablespaces in this database.
PRO Decide which tablespace you wish to create the SQLTXPLAIN tables
PRO and indexes.  This will also be the SQLTXPLAIN default tablespace.
PRO
PRO Specifying the SYSTEM tablespace will result in the installation
PRO FAILING, as using SYSTEM for tools data is not supported.
PRO
PRO wait...
PRO

COL tablespace_name FOR A30;
SELECT t.tablespace_name,
       (SELECT NVL(ROUND(SUM(f.bytes)/1024/1024), 0)
          FROM sys.dba_free_space f
         WHERE f.tablespace_name = t.tablespace_name) free_space_mb
  FROM sys.dba_tablespaces t
 WHERE t.tablespace_name <> 'SYSTEM'
   AND t.status = 'ONLINE'
   AND t.contents <> 'TEMPORARY'
 ORDER BY free_space_mb;

PRO
PRO Above are the list of online tablespaces in this database.
PRO Decide which tablespace you wish to create the SQLTXPLAIN tables
PRO and indexes.  This will also be the SQLTXPLAIN default tablespace.
PRO
PRO Specifying the SYSTEM tablespace will result in the installation
PRO FAILING, as using SYSTEM for tools data is not supported.
PRO
PRO PRIOR_DEFAULT_TABLESPACE: &&prior_default_tablespace
PRO
PRO Specify now SQLTXPLAIN default tablespace
PRO
PRO Using &&default_tablespace for the default tablespace
PRO

BEGIN
  IF UPPER('&&default_tablespace') = 'SYSTEM' THEN
    RAISE_APPLICATION_ERROR(-20104, 'Install failed - SYSTEM tablespace specified for DEFAULT tablespace');
  END IF;
END;
/
ALTER USER sqltxplain DEFAULT TABLESPACE &&default_tablespace;
ALTER USER sqltxplain QUOTA UNLIMITED ON &&default_tablespace;

PRO
PRO Choose the SQLTXPLAIN temporary tablespace.
PRO
PRO Specifying the SYSTEM tablespace will result in the installation
PRO FAILING, as using SYSTEM for the temporary tablespace is not recommended.
PRO
PRO wait...
PRO

SET SERVEROUT ON SIZE 1000000
DECLARE
  TYPE cursor_type IS REF CURSOR;
  c_temp_table_spaces cursor_type;
  rdbms_release NUMBER;
  my_tablespace VARCHAR2(32767);
  my_sql VARCHAR2(32767);
BEGIN
  SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_release
    FROM v$instance;

  IF rdbms_release < 10 THEN
    my_sql := 'SELECT tablespace_name '||
              '  FROM sys.dba_tablespaces '||
              ' WHERE tablespace_name <> ''SYSTEM'' '||
              '   AND status = ''ONLINE'' '||
              '   AND contents = ''TEMPORARY'' '||
              ' ORDER BY tablespace_name ';
  ELSE
    my_sql := 'SELECT t.tablespace_name '||
              '  FROM sys.dba_tablespaces t '||
              ' WHERE t.tablespace_name <> ''SYSTEM'' '||
              '   AND t.status = ''ONLINE'' '||
              '   AND t.contents = ''TEMPORARY'' '||
              '   AND NOT EXISTS ( '||
              'SELECT NULL '||
              '  FROM sys.dba_tablespace_groups tg '||
              ' WHERE t.tablespace_name = tg.tablespace_name ) '||
              ' UNION '||
              'SELECT tg.group_name '||
              '  FROM sys.dba_tablespaces t, '||
              '       sys.dba_tablespace_groups tg '||
              ' WHERE t.tablespace_name <> ''SYSTEM'' '||
              '   AND t.status = ''ONLINE'' '||
              '   AND t.contents = ''TEMPORARY'' '||
              '   AND t.tablespace_name = tg.tablespace_name ';
  END IF;

  DBMS_OUTPUT.PUT_LINE('TABLESPACE_NAME');
  DBMS_OUTPUT.PUT_LINE('------------------------------');

  OPEN c_temp_table_spaces FOR my_sql;
  LOOP
    FETCH c_temp_table_spaces INTO my_tablespace;
    EXIT WHEN c_temp_table_spaces%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(my_tablespace);
  END LOOP;
END;
/

PRO
PRO PRIOR_TEMPORARY_TABLESPACE: &&prior_temporary_tablespace
PRO
PRO Specify now SQLTXPLAIN temporary tablespace.
PRO
PRO Using &&temporary_tablespace for the temporary tablespace
PRO

BEGIN
  IF UPPER('&&temporary_tablespace') = 'SYSTEM' THEN
    RAISE_APPLICATION_ERROR(-20105, 'Install failed - SYSTEM tablespace specified for TEMPORARY tablespace');
  END IF;
END;
/
ALTER USER sqltxplain TEMPORARY TABLESPACE &&temporary_tablespace;

UNDEFINE P_UDUMP APPLICATION_SCHEMA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
UNDEFINE PRIOR_DEFAULT_TABLESPACE PRIOR_TEMPORARY_TABLESPACE

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

SPOOL OFF;
PRO SQCUSR completed.
