Home » RDBMS Server » Performance Tuning » Error Creating STATSPACK on Oracle10g
Error Creating STATSPACK on Oracle10g [message #251367] Fri, 13 July 2007 06:00 Go to next message
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 #251369 is a reply to message #251367] Fri, 13 July 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to be connected as SYS to run these scripts.
Errors "object does not exist" on drop may be see as "normal".

Regards
Michel
Re: Error Creating STATSPACK on Oracle10g [message #251374 is a reply to message #251369] Fri, 13 July 2007 06:42 Go to previous messageGo to next message
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 #251382 is a reply to message #251374] Fri, 13 July 2007 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please check spcusr.lis for any errors.

Regards
Michel
Re: Error Creating STATSPACK on Oracle10g [message #251386 is a reply to message #251382] Fri, 13 July 2007 07:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #251402 is a reply to message #251386] Fri, 13 July 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is normal logs are ALWAYS containing full of errors!

Of course it is not!
Post the beginning of your spcusr.sql but I bet you overwrote it with a spool or other output.

Regards
Michel
Re: Error Creating STATSPACK on Oracle10g [message #251404 is a reply to message #251402] Fri, 13 July 2007 08:15 Go to previous messageGo to next message
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 #251408 is a reply to message #251404] Fri, 13 July 2007 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't say .lis but .sql.

Regards
Michel
Re: Error Creating STATSPACK on Oracle10g [message #251409 is a reply to message #251408] Fri, 13 July 2007 08:27 Go to previous messageGo to next message
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 #251422 is a reply to message #251409] Fri, 13 July 2007 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you are executing this file?
Are you sure you don't have another file with the same name? Above all in your SQL path.

Regards
Michel
Re: Error Creating STATSPACK on Oracle10g [message #251428 is a reply to message #251422] Fri, 13 July 2007 09:46 Go to previous messageGo to next message
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 #251444 is a reply to message #251428] Fri, 13 July 2007 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No this is this one to use but you don't answer to my question.
Search everywhere on your server if you have another script.
put echo $SQLPATH.
...

Well, the best is to ask to your DBA as you don't seem to know enough to achieve this task.

Regards
Michel
Re: Error Creating STATSPACK on Oracle10g [message #252649 is a reply to message #251444] Thu, 19 July 2007 09:39 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your replay,
well ..there is no one to ask ..so if you do not have good answer is there anyone has that????


thanks in advance,
Re: Error Creating STATSPACK on Oracle10g [message #252654 is a reply to message #252649] Thu, 19 July 2007 10:10 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
The question is which file to search for...............???
Re: Error Creating STATSPACK on Oracle10g [message #252658 is a reply to message #252654] Thu, 19 July 2007 10:15 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
??
Re: Error Creating STATSPACK on Oracle10g [message #252660 is a reply to message #251367] Thu, 19 July 2007 10:23 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
So how to drop statspack the right way..
@?/spdrop ..seems not working good??!
Re: Error Creating STATSPACK on Oracle10g [message #252682 is a reply to message #252660] Thu, 19 July 2007 11:29 Go to previous messageGo to next message
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 #261683 is a reply to message #252682] Thu, 23 August 2007 05:53 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


did you got solution ...

Babu
Re: Error Creating STATSPACK on Oracle10g [message #422366 is a reply to message #251367] Tue, 15 September 2009 20:02 Go to previous message
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
Previous Topic: Useing USE_HASH in a GROUP BY Clause (merged 7)
Next Topic: EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option
Goto Forum:
  


Current Time: Sat Oct 01 05:57:01 CDT 2016

Total time taken to generate the page: 0.04690 seconds