Home » RDBMS Server » Performance Tuning » Error Creating STATSPACK on Oracle10g
| Error Creating STATSPACK on Oracle10g [message #251367] |
Fri, 13 July 2007 06:00  |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Hi All,
We have an oracle10g r.2 standby database installed on a Hp-ux server ..which have some problems relating to memory allocations..i tried to install statspack on this database (Standbay database) but i got this error:
'Using SYSAUX tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
create sequence STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-01031: insufficient privileges'
the default tablespace=sysaux
the password=perfstat
the temp tablespace= temp
I searched for solutions on the interenet but i could not find any..!i tried to drop and recreate statspack using (spdrop.sql and spcreate.sql) as many sites refer to but without any success..the result of the 'spduser.lis' file is the following:
by checking the following files ,i found that the 'spdtab.lis' returns the following:
Dropping old versions (if any)
drop public synonym STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
drop sequence PERFSTAT.STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-02289: sequence does not exist
drop public synonym STATS$FILESTATXS
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
drop table PERFSTAT.STATS$FILESTATXS
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop public synonym STATS$TEMPSTATXS
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
drop table PERFSTAT.STATS$TEMPSTATXS
*
ERROR at line 1:
ORA-00942: table or view does not exist
.
.
.
.
.
drop package PERFSTAT.STATSPACK
*
ERROR at line 1:
ORA-04043: object STATSPACK does not exist
NOTE:
SPDTAB complete. Please check spdtab.lis for any errors.
Is there any way to do this right?? ..
If i make another tablespace specific for perfstat user ,can this solve the problem??
Thanks in Advance,
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #251374 is a reply to message #251369] |
Fri, 13 July 2007 06:42  |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thanks for replay,
the user is 'SYS' as the following:
SQL> show user
USER is "SYS"
SQL> @/opt/oracle/product/1020/rdbms/admin/spcreate.sql;
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: perfstat
perfstat
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
SD_DATA PERMANENT
SD_INDEX PERMANENT
SYSAUX PERMANENT *
TOOLS PERMANENT
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace:
Using tablespace SYSAUX as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
SQL>
SQL> --
SQL> -- Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: spctab.sql 31-may-2005.14:05:43 cdgreen Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2005, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create tables to hold
SQL> Rem start and end "snapshot" statistical information
SQL> Rem
SQL> Rem NOTES
SQL> Rem Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 03/08/05 - 10gR2 misc
SQL> Rem vbarrier 02/18/05 - 4081984
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 07/16/04 - 10gR2
SQL> Rem cdialeri 03/25/04 - 3516921
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 11/05/03 - 3202706
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem cdialeri 02/27/03 - 10g F2: baseline, purge
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem cdialeri 09/27/02 - sleep4
SQL> Rem vbarrier 03/20/02 - 2143634
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/11/02 - 9.2 - features 2
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem cdialeri 04/22/01 - Undostat changes
SQL> Rem cdialeri 03/02/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 03/20/00 - Support for purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 01/26/00 - 1169401
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cmlim 07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem gwood 10/16/95 - Version to run as sys without using many views
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set showmode off echo off;
If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.
Using SYSAUX tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
create sequence STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-01031: insufficient privileges
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
the databse still return the same error...
thanks in advance,
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #251386 is a reply to message #251382] |
Fri, 13 July 2007 07:06 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thank you for your replay,
the 'spcusr.lis' give the following:
SQL> @/opt/oracle/product/1020/spcusr.lis;
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Below is t..." - rest of line ignored.
SP2-0088: missing SET keyword
Usage: STORE {SET} filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
SP2-0734: unknown command beginning "default ta..." - rest of line ignored.
SP2-0734: unknown command beginning "using SYST..." - rest of line ignored.
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0734: unknown command beginning "TABLESPACE..." - rest of line ignored.
SP2-0734: unknown command beginning "SD_DATA ..." - rest of line ignored.
SP2-0734: unknown command beginning "SD_INDEX ..." - rest of line ignored.
SP2-0734: unknown command beginning "SYSAUX ..." - rest of line ignored.
SP2-0734: unknown command beginning "TOOLS ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "USERS ..." - rest of line ignored.
SP2-0734: unknown command beginning "Pressing <..." - rest of line ignored.
SP2-0734: unknown command beginning "tablespace..." - rest of line ignored.
SP2-0734: unknown command beginning "Enter valu..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Using tabl..." - rest of line ignored.
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0734: unknown command beginning "Below is t..." - rest of line ignored.
SP2-0088: missing SET keyword
Usage: STORE {SET} filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
SP2-0734: unknown command beginning "tablespace..." - rest of line ignored.
SP2-0734: unknown command beginning "installati..." - rest of line ignored.
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0734: unknown command beginning "TABLESPACE..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "TEMP ..." - rest of line ignored.
SP2-0734: unknown command beginning "Pressing <..." - rest of line ignored.
SP2-0734: unknown command beginning "tablespace..." - rest of line ignored.
SP2-0734: unknown command beginning "Enter valu..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Using tabl..." - rest of line ignored.
SP2-0734: unknown command beginning "... Creati..." - rest of line ignored.
SP2-0734: unknown command beginning "... Instal..." - rest of line ignored.
SP2-0734: unknown command beginning "... Creati..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "... Granti..." - rest of line ignored.
SP2-0042: unknown command "NOTE:" - rest of line ignored.
SP2-0734: unknown command beginning "SPCUSR com..." - rest of line ignored.
SQL>
which is full with errors ..is this normal??
thanks in Advance,
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #251401 is a reply to message #251386] |
Fri, 13 July 2007 08:10 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Hi again,
the cause for these errors to occur is not obvious to me..
are they related in any way to unsuccessful drop of statspack objects..and if this is the reason.. what can i do to make oracle drop all the objects..?
thanks in advance,
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #251404 is a reply to message #251402] |
Fri, 13 July 2007 08:15 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thanks for your notes,
i already post the whole file 'spcusr.lis' ..but if you missed something ,this is the file once again:
SQL> @/opt/oracle/product/1020/spcusr.lis;
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Below is t..." - rest of line ignored.
SP2-0088: missing SET keyword
Usage: STORE {SET} filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
SP2-0734: unknown command beginning "default ta..." - rest of line ignored.
SP2-0734: unknown command beginning "using SYST..." - rest of line ignored.
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0734: unknown command beginning "TABLESPACE..." - rest of line ignored.
SP2-0734: unknown command beginning "SD_DATA ..." - rest of line ignored.
SP2-0734: unknown command beginning "SD_INDEX ..." - rest of line ignored.
SP2-0734: unknown command beginning "SYSAUX ..." - rest of line ignored.
SP2-0734: unknown command beginning "TOOLS ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "USERS ..." - rest of line ignored.
SP2-0734: unknown command beginning "Pressing <..." - rest of line ignored.
SP2-0734: unknown command beginning "tablespace..." - rest of line ignored.
SP2-0734: unknown command beginning "Enter valu..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Using tabl..." - rest of line ignored.
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0734: unknown command beginning "Below is t..." - rest of line ignored.
SP2-0088: missing SET keyword
Usage: STORE {SET} filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
SP2-0734: unknown command beginning "tablespace..." - rest of line ignored.
SP2-0734: unknown command beginning "installati..." - rest of line ignored.
SP2-0734: unknown command beginning "Choose the..." - rest of line ignored.
SP2-0734: unknown command beginning "TABLESPACE..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "TEMP ..." - rest of line ignored.
SP2-0734: unknown command beginning "Pressing <..." - rest of line ignored.
SP2-0734: unknown command beginning "tablespace..." - rest of line ignored.
SP2-0734: unknown command beginning "Enter valu..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "Using tabl..." - rest of line ignored.
SP2-0734: unknown command beginning "... Creati..." - rest of line ignored.
SP2-0734: unknown command beginning "... Instal..." - rest of line ignored.
SP2-0734: unknown command beginning "... Creati..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "... Granti..." - rest of line ignored.
SP2-0042: unknown command "NOTE:" - rest of line ignored.
SP2-0734: unknown command beginning "SPCUSR com..." - rest of line ignored.
SQL>
thanks in advance,
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #251409 is a reply to message #251408] |
Fri, 13 July 2007 08:27 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thanks again,
well ..sorry i could not see the '.sql' you mention it immediately..the following is the content of the 'spcusr.sql' file:
sd4rep:/opt/oracle >cat /opt/oracle/product/1020/rdbms/admin/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
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;
/
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 <return> 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 <return> 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;
sd4rep:/opt/oracle >
i hoop is what you need ..?
thanks in advance,
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #251428 is a reply to message #251422] |
Fri, 13 July 2007 09:46 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thanks for your replay,
well this is the only file that i have in the filesystem:
---------------------------------------------------------
sd4rep:/opt/oracle >ls -l /opt/oracle/product/1020/rdbms/admin/
total 58872
-rw-r----- 1 oracle dba 20668 Jun 7 2001 a0801070.sql
-rw-r----- 1 oracle dba 8700 Aug 6 2003 a0900010.sql
.
.
.
.
-rw-r----- 1 oracle dba 861 May 17 2002 spcreate.sql
-rw-r----- 1 oracle dba 82281 May 31 2005 spctab.sql
-rw-r----- 1 oracle dba 15247 May 31 2005 spcusr.sql
-rw-r----- 1 oracle dba 149763 May 31 2005 spdoc.txt
-rw-r----- 1 oracle dba 758 Jun 19 2000 spdrop.sql
-rw-r----- 1 oracle dba 7479 May 31 2005 spdtab.sql
-rw-r----- 1 oracle dba 1669 May 31 2005 spdusr.sql
-rw-r----- 1 oracle dba 4900 Mar 7 2003 sppurge.sql
-rw-r----- 1 oracle dba 5193 Mar 31 2005 sprepcon.sql
.
.
.
.
sd4rep:/opt/oracle >
sd4rep:/opt/oracle >cat /opt/oracle/product/1020/rdbms/admin/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
So ..can we use another script??
thanks in advance,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #252682 is a reply to message #252660] |
Thu, 19 July 2007 11:29 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thanks for you,
well,there are two files ('spcusr.sql') as follows:
----------------------------------------------------
sd4rep:/opt/oracle >find / -name spcusr.sql
find: cannot search /etc/ftpd
find: cannot open /etc/opt/resmon/persistence
find: cannot open /etc/opt/resmon/pipe
find: cannot open /etc/opt/hpsmc
find: cannot open /etc/sam/custom
find: cannot open /tmp/.AgentSockets
find: cannot open /tmp/depot/catalog
find: cannot open /tmp/depot/PHSS_32732
find: cannot open /tmp/depot/PHSS_30970
find: cannot open /tmp/depot/PHNE_28810
find: cannot open /tmp/depot/PHSS_31221
find: cannot open /tmp/depot/PHSS_32508
find: cannot open /tmp/depot/PHSS_32509
find: cannot open /tmp/depot/PHSS_32510
find: cannot open /tmp/depot/PHSS_32731
find: cannot open /tmp/depot/BUNDLE
find: cannot open /tmp/SCSI/depot/catalog
find: cannot open /tmp/SCSI/depot/PHKL_32089
find: cannot open /tmp/SCSI/depot/PHKL_34187
find: cannot open /tmp/SCSI/depot/BUNDLE
find: cannot open /home/beheer/.elm
find: cannot open /home/beheer/Mail
find: cannot open /home/beheer/Backup_PC
find: cannot search /opt/samba_src/samba/source
find: cannot open /opt/mx/newconfig/var/opt/mx/config
find: cannot search /opt/mx/newconfig/var/opt/mx/tools
find: cannot open /opt/hpwebadmin/newconfig
find: cannot open /opt/hpwebadmin/webapps
find: cannot open /opt/hpwebadmin/work
find: cannot open /opt/hpws/webmin/conf
find: cannot open /opt/hpws/webmin/logs
find: cannot open /opt/hpws/webmin/newconfig/opt
find: cannot search /opt/ssh/src
find: cannot open /opt/hpservices/RemoteSupport/config
find: cannot open /opt/hpservices/RemoteSupport/collect/map_triggers
find: cannot open /opt/hpservices/bin
find: cannot open /opt/hpservices/etc
find: cannot open /opt/hpservices/contrib
find: cannot open /opt/hpservices/adm
find: cannot open /opt/hpservices/tmp
find: cannot open /opt/hpservices/mqueue
find: cannot open /opt/hpservices/stats
find: cannot open /opt/runner
find: cannot open /opt/hpsmc
/opt/oracle/product/920/rdbms/admin/spcusr.sql
/opt/oracle/product/1020/rdbms/admin/spcusr.sql
find: cannot open /usr/lib/sw/lib
find: cannot open /usr/newconfig/etc/sam/custom
find: cannot open /var/opt/samba/private
find: cannot open /var/opt/mx/depot11/catalog
find: cannot open /var/opt/mx/depot11/AgentConfig
find: cannot open /var/opt/mx/depot10/catalog
find: cannot open /var/opt/mx/depot10/AgentConfig
find: cannot open /var/opt/hpservices/incidents
find: cannot open /var/opt/hpservices/log
find: cannot open /var/opt/hpservices/mqueue
find: cannot open /var/opt/runner
find: cannot open /var/opt/dce/security/preauth
find: cannot open /var/opt/mysql/mysql
find: cannot open /var/opt/mysql/test
find: cannot open /var/opt/hpsmc
find: cannot search /var/opt/centric/backup/lost+found
find: cannot open /var/opt/centric/backup/PATCH_SMC-HP04
find: cannot open /var/opt/OV/conf/sec
find: cannot open /var/opt/OV/datafiles/sec
find: cannot open /var/opt/OV/tmp/bbc
find: cannot open /var/opt/OV/tmp/osspi
find: cannot open /var/adm/sw/queue
find: cannot open /var/adm/sw/products
find: cannot open /var/adm/sw/save
find: cannot open /var/adm/sw/tmp
find: cannot open /var/adm/ilogin
find: cannot open /var/adm/fbackupfiles
find: cannot open /var/spool/uucp/.Corrupt
find: cannot open /var/spool/uucp/.Sequence
find: cannot open /var/spool/uucp/.Workspace
find: cannot open /var/spool/uucp/.Xqtdir
find: cannot open /var/statmon/sm.temp
find: cannot open /var/statmon/sm.bak.temp
find: cannot search /dev/vx
find: cannot open /.elm
find: cannot open /.ssh
find: cannot open /Mail
find: cannot open /.dt/Desktop
sd4rep:/opt/oracle >
-----------------
/opt/oracle/product/920/rdbms/admin/spcusr.sql
/opt/oracle/product/1020/rdbms/admin/spcusr.sql
-----------------------------------------------
and when i open the this one '/1020/rdbms/admin/spcusr.sql' i get:
-----------------
sd4rep:/opt/oracle >cat /opt/oracle/product/1020/rdbms/admin/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
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;
/
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 <return> 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 <return> 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;
sd4rep:/opt/oracle >
So any solution??
thanks in advance,
|
|
|
|
|
|
| Re: Error Creating STATSPACK on Oracle10g [message #422366 is a reply to message #251367] |
Tue, 15 September 2009 20:02 |
diri
Messages: 1
Registered: September 2009
|
Junior Member |
|
|
I meet the same problem but I solve it. I suggest you execute as such steps:
1 drop the schema using /@?/rdbms/admin/spdrop
2 login as user oracle into oracle server using ssh or telnet.
3 execute 'sqlplus'
4 enter '/ as sysdba'
5 Then execute the spcreate command. Now I wish it would be fine.
Regards
Whisky
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 12:26:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
