Statspack snap failed with ORA-00001: unique constraint

SQL> exec statspack.snap;
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 4349
ORA-06512: at "PERFSTAT.STATSPACK", line 5697
ORA-06512: at "PERFSTAT.STATSPACK", line 105
ORA-06512: at line 1


#

SQL> select object_name,object_type from dba_objects where object_name like 'STATS$SQL_SUMMARY%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
STATS$SQL_SUMMARY              SYNONYM
STATS$SQL_SUMMARY              TABLE
STATS$SQL_SUMMARY_PK           INDEX


SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,STATUS from dba_constraints  where table_name='STATS$SQL_SUMMARY';

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PERFSTAT                       SYS_C002724217                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724216                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724215                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724214                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       SYS_C002724213                 STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       STATS$SQL_SUMMARY_FK           STATS$SQL_SUMMARY              ENABLED
PERFSTAT                       STATS$SQL_SUMMARY_PK           STATS$SQL_SUMMARY              ENABLED

7 rows selected.



####Disable the STATS$SQL_SUMMARY_PK constraint:
SQL> ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

Table altered.

####Collect the DDL of associated index.

set long 99999
select DBMS_METADATA.GET_DDL('INDEX','STATS$SQL_SUMMARY_PK','PERFSTAT') FROM DUAL;


CREATE UNIQUE INDEX "PERFSTAT"."STATS$SQL_SUMMARY_PK" ON "PERFSTAT"."STATS$SQL_SUMMARY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "OLD_HASH_VALUE", "TEXT_SUBSET")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PERFSTAT";



###Re-Created the Index (without unique) 

CREATE INDEX "PERFSTAT"."STATS$SQL_SUMMARY_PK" ON "PERFSTAT"."STATS$SQL_SUMMARY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "OLD_HASH_VALUE", "TEXT_SUBSET")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PERFSTAT";

##Run the stats 

SQL> exec dbms_stats.gather_schema_stats('PERFSTAT',estimate_percent => 100,cascade => true , no_invalidate => false);

or

SQL> exec dbms_stats.gather_table_stats('PERFSTAT','STATS$SQL_SUMMARY',estimate_percent => 100,cascade => true , no_invalidate => false);

# re-run the statspack
SQL> conn PERFSTAT/PERFSTAT

Connected.

SQL> EXECUTE statspack.snap;



2. 

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

no rows selected

SQL> EXECUTE statspack.snap;
BEGIN statspack.snap; END;

*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

As per Doc ID 393300.1,



SQL> create or replace view STATS$V_$SQLXS as
select max(sql_text) sql_text
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
2 3 4 5 6 7 , sum(fetches) fetches
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
8 , sum(buffer_gets) buffer_gets
9 10 , sum(rows_processed) rows_processed
, max(command_type) command_type
11 12 13 14 15 16 , address address
, hash_value hash_value
17 18 , count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
19 , max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
20 21 from v$sql
where ( plan_hash_value > 0
or executions > 0
22 23 or parse_calls > 0
or disk_reads > 0
24 25 26 27 28 29 30 or buffer_gets > 0)
group by hash_value, address; 31

View created.

SQL> @?/rdbms/admin/utlrp

PL/SQL procedure successfully completed.


Table created.


Table created.


Table created.


Index created.


Table created.


Table created.


View created.


View created.


Package created.

No errors.

Package body created.

No errors.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.



JOB PRIV_USER SCHEMA_USER NEXT_DATE INTERVAL FAILURES B WHAT
---------- -------------------- -------------------- -------------------- -------------------- ---------- - ------------------------------------------------------------
146 PERFSTAT PERFSTAT 02-JAN-2021 06:00:00 trunc(SYSDATE+1/24,' 12 N statspack.snap;
HH')

149 PERFSTAT PERFSTAT 03-JAN-2021 00:00:00 trunc(sysdate+1) N statspack.purge(i_num_days=>;90,i_extended_purge=>;TRUE);

SQL> EXECUTE statspack.snap;

PL/SQL procedure successfully completed.

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
RMS 14 02.01.2021:05:08:33

###Reference Note##

Cannot Run Statspack.Snap ORA-00001 (Doc ID 267244.1)

ORA-1 on PERFSTAT.STATS$MUTEX_SLEEP_PK when executing statspack.snap (Doc ID 382993.1)

Ora-00001: Unique Constraint (Perfstat.Stats$Sql_summary_pk) Violated (Doc ID 393300.1)

Find session Generatating huge archivelog

1. Find which table/objects has more number of changes during the problematic window:

set lines 2000 pages 2000
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta) as maxchages
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2017_06_23 08','YYYY_MM_DD HH24')
                                           AND to_date('2017_06_23 13','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name order by maxchages asc;


SNAP_TIME        OBJECT_NAME                     MAXCHAGES
---------------- ------------------------------ ----------

2017_06_23 10:10 ** USER2: 12880000             929040
2017_06_23 12:20 USER3                  945840
2017_06_23 11:50 ** USER2: 12920960            1044608
2017_06_23 12:20 ** USER2: 12548224            1053648
2017_06_23 10:50 USER1                        10270048
2017_06_23 09:50 USER1                        10482448
2017_06_23 08:50 USER1                        12311328
2017_06_23 12:50 USER1                        12840416
2017_06_23 08:30 USER1                        14257616
2017_06_23 10:30 USER1                        14998688
2017_06_23 09:30 USER1                        15096224
2017_06_23 08:40 USER1                        15209616
2017_06_23 09:40 USER1                        16080960
2017_06_23 10:40 USER1                        16450688
2017_06_23 12:20 USER1                        16478448
2017_06_23 11:10 USER1                        27028928
2017_06_23 11:20 USER1                        30691664
2017_06_23 12:10 USER1                        33638784
2017_06_23 12:00 USER1                        35467568
2017_06_23 12:40 USER1                        39375520
2017_06_23 11:50 USER1                        48477552
2017_06_23 11:30 USER1                        48963904
2017_06_23 12:30 USER1                        53693696
2017_06_23 11:40 USER1                        63538416




2. Once you know the objects ,get the SQL information realted to those objects


SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%USER1%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
 AND begin_interval_time BETWEEN to_date('2017_06_23 08','YYYY_MM_DD HH24')
                                           AND to_date('2017_06_23 13','YYYY_MM_DD HH24')
    AND dhss.sql_id = dhst.sql_id;




INSTANCE_NUMBER SQL_ID        EXECUTIONS_DELTA ROWS_PROCESSED_DELTA
--------------- ------------- ---------------- --------------------
2017_06_23 11:20
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
              1 bw9hk94rnvg3z                0                    0

2017_06_23 11:20
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
              1 bw9hk94rnvg3z             2356              7619304

2017_06_23 12:40
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
              1 bw9hk94rnvg3z                0                    0

2017_06_23 10:30
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
              1 bw9hk94rnvg3z               10                   13

2017_06_23 10:30
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
              1 bw9hk94rnvg3z             2317              7493022

2017_06_23 12:40
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
              1 bw9hk94rnvg3z             3790             11601387



set linesize 20000 
set pagesize 20000 
set long 99999
select sql_id,SQL_FULLTEXT from gv$sql where SQL_ID='&SQL_ID';

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
bw9hk94rnvg3z UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3




3. Once you know the SQL ID , get the program and userid who is running it and intimate the user to take care of this query / Program.

SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
         user_id,
         program
  FROM dba_hist_active_sess_history
  WHERE sql_id in ('bw9hk94rnvg3z')
    AND snap_id BETWEEN 489306 AND 489320 ;

INSTANCE_NUMBER TO_CHAR(SAMPLE_TIME    USER_ID PROGRAM
--------------- ------------------- ---------- ----------------------------------------------------------------
1 2017_06_23 11:10:15        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:17        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:17        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:07        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:07        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:57        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:57        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:47        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:47        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:37        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:37        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:27        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:27        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:16        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:16        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:06        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:06        124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:40:41        280 Golden32.exe
1 2017_06_23 11:40:41        124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:41        124 oracle@localhost (J003)
1 2017_06_23 11:40:41        124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:30        280 Golden32.exe
1 2017_06_23 11:40:30        124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:30        124 oracle@localhost (J003)
1 2017_06_23 11:40:30        124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:20        280 Golden32.exe




###

V$ARCHIVED_LOG displays information about the archived logs generated by your database and haven't yet aged out from your control file.
We are going to use this view to generate a report, displaying some useful information about it.

BLOCKS column stores archived log's size in blocks, BLOCK_SIZE stores block size in bytes, so if we multiply them (BLOCKS*BLOCK_SIZE) we get its size in bytes.
ARCHIVED indicates whether the log is archived and DELETED whether RMAN has deleted it.

The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN.


SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');




1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values 
indicate a session generating lots of redo. The query you can use is: 


SELECT s.sid, s.serial#, s.username, s.program,
  i.block_changes
  FROM v$session s, v$sess_io i
  WHERE s.sid = i.sid
  ORDER BY 5 desc, 1, 2, 3, 4;



       SID    SERIAL# USERNAME                       PROGRAM                                          BLOCK_CHANGES
---------- ---------- ------------------------------ ------------------------------------------------ -------------
      3853       2084 WEB_TEST                       oracle@localhost (J003)            23880750
       302      54420 USER5                        Golden32.exe                                           1695622
      3845      58755 USER4                      httpd.worker@site.joshin.com (TNS V1-V3)         1131377
      3144      35736 USER4                      JDBC Connect Client                                    1085874
      2572       4955 USER4                      httpd.worker@site15.localhost.com (TNS V1-V3)          941475
      1727       8937 USER4                      JDBC Connect Client                                     932579
      2415      10553                                oracle@localhost (SMON)              410585
      1442      23763 USER4                      httpd.worker@joshi.com (TNS V1-V3)          357261
      1428       2166 USER4                      JDBC Connect Client                                     237139
      1728      13101 USER4                      httpd.worker@site.joshin.com (TNS V1-V3)          235480
      2565      65219 USER4                      oracle@localhost (J001)              174404




2) Query V$TRANSACTION. This view contains information about the amount of
undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).


SELECT s.sid, s.serial#, s.username, s.program, 
  t.used_ublk, t.used_urec
  FROM v$session s, v$transaction t
  WHERE s.taddr = t.addr
  ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

       SID    SERIAL# USERNAME                       PROGRAM                                           USED_UBLK  USED_UREC
---------- ---------- ------------------------------ ------------------------------------------------ ---------- ----------
      3853       2084 WEB_TEST                       oracle@localhost (J003)               90       6328
      2565      65219 USER4                      oracle@localhost (J001)                3        164
       586       4151 WILLARDC                       SQL Developer                                             1         20



set lines 2000 pages 500
col MODULE for a20
col USERNAME for a15
col EVENT for a30
col MACHINE for a40
select SID,USERNAME,STATUS,PROGRAM,MACHINE,to_char(LOGON_TIME,'dd/mm/yyyy hh24:mi:Ss') LOGON_TIME,SQL_id from v$session where username is not null  and SID='&SID';



       SID USERNAME        STATUS   PROGRAM                                          MACHINE                   LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ------------------------- ------------------- -------------
      3853 WEB_TEST        ACTIVE   oracle@localhost (J003)       localhost 23/06/2017 13:30:16 bw9hk94rnvg3z
                                                                                     cal
SQL> col MACHINE for a40
SQL> /
Enter value for sid: 3853

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      3853 WEB_TEST        ACTIVE   oracle@localhost (J003)       localhost             23/06/2017 13:30:16 bw9hk94rnvg3z
SQL>
SQL> /
Enter value for sid: 302

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
       302 USER5         INACTIVE Golden32.exe                                     PCDATA\CTC2372M1                         23/06/2017 10:01:23
SQL> /
Enter value for sid: 3845

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      3845 USER4       INACTIVE httpd.worker@site.joshin.com (TNS V1-V3)   site17.localhost.com                    23/06/2017 11:07:02
SQL> /
Enter value for sid: 3144

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      3144 USER4       INACTIVE JDBC Connect Client                              site21.localhost.dmz/172.22.1.6         22/06/2017 23:30:00
SQL> /
Enter value for sid: 2572

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      2572 USER4       INACTIVE httpd.worker@site15.localhost.com (TNS V1-V3)   site15.localhost.com                    23/06/2017 12:33:09
SQL> /
Enter value for sid: 1727

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      1727 USER4       INACTIVE JDBC Connect Client                              site21.localhost.dmz/172.22.1.6         23/06/2017 02:00:00

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      1442 USER4       ACTIVE   oracle@localhost (J002)       localhost             23/06/2017 13:45:27 ajs0qvdbt6qs5
SQL> /
Enter value for sid: 1428

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      1428 USER4       INACTIVE JDBC Connect Client                              site21.localhost.dmz/172.22.1.6         23/06/2017 11:00:00
SQL> /
Enter value for sid: 1728

       SID USERNAME        STATUS   PROGRAM                                          MACHINE                                  LOGON_TIME          SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
      1728 USER4       INACTIVE httpd.worker@site.joshin.com (TNS V1-V3)   site17.localhost.com                    23/06/2017 12:43:42



set linesize 20000 
set pagesize 20000 
set long 99999
select sql_id,SQL_FULLTEXT from gv$sql where SQL_ID='&SQL_ID';


SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
ajs0qvdbt6qs5 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
              = FALSE; BEGIN PcdGate_3rdPartyDigital.APIBatchSend; :mydate := next_date; IF br
              oken THEN :b := 1; ELSE :b := 0; END IF; END;



SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
bw9hk94rnvg3z UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3


###

 select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks,
 (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
 v$session s,
 v$sql sql
 where t.addr = s.taddr
 and s.sql_id = sql.sql_id
 and s.username ='&USERNAME';

Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)

Install STATSPACK

##Check if user is present##

Select * from dba_users where username='PERFSTAT';
SQL> Select * from dba_users where username='PERFSTAT';

no rows selected


###Create a tablespace###

SQL> select file_name from dba_data_files order by 1;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_01.DBF
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_02.DBF
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_03.DBF
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_04.DBF
E:\ORACLE\ORADATA\CAQ2000\SYSAUX01.DBF
E:\ORACLE\ORADATA\CAQ2000\SYSTEM01.DBF
E:\ORACLE\ORADATA\CAQ2000\UNDOTBS01.DBF
E:\ORACLE\ORADATA\CAQ2000\USERS01.DBF

8 rows selected.

SQL> create tablespace perfstat datafile 'E:\ORACLE\ORADATA\CAQ2000\PERFSTAT_01.dbf' size 1G autoextend on maxsize unlimited;

Tablespace created.

###Run the below script to install statspack##
SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password
-----------------------------------
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
------------------------------ --------- ----------------------------
CAQ2000                        PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT 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: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages


... Creating views

QL> Rem
QL>
QL> set echo off;
reating Package STATSPACK...

ackage created.

o errors.
reating Package Body STATSPACK...

ackage body created.

o errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

###Schedule the job##

SQL> conn perfstat/perfstat/

Enter password:

Connected.

SQL>

SQL>

SQL>

SQL> show user

USER is "PERFSTAT"

SQL>

SQL>

SQL> @?/rdbms/admin/spauto.sql

SQL> Rem

SQL> Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $

SQL> Rem

SQL> Rem spauto.sql

SQL> Rem

SQL> Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.

SQL> Rem

SQL> Rem    NAME

SQL> Rem         spauto.sql

SQL> Rem

SQL> Rem    DESCRIPTION

SQL> Rem         SQL*PLUS command file to automate the collection of STATPACK

SQL> Rem         statistics.

SQL> Rem

SQL> Rem    NOTES

SQL> Rem         Should be run as the STATSPACK owner, PERFSTAT.

SQL> Rem         Requires job_queue_processes init.ora parameter to be

SQL> Rem         set to a number >0 before automatic statistics gathering

SQL> Rem         will run.

SQL> Rem

SQL> Rem    MODIFIED   (MM/DD/YY)

SQL> Rem    cdialeri    02/16/00 - 1191805

SQL> Rem    cdialeri    12/06/99 - 1059172, 1103031

SQL> Rem    cdialeri    08/13/99 - Created

SQL> Rem

SQL>

SQL>

SQL> spool spauto.lis

SQL>

SQL> --

SQL> --  Schedule a snapshot to be run on this instance every hour, on the hour

SQL>

SQL> variable jobno number;

SQL> variable instno number;

SQL> begin

  2    select instance_number into :instno from v$instance;

  3    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

  4    commit;

  5  end;

  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43

SQL>

SQL>

SQL> prompt

SQL> prompt     Job number for automated statistics collection for this instance

Job number for automated statistics collection for this instance

SQL> prompt     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> prompt     Note that this job number is needed when modifying or removing

Note that this job number is needed when modifying or removing

SQL> prompt     the job:

the job:

SQL> print jobno

     JOBNO

----------

        21

SQL>

SQL> prompt

SQL> prompt     Job queue process

Job queue process

SQL> prompt     ~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~

SQL> prompt     Below is the current setting of the job_queue_processes init.ora

Below is the current setting of the job_queue_processes init.ora

SQL> prompt     parameter - the value for this parameter must be greater

parameter - the value for this parameter must be greater

SQL> prompt     than 0 to use automatic statistics gathering:

than 0 to use automatic statistics gathering:

SQL> show parameter job_queue_processes

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

job_queue_processes                  integer                           1000

SQL> prompt

SQL>

SQL> prompt

SQL> prompt     Next scheduled run

Next scheduled run

SQL> prompt     ~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~

SQL> prompt     The next scheduled run for this job is:

The next scheduled run for this job is:

SQL> select job, next_date, next_sec

  2    from user_jobs

  3   where job = :jobno;

       JOB NEXT_DATE       NEXT_SEC

---------- --------------- ------------------------

        21 02-JUN-17       13:00:00

1 row selected.

Elapsed: 00:00:00.01

SQL>

SQL> spool off;

SQL>

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

no rows selected

Elapsed: 00:00:00.03

SQL> EXECUTE statspack.snap;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.66

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

NAME          SNAP_ID Date/Time

---------- ---------- ---------------------------------------------------------

UNIORCL             1 02.06.2017:12:55:21

1 row selected.

Elapsed: 00:00:00.00

SQL>

SQL>

SQL> col WHAT for a60

SQL> col PRIV_USER for a12

SQL> col SCHEMA_USER for a12

SQL> col NEXT_DATE for a20

SQL> col INTERVAL for a25

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where job=21;

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-17            trunc(SYSDATE+1/24,'HH')             N   statspack.snap;

##To change the snap interval to 30 mins###

SQL> execute dbms_job.interval(21,'SYSDATE+(1/48)');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where job=21;

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-17            SYSDATE+(1/48)                     0 N   statspack.snap;

1 row selected.

Elapsed: 00:00:00.01

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:mi:ss';

Session altered.

Elapsed: 00:00:00.00

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where job=21;

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-2017 14:00:00 SYSDATE+(1/48)                     0 N   statspack.snap;

1 row selected.

Elapsed: 00:00:00.00

SQL> select sysdate from dual;

SYSDATE

--------------------------

02-JUN-2017 13:02:46

1 row selected.

######To schedule the purge job####

QL> variable v_jobno number;

SQL> begin

  2    dbms_job.submit(:v_jobno,

  3                    'statspack.purge(i_num_days=>;30,i_extended_purge=>;TRUE);',

  4                    trunc(sysdate+1),

  5                    'trunc(sysdate+1)',

  6                    TRUE);

  7    commit;

  8  end;

  9  /

PL/SQL procedure successfully completed.

##Check the status####

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where PRIV_USER='PERFSTAT';

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-2017 14:00:00 SYSDATE+(1/48)                     0 N   statspack.snap;

        22 PERFSTAT     PERFSTAT     03-JUN-2017 00:00:00 trunc(sysdate+1)                     N   statspack.purge(i_num_days=>;30,i_extended_purge=>;TRUE);