遠端建立SP模式

[@more@]

以下為:spcusr.sql修改後的指令碼：

Rem
Rem $Header: spcusr.sql 31-may-2005.14:34:29 cdgreen Exp $
Rem
Rem spcusr.sql
Rem
Rem Copyright (c) 1999, 2005, Oracle. 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 cdgreen 05/24/05 - 4246955
Rem cdgreen 04/18/05 - 4228432
Rem cdgreen 10/29/04 - 10gR2_sqlstats
Rem cdgreen 08/12/04 - 10g R2
Rem vbarrier 02/12/04 - 3412853
Rem cdialeri 12/04/03 - 3290482
Rem cdialeri 10/14/03 - 10g - streams - rvenkate
Rem cdialeri 08/05/03 - 10g F3
Rem vbarrier 02/25/03 - 10g RAC
Rem cdialeri 11/15/02 - 10g F1
Rem vbarrier 09/06/02 - SYSAUX and db default temp tbs
Rem vbarrier 04/01/02 - 2290728
Rem vbarrier 03/05/02 - Segment Statistics
Rem cdialeri 02/07/02 - 2218573
Rem cdialeri 11/30/01 - 9.2 - features 1
Rem cdialeri 04/26/01 - 9.0
Rem cdialeri 09/12/00 - sp_1404195
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 feedback off;
whenever sqlerror exit sql.sqlcode

prompt
prompt Choose the PERFSTAT user's password
prompt ------------------------------------

prompt Not specifying a password will result in the installation FAILING
prompt
prompt &&perfstat_password

prompt &&connection_text

Rem Begin spooling after password has been entered
spool spcusr.lis

begin
if '&&perfstat_password' is null then
raise_application_error(-20101, 'Install failed - No password specified for PERFSTAT user');
end if;
end;
/

prompt -----------------------------------------------------
set heading off
prompt select utl_inaddr.get_host_address||':1521/'||instance_name from v$instance;

col connection_text new_value connection_text print
select 'Using connection_text '||
upper(nvl('&connection_text','@'||utl_inaddr.get_host_address||':1521/'||instance_name))||
' as PERFSTAT default tablespace.'
, nvl('&connection_text','@'||utl_inaddr.get_host_address||':1521/'||instance_name) connection_text
from sys.v$instance;

begin
if '&connection_text' is null then
raise_application_error(-20101, 'Install failed - No connection_text specified for PERFSTAT user');
end if;
end;
/

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

prompt
prompt
prompt Choose the Default tablespace for the PERFSTAT user
prompt ----------------------------------------------------

prompt Below is the list of online tablespaces in this database which can
prompt store user data. Specifying the SYSTEM tablespace for the user's
prompt default tablespace will result in the installation FAILING, as
prompt using SYSTEM for performance data is not supported.
prompt
prompt Choose the PERFSTAT users's default tablespace. This is the tablespace
prompt in which the STATSPACK tables and indexes will be created.

column db_default format a28 heading 'STATSPACK DEFAULT TABLESPACE'
select tablespace_name, contents
, decode(tablespace_name,'SYSAUX','*') db_default
from sys.dba_tablespaces
where tablespace_name <> 'SYSTEM'
and contents = 'PERMANENT'
and status = 'ONLINE'
order by tablespace_name;

prompt
prompt Pressing will result in STATSPACK's recommended default
prompt tablespace (identified by *) being used.
prompt

set heading off
col default_tablespace new_value default_tablespace noprint
select 'Using tablespace '||
upper(nvl('&&default_tablespace','SYSAUX'))||
' as PERFSTAT default tablespace.'
, nvl('&default_tablespace','SYSAUX') default_tablespace
from sys.dual;
set heading on

begin
if upper('&&default_tablespace') = 'SYSTEM' then
raise_application_error(-20101, 'Install failed - SYSTEM tablespace specified for DEFAULT tablespace');
end if;
end;
/

prompt
prompt
prompt Choose the Temporary tablespace for the PERFSTAT user
prompt ------------------------------------------------------

prompt Below is the list of online tablespaces in this database which can
prompt store temporary data (e.g. for sort workareas). Specifying the SYSTEM
prompt tablespace for the user's temporary tablespace will result in the
prompt installation FAILING, as using SYSTEM for workareas is not supported.

prompt
prompt Choose the PERFSTAT user's Temporary tablespace.

column db_default format a26 heading 'DB DEFAULT TEMP TABLESPACE'
select t.tablespace_name, t.contents
, decode(dp.property_name,'DEFAULT_TEMP_TABLESPACE','*') db_default
from sys.dba_tablespaces t
, sys.database_properties dp
where t.contents = 'TEMPORARY'
and t.status = 'ONLINE'
and dp.property_name(+) = 'DEFAULT_TEMP_TABLESPACE'
and dp.property_value(+) = t.tablespace_name
order by tablespace_name;

prompt
prompt Pressing will result in the database's default Temporary
prompt tablespace (identified by *) being used.
prompt

set heading off
col temporary_tablespace new_value temporary_tablespace noprint
select 'Using tablespace '||
nvl('&&temporary_tablespace',property_value)||
' as PERFSTAT temporary tablespace.'
, nvl('&&temporary_tablespace',property_value) temporary_tablespace
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
set heading on

begin
if upper('&&temporary_tablespace') = 'SYSTEM' then
raise_application_error(-20101, 'Install failed - SYSTEM tablespace specified for TEMPORARY tablespace');
end if;
end;
/

prompt
prompt
prompt ... Creating PERFSTAT user

create user perfstat
identified by &&perfstat_password
default tablespace &&default_tablespace
temporary tablespace &&temporary_tablespace;

alter user PERFSTAT quota unlimited on &&default_tablespace;

prompt
prompt
prompt ... Installing required packages

Rem
Rem Install required packages
Rem

@@dbmspool

prompt
prompt
prompt ... Creating views

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

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

create or replace view STATS$V_$FILESTATXS as
select ts.name tsname
, df.name filename
, fs.phyrds
, fs.phywrts
, fs.readtim
, fs.writetim
, fs.singleblkrds
, fs.phyblkrd
, fs.phyblkwrt
, fs.singleblkrdtim
, fw.count wait_count
, fw.time time
, df.file#
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 or replace public synonym STATS$V$FILESTATXS for STATS$V_$FILESTATXS;

create or replace view STATS$V_$TEMPSTATXS as
select ts.name tsname
, tf.name filename
, tm.phyrds
, tm.phywrts
, tm.readtim
, tm.writetim
, tm.singleblkrds
, tm.phyblkrd
, tm.phyblkwrt
, tm.singleblkrdtim
, fw.count wait_count
, fw.time time
, tf.file#
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# + (select value from v$parameter where name='db_files'));
create or replace public synonym STATS$V$TEMPSTATXS for STATS$V_$TEMPSTATXS;

create or replace view STATS$V_$SQLXS as
select max(sql_text) sql_text
, max(sql_id) sql_id
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(fetches) fetches
, sum(executions) executions
, sum(px_servers_executions) px_servers_executions
, sum(end_of_fetch_count) end_of_fetch_count
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(direct_writes) direct_writes
, sum(buffer_gets) buffer_gets
, sum(application_wait_time) application_wait_time
, sum(concurrency_wait_time) concurrency_wait_time
, sum(cluster_wait_time) cluster_wait_time
, sum(user_io_wait_time) user_io_wait_time
, sum(plsql_exec_time) plsql_exec_time
, sum(java_exec_time) java_exec_time
, sum(rows_processed) rows_processed
, max(command_type) command_type
, address address
, old_hash_value old_hash_value
, max(hash_value) hash_value
, count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
, max(sql_profile) sql_profile
, max(program_id) program_id
, max(program_line#) program_line#
, max(exact_matching_signature) exact_matching_signature
, max(force_matching_signature) force_matching_signature
, max(last_active_time) last_active_time
from v$sql
group by old_hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

create or replace view STATS$V_$SQLSTATS_SUMMARY as
select sql_id
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(executions) executions
, sum(version_count) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, sum(sharable_mem) sharable_mem
from v$sqlstats
group by sql_id;
create or replace public synonym STATS$V$SQLSTATS_SUMMARY for STATS$V_$SQLSTATS_SUMMARY;

--
-- Workaround for Remaster Stats bug 4029107

create or replace view STATS$V_$DYNAMIC_REM_STATS as
select drms remaster_ops
, avg_drm_time*drms remaster_time
, objects_per_drm*drms remastered_objects
, quisce_t*drms quiesce_time
, frz_t*drms freeze_time
, cleanup_t*drms cleanup_time
, replay_t*drms replay_time
, fixwrite_t*drms fixwrite_time
, sync_t*drms sync_time
, res_cleaned*drms resources_cleaned
, replay_s*drms replayed_locks_sent
, replay_r*drms replayed_locks_received
, my_objects current_objects
from x$kjdrmafnstats;
grant select on STATS$V_$DYNAMIC_REM_STATS to PERFSTAT;
create synonym PERFSTAT.V$DYNAMIC_REMASTER_STATS for STATS$V_$DYNAMIC_REM_STATS;

prompt
prompt
prompt ... Granting privileges

Rem
Rem Grant privileges
Rem

/* 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 STATS$X_$KCBFWAIT to PERFSTAT;
grant select on STATS$X_$KSPPSV to PERFSTAT;
grant select on STATS$X_$KSPPI to PERFSTAT;
grant select on STATS$V_$FILESTATXS to PERFSTAT;
grant select on STATS$V_$TEMPSTATXS to PERFSTAT;
grant select on STATS$V_$SQLXS to PERFSTAT;
grant select on STATS$V_$SQLSTATS_SUMMARY 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 GV_$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_$ENQUEUE_STATISTICS 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;
grant select on V_$RESOURCE_LIMIT to PERFSTAT;
grant select on V_$DLM_MISC to PERFSTAT;
grant select on V_$UNDOSTAT to PERFSTAT;
grant select on V_$SQL_PLAN to PERFSTAT;
grant select on V_$DB_CACHE_ADVICE to PERFSTAT;
grant select on V_$PGASTAT to PERFSTAT;
grant select on V_$INSTANCE_RECOVERY to PERFSTAT;
grant select on V_$SHARED_POOL_ADVICE to PERFSTAT;
grant select on V_$SQL_WORKAREA_HISTOGRAM to PERFSTAT;
grant select on V_$PGA_TARGET_ADVICE to PERFSTAT;
grant select on V_$SEGSTAT to PERFSTAT;
grant select on V_$SEGMENT_STATISTICS to PERFSTAT;
grant select on V_$SEGSTAT_NAME to PERFSTAT;
grant select on V_$JAVA_POOL_ADVICE to PERFSTAT;
grant select on V_$THREAD to PERFSTAT;
grant select on V_$CR_BLOCK_SERVER to PERFSTAT;
grant select on V_$CURRENT_BLOCK_SERVER to PERFSTAT;
grant select on V_$INSTANCE_CACHE_TRANSFER to PERFSTAT;
grant select on V_$FILE_HISTOGRAM to PERFSTAT;
grant select on V_$TEMP_HISTOGRAM to PERFSTAT;
grant select on V_$EVENT_HISTOGRAM to PERFSTAT;
grant select on V_$EVENT_NAME to PERFSTAT;
grant select on V_$SYS_TIME_MODEL to PERFSTAT;
grant select on V_$SESS_TIME_MODEL to PERFSTAT;
grant select on V_$STREAMS_CAPTURE to PERFSTAT;
grant select on V_$STREAMS_APPLY_COORDINATOR to PERFSTAT;
grant select on V_$STREAMS_APPLY_READER to PERFSTAT;
grant select on V_$STREAMS_APPLY_SERVER to PERFSTAT;
grant select on V_$PROPAGATION_SENDER to PERFSTAT;
grant select on V_$PROPAGATION_RECEIVER to PERFSTAT;
grant select on V_$BUFFERED_QUEUES to PERFSTAT;
grant select on V_$BUFFERED_SUBSCRIBERS to PERFSTAT;
grant select on V_$RULE_SET to PERFSTAT;
grant select on V_$OSSTAT to PERFSTAT;
grant select on V_$PROCESS to PERFSTAT;
grant select on V_$PROCESS_MEMORY to PERFSTAT;
grant select on V_$STREAMS_POOL_ADVICE to PERFSTAT;
grant select on V_$SGA_TARGET_ADVICE to PERFSTAT;
grant select on V_$SQLSTATS to PERFSTAT;
grant select on V_$MUTEX_SLEEP to PERFSTAT;

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

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

spool off;
whenever sqlerror continue;
set echo on feedback on;

======================以下為spcreate.sql修改後的指令碼：

Rem
Rem $Header: spcreate.sql 16-apr-2002.11:22:55 vbarrier Exp $
Rem
Rem spcreate.sql
Rem
Rem Copyright (c) 1999, 2002, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem spcreate.sql - Statistics Create
Rem
Rem DESCRIPTION
Rem SQL*PLUS command file which creates the STATSPACK user,
Rem tables and package for the performance diagnostic tool STATSPACK
Rem
Rem NOTES
Rem Note the script. connects INTERNAL and so must be run from
Rem an account which is able to connect internal.
Rem
Rem MODIFIED (MM/DD/YY)
Rem cdialeri 02/16/00 - 1191805
Rem cdialeri 12/06/99 - 1103031
Rem cdialeri 08/13/99 - Created
Rem

--
-- Create PERFSTAT user and required privileges
@@spcusr

--
-- Build the tables and synonyms
connect perfstat/&&perfstat_password&&connection_text
@@spctab
-- Create the statistics Package
@@spcpkg