Rem
Rem $Header: spcusr.sql 07-apr-00.17:23:27 cdialeri Exp $
Rem
Rem spcusr.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      spcusr.sql
Rem
Rem    DESCRIPTION
Rem      SQL*Plus command file to create user which will contain the
Rem      STATSPACK database objects.
Rem
Rem    NOTES
Rem      Must be run from connected to SYS (or internal)
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    04/07/00 - 1261813
Rem    cdialeri    02/16/00 - 1191805
Rem    cdialeri    01/26/00 - 1169401
Rem    cdialeri    11/01/99 - 1059172
Rem    cdialeri    08/13/99 - Created
Rem


set echo off verify off showmode off;
whenever sqlerror exit;

spool spcusr.lis

prompt ... Installing Required Packages

Rem
Rem  Install required packages
Rem

@@dbmspool
@@dbmsjob


Rem
Rem  Create X$views as a temporary workaround to externalizing these objects
Rem  through V$views

create view           X_$KCBFWAIT as select * from  X$KCBFWAIT;
create public synonym  X$KCBFWAIT for              X_$KCBFWAIT;
create view           X_$KSPPSV   as select * from  X$KSPPSV;
create public synonym  X$KSPPSV   for              X_$KSPPSV;
create view           X_$KSPPI    as select * from  X$KSPPI;
create public synonym  X$KSPPI    for              X_$KSPPI;
create view           X_$KSQST    as select * from  X$KSQST;
create public synonym  X$KSQST    for              X_$KSQST;

create view V_$FILESTATXS as
select ts.name      tsname
     , df.name	    filename
     , fs.phyrds
     , fs.phywrts
     , fs.readtim
     , fs.writetim
     , fs.phyblkrd
     , fs.phyblkwrt
     , fw.count     wait_count
     , fw.time      time
  from x$kcbfwait   fw
     , v$filestat   fs
     , v$tablespace ts
     , v$datafile   df
 where ts.ts#    = df.ts#
   and fs.file#  = df.file#
   and fw.indx+1 = df.file#;
create public synonym  V$FILESTATXS for V_$FILESTATXS;

create view V_$TEMPSTATXS as
select ts.name      tsname
     , tf.name	    filename
     , tm.phyrds
     , tm.phywrts
     , tm.readtim
     , tm.writetim
     , tm.phyblkrd
     , tm.phyblkwrt
     , fw.count     wait_count
     , fw.time      time
  from x$kcbfwait   fw
     , v$tempstat   tm
     , v$tablespace ts
     , v$tempfile   tf
 where ts.ts#     = tf.ts#
   and tm.file#   = tf.file#
   and fw.indx+1  = tf.file#;
create public synonym  V$TEMPSTATXS for V_$TEMPSTATXS;

create view V_$SQLXS as 
select max(sql_text)        sql_text
     , sum(sharable_mem)    sharable_mem
     , sum(sorts)           sorts
     , min(module)          module
     , sum(loaded_versions) loaded_versions
     , sum(executions)      executions
     , sum(loads)           loads
     , sum(invalidations)   invalidations
     , sum(parse_calls)     parse_calls
     , sum(disk_reads)      disk_reads
     , sum(buffer_gets)     buffer_gets
     , sum(rows_processed)  rows_processed
     , address              address
     , hash_value           hash_value
     , count(1)             version_count
  from v$sql
 group by hash_value, address;
create public synonym V$SQLXS for V_$SQLXS; 

prompt ... Creating PERFSTAT user

Rem
Rem  Create PERFSTAT user and grant privileges
Rem

create user perfstat identified by perfstat;

/*  System privileges  */
grant create session              to PERFSTAT;
grant alter  session              to PERFSTAT;
grant create table                to PERFSTAT;
grant create procedure            to PERFSTAT;
grant create sequence             to PERFSTAT;
grant create public synonym       to PERFSTAT;
grant drop   public synonym       to PERFSTAT;

/*  Select privileges on STATSPACK created views  */
grant select on X_$KCBFWAIT       to PERFSTAT;
grant select on X_$KSPPSV         to PERFSTAT;
grant select on X_$KSPPI          to PERFSTAT;
grant select on X_$KSQST          to PERFSTAT;
grant select on V$FILESTATXS      to PERFSTAT;
grant select on V$TEMPSTATXS      to PERFSTAT;
grant select on V$SQLXS           to PERFSTAT;

/*  Roles  */
grant SELECT_CATALOG_ROLE         to PERFSTAT;

/*  Select privs for catalog objects - ROLES disabled in PL/SQL packages  */
grant select on V_$PARAMETER      to PERFSTAT;
grant select on V_$SYSTEM_PARAMETER to PERFSTAT;
grant select on V_$DATABASE       to PERFSTAT;
grant select on V_$INSTANCE       to PERFSTAT;
grant select on V_$LIBRARYCACHE   to PERFSTAT;
grant select on V_$LATCH          to PERFSTAT;
grant select on V_$LATCH_MISSES   to PERFSTAT;
grant select on V_$LATCH_CHILDREN to PERFSTAT;
grant select on V_$LATCH_PARENT   to PERFSTAT;
grant select on V_$ROLLSTAT       to PERFSTAT;
grant select on V_$ROWCACHE       to PERFSTAT;
grant select on V_$SGA            to PERFSTAT;
grant select on V_$BUFFER_POOL    to PERFSTAT;
grant select on V_$SGASTAT        to PERFSTAT;
grant select on V_$SYSTEM_EVENT   to PERFSTAT;
grant select on V_$SESSION        to PERFSTAT;
grant select on V_$SESSION_EVENT  to PERFSTAT;
grant select on V_$SYSSTAT        to PERFSTAT;
grant select on V_$WAITSTAT       to PERFSTAT;
grant select on V_$SQLAREA        to PERFSTAT;
grant select on V_$SQL            to PERFSTAT;
grant select on V_$SQLTEXT        to PERFSTAT;
grant select on V_$SESSTAT        to PERFSTAT;
grant select on V_$BUFFER_POOL_STATISTICS to PERFSTAT;

/*  Packages  */
grant execute on DBMS_SHARED_POOL to PERFSTAT;
grant execute on DBMS_JOB         to PERFSTAT;


Rem
Rem  Set up PERFSTAT's temporary and default tablespaces;
Rem

prompt
prompt  Below are the list of online tablespaces in this database.
prompt  Decide which tablespace you wish to create the STATSPACK tables
prompt  and indexes.  This will also be the PERFSTAT user's default tablespace.
prompt  Using the SYSTEM tablespace to store statistical data is
prompt  NOT recommended.

select tablespace_name 
  from sys.dba_tablespaces 
 where tablespace_name <> 'SYSTEM'
   and status = 'ONLINE';

prompt
prompt Specify PERFSTAT user's default   tablespace
prompt Using &&default_tablespace for the default tablespace
alter user PERFSTAT quota unlimited on &&default_tablespace;
alter user PERFSTAT default tablespace &&default_tablespace;

prompt Specify PERFSTAT user's temporary tablespace
prompt Using &&temporary_tablespace for the temporary tablespace
alter user PERFSTAT temporary tablespace &&temporary_tablespace;


prompt
prompt NOTE:
prompt   SPCUSR complete. Please check spcusr.lis for any errors.
prompt

spool off;
whenever sqlerror continue;
set echo on;
