PRIVILEGES REQUIRED FOR CUSTOM OR NORMAL USER TO GENERATE STATSPACK REPORT IN ORACLE
In general, if an application user requires privileges to generate a statspack report independently, then DBA role is suffice to fulfil the requirement. In case, there is a security policy of granting DBA access to an application user, then individual grants need to be extracted from the database. spcusr.sql script contains all the grants required for PERFSTAT user. Get the grants and give it to the custom user.
[oracle@xhydra ~]$ grep 'grant' /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spcusr.sql
grant create session to PERFSTAT;
grant alter session to PERFSTAT;
grant create table to PERFSTAT;
grant create view to PERFSTAT;
grant create procedure to PERFSTAT;
grant create sequence to PERFSTAT;
grant create public synonym to PERFSTAT;
grant drop public synonym to PERFSTAT;
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$X_$KSXPPING 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;
grant SELECT_CATALOG_ROLE to PERFSTAT;
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_$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;
grant select on V_$DYNAMIC_REMASTER_STATS to PERFSTAT;
grant select on V_$IOSTAT_FUNCTION to PERFSTAT;
grant select on V_$IOSTAT_FUNCTION_DETAIL to PERFSTAT;
grant select on V_$IOSTAT_FILE to PERFSTAT;
grant select on V_$MEMORY_TARGET_ADVICE to PERFSTAT;
grant select on V_$MEMORY_RESIZE_OPS to PERFSTAT;
grant select on V_$MEMORY_DYNAMIC_COMPONENTS to PERFSTAT;
grant select on V_$MEMORY_CURRENT_RESIZE_OPS to PERFSTAT;
grant execute on DBMS_SHARED_POOL to PERFSTAT;
grant execute on DBMS_JOB to PERFSTAT;
--Plus some additional privileges
grant select on stats$database_instance to PERFSTAT;
grant select on stats$snapshot to PERFSTAT;In this case, i create a sample application user called PERFTEST with minimum grants
kIsH@Xhydra<>alter session set "_ORACLE_SCRIPT"=true;
Session altered.
kIsH@Xhydra<>create user perftest identified by password;
User created.
kIsH@Xhydra<>grant create session,unlimited tablespace to perftest;
Grant succeeded.Get the grants provided for PERFSTAT user from database after installing statspack. Note that, statspack need to be installed with SYS user and PERFSTAT user should not be created manually. PERFSTAT user will be created automatically by spcreate.sql. All the objects like tables, views and public synonyms are created with referential constraints under SYS schema not PERFSTAT schema.
spool perfgrants.sql
set heading off
set echo off
set feedback off
set verify off
select 'grant '||privilege||' on '||owner||'.'||table_name||' to perftest;' from dba_tab_privs where grantee='PERFSTAT';
select 'grant '||privilege||' to perftest;' from dba_sys_privs where grantee='PERFSTAT';
select 'grant '||privilege||' on '||owner||'.'||table_name||' to perftest;' from dba_tab_privs where table_name like '%STATS$%';
spool offIf statspack is newly installed, then generate a single snapshot. Otherwise ora-20200 will be thrown.
kIsH@Xhydra<>exec statspack.snap;
PL/SQL procedure successfully completed.
kIsH@Xhydra<>select count(*) from stats$snapshot;
COUNT(*)
----------
1
1 row selected.
Generate statspack report with application user.
kIsH@Xhydra<>conn perftest/password
Connected.
kIsH@Xhydra<>@?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1010810948 DB9ZX 1 db9zx
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1010810948 1 DB9ZX db9zx xhydra
Using 1010810948 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
db9zx DB9ZX 1 15 Sep 2022 10:49 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 1
End Snapshot Id specified: 1
Another way is to install statspack report with a custom user rather than PERFSTAT
Warning: This may cause conflicts or corruption of statspack objects, if not installed correctly. So play around in test environment. Do not install statspack with multiple user in different tablespaces.
Take a backup of spcusr.sql
[oracle@xhydra ~]$ cp /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spcusr.sql /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spcusr.sql.bkp
[oracle@xhydra ~]$ ls -lrt /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spcusr.sql.bkp
-rw-r--r--. 1 oracle oinstall 15806 Sep 14 13:17 /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spcusr.sql.bkp
Replace PERFSTAT user with custom username inside the spcusr.sql, spctab.sql,spcpkg.sql, spdusr.sql, spdtab.sql and spdpkg.sql. It may not be necessary to replace all the files mentioned earlier but make sure all the files containing PERFSTAT are replaced with custom user name.
:%s/perfstat/perfuser/g | %s/PERFSTAT/PERFTEST/gAfter replacing, install statspack with custom user and enjoy !!!