修改spcreate.sql指令碼，使本地指令碼建立遠端statspack
以下為: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
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
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10130206/viewspace-624172/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- shell指令碼-透過lftp同步遠端目錄到本地指令碼FTP
- 遠端分發sh指令碼指令碼
- SSH 無密碼遠端執行指令碼密碼指令碼
- 自動生成Statspack的指令碼指令碼
- Statspack之九-其它重要指令碼指令碼
- 產生Statspack快照的Shell指令碼指令碼
- ssh執行遠端指令碼遇到的坑指令碼
- [Oracle] 指令碼建立DBOracle指令碼
- GIT(7)----強制用遠端程式碼覆蓋本地修改Git
- 快速建立分支Shell指令碼指令碼
- CentOS使用expect批次遠端執行指令碼和命令CentOS指令碼
- 使ssh不用輸入密碼（指令碼執行scp）密碼指令碼
- 日期判斷行修改指令碼指令碼
- seleniumGrid分散式遠端執行測試指令碼分散式指令碼
- 使用VSCode遠端除錯惡意Powershell指令碼VSCode除錯指令碼
- 遠端啟動HADOOP+YARN叢集Shell指令碼HadoopYarn指令碼
- 建立 Docker 映象倉庫指令碼Docker指令碼
- 動態建立檢視指令碼指令碼
- linux建立定時指令碼Linux指令碼
- envoy 本地拉取遠端程式碼
- 批量修改檔名的bash指令碼指令碼
- 庫物件指令碼抽取指令碼物件指令碼
- 巧用shell指令碼生成快捷指令碼指令碼
- 生成insert指令碼的指令碼指令碼
- 批量匯出建立索引的指令碼索引指令碼
- shell oracle 建立使用者指令碼Oracle指令碼
- hr使用者示例建立指令碼指令碼
- 建立dblink的兩個指令碼。指令碼
- 指令碼建立Oracle的資料庫指令碼Oracle資料庫
- 建立Azure Blob Snapshot的指令碼指令碼
- iOS使用shell指令碼批量修改屬性iOS指令碼
- centos6 修改網路卡名指令碼CentOS指令碼
- 批量修改OC類名指令碼 -- python指令碼Python
- 利用 ant 指令碼修改專案包名指令碼
- 密碼指令碼密碼指令碼
- [指令碼例項]——統計系統資訊指令碼指令碼
- 前端js指令碼與防止js指令碼前端JS指令碼
- 簡單介紹Shell指令碼之檔案批次建立與修改的方法指令碼