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 off

If statspack is newly installed, then generate a single snapshot. Otherwise ora-20200 will be thrown.

http://alphaoragroup.com/2022/09/14/ora-20200-database-instance-1010810948-1-does-not-exist-instatsdatabase_instance/

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/g

After replacing, install statspack with custom user and enjoy !!!

Leave a Reply