Unlock Table statistics

########THEN####
SQL> col stattype_locked for a30
SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

OWNER TABLE_NAME STATTYPE_LOCKED
------------------------------ ------------------------------ ------------------------------
SCOTT TIGER ALL


1 rows selected.


SQL> exec dbms_stats.unlock_table_stats('SCOTT', 'TIGER');

PL/SQL procedure successfully completed.



####NOW#####

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

no rows selected

SQL> 

Get the SQL_ID of the statement

1. Run the sql statement.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRDWCS7   READ WRITE


2. Run below 
select * from table(dbms_xplan.display_cursor(null));


SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9czut73sxc2y4, child number 0
-------------------------------------
select name,open_mode from v$database

Plan hash value: 3039639316

----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |     1 |    14 |     0   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    14 |     0   (0)|
|   3 |   BUFFER SORT        |          |     1 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |     1 |       |     0   (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))


21 rows selected.


3. you will get the output.

SQL_ID  9czut73sxc2y4

Script to Delete Applied Archivelog from Standby ( RAC )

#!/bin/ksh
export ORACLE_SID=ORCL
export ORACLE_HOME=/app/oracle/product/12102/db
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
rm /app/oracle/dba/script/notearc.txt
sqlplus -s "/ as sysdba" << EOF
set pagesize 0
set pages 0 echo off feedback off
spool /app/oracle/dba/script/notearc.txt
select thread#,max(sequence#)-10 from gv\$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread#;
spool off;
exit;
EOF

seq1=`cat /app/oracle/dba/script/notearc.txt | awk 'NR==1{print $2}'`
seq2=`cat /app/oracle/dba/script/notearc.txt | awk 'NR==2{print $2}'`
seq3=`cat /app/oracle/dba/script/notearc.txt | awk 'NR==3{print $2}'`
echo $seq1 $seq2 $seq3
$ORACLE_HOME/bin/rman msglog /app/oracle/dba/logs/STANDBYDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF
connect target /
run{
delete noprompt archivelog until sequence $seq1 thread 1;
delete noprompt archivelog until sequence $seq2 thread 2;
delete noprompt archivelog until sequence $seq3 thread 3;
}
EOF

-------------------------------------crontab------------------------------

05 02 * * * /u01/app/oracle/cleanarc.ksh > /u01/app/oracle/clean_arc.log 2>&1

12c Create Dataguard Broker Configuration - DGMGRL for RAC

==> Pre-Check

Make sure below is set to false on primary and Standby.

#primary
SQL> sho parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

#standby
SQL> sho parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE


1. Create config files on ASM DiskGroup.

==>On primary
alter system set dg_broker_config_file1 = '+RTLDATA01/ORCL/DATAFILE/dr1ORCL.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+RTLDATA01/ORCL/DATAFILE/dr2ORCL.dat' scope=both sid='*';

P.S. Config file name has to be DB_UNIQUE_NAME

SQL> alter system set dg_broker_config_file1 = '+RTLDATA01/ORCL/DATAFILE/dr1ORCL.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2 = '+RTLDATA01/ORCL/DATAFILE/dr2ORCL.dat' scope=both sid='*';

System altered.

SQL> sho parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +RTLDATA01/ORCL/DATAFILE/dr1
                                                 ORCL.dat
dg_broker_config_file2               string      +RTLDATA01/ORCL/DATAFILE/dr2
                                                 ORCL.dat

==>On Standby
alter system set dg_broker_config_file1 = '+MDRDATA01/DRORCL/DATAFILE/dr1DRORCL.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+MDRDATA01/DRORCL/DATAFILE/dr2DRORCL.dat' scope=both sid='*';

P.S. Config file name has to be DB_UNIQUE_NAME
SQL> alter system set dg_broker_config_file1 = '+MDRDATA01/DRORCL/DATAFILE/dr1DRORCL.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2 = '+MDRDATA01/DRORCL/DATAFILE/dr2DRORCL.dat' scope=both sid='*';

System altered.

SQL> sho parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      +MDRDATA01/DRORCL/DATAFILE/d
                                                 r1DRORCL.dat
dg_broker_config_file2               string      +MDRDATA01/DRORCL/DATAFILE/d
                                                 r2DRORCL.dat


2. Start the Dataguard broker DMON process.

On Primary and Standby,

alter system set dg_broker_start=true sid='*'; 

##primary
SQL> alter system set dg_broker_start=true sid='*';

System altered.


#standby##
SQL> alter system set dg_broker_start=true sid='*';

System altered.


3.Take backup of log_archive_dest_2 and run below on primary and standby


Show parameter log_archive_dest_2


##primary
SQL> alter system set log_archive_dest_2='' scope=both sid='*';

System altered.


#standby##
SQL> alter system set log_archive_dest_2='' scope=both sid='*';

System altered.


4. Connect to DGMGRL

On Primary,

node1:/app/oracle/product/12.1.0.2/db/dbs>dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL>


5. Create configuration,


DGMGRL> CREATE CONFIGURATION '<configuration name>' AS PRIMARY DATABASE IS '<primary db_unique_name>' CONNECT IDENTIFIER IS <primary connect string>;


CREATE CONFIGURATION ORCL_DG_CONFIG AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;


DGMGRL> CREATE CONFIGURATION ORCL_DG_CONFIG AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
Configuration "ORCL_dg_config" created with primary database "ORCL"
DGMGRL>


Verify configuration,

DGMGRL> show configuration;

Configuration - ORCL_dg_config

  Protection Mode: MaxPerformance
  Members:
  ORCL - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


6. Add Standby,

DGMGRL>  add database <standby db_unique_name> as connect identifier is <standby connect string>;

ADD DATABASE DRORCL AS CONNECT IDENTIFIER IS DRORCL;



DGMGRL> ADD DATABASE DRORCL AS CONNECT IDENTIFIER IS DRORCL;
Database "drORCL" added
DGMGRL>


DGMGRL> show configuration;

Configuration - ORCL_dg_config

  Protection Mode: MaxPerformance
  Members:
  ORCL   - Primary database
    drORCL - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>


7. Enable Configuration,

DGMGRL> enable configuration;
Enabled.
DGMGRL>


6. Verify configuration,

DGMGRL>


DGMGRL> show configuration;

Configuration - ORCL_dg_config

  Protection Mode: MaxPerformance
  Members:
  ORCL   - Primary database
    drORCL - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 10 seconds ago)

DGMGRL>



DGMGRL> show database ORCL;

Database - ORCL

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORCL1
    ORCL2
    ORCL3

Database Status:
SUCCESS

DGMGRL> show database drORCL;

Database - drORCL

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 7.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ORCL1 (apply instance)
    ORCL2
    ORCL3

Database Status:
SUCCESS


#Reference Doc
12c Create Dataguard Broker Configuration - DGMGRL (Doc ID 1583588.1)
Data Guard Broker Return ORA-16664 Unable To Receive The Result From A Database (Doc ID 2138555.1)

DBA Jobs details

set markup html on spool on 
spool scheduler_check_norun.html 
set echo on 
alter session set nls_timestamp_tz_format='DD-MON-RR HH.MI.SS AM TZR'; 
alter session set nls_date_format='DD-MON-RR HH.MI.SS AM'; 
show parameter job_queue_processes 
select * from dba_scheduler_jobs; 
select * from dba_scheduler_windows; 
select * from dba_scheduler_window_details where log_date >= sysdate-10 order by log_date desc; 
select * from dba_scheduler_window_log order by log_date desc; 
select * from dba_scheduler_window_groups; 
select * from dba_scheduler_job_run_details where log_date >= sysdate-10 order by log_date desc; 
select * from dba_scheduler_job_log where log_date >= sysdate-10 order by log_date desc; 
spool off 
set markup html off 

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD'; 
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS'; 
set pagesize 9999 
spool /tmp/dba_autotask_client.html 
set markup html on 
select * from DBA_AUTOTASK_CLIENT; 
select * from DBA_AUTOTASK_CLIENT_HISTORY; 
select * from DBA_AUTOTASK_CLIENT_JOB; 
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME; 
select * from DBA_AUTOTASK_OPERATION; 
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME; 
select * from DBA_AUTOTASK_TASK; 
select * from DBA_AUTOTASK_WINDOW_CLIENTS; 
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME; 
select * from dba_scheduler_windows; 
select * from dba_scheduler_window_groups; 
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE; 
select * from DBA_SCHEDULER_JOB_LOG; 
SELECT program_name, program_action, enabled FROM dba_scheduler_programs; 
spool off

Truncate Audit Trail Table AUD$

###Take the export###

exp aud_backup/audit file=/local/oracle/UATWCS7/aud_backup.dmp log=aud_backup.log tables=SYS.AUD$ buffer=5000000

log==>
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                           AUD$  929290860 rows exported
EXP-00091: Exporting questionable statistics.

##
NAME                          GBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN
------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------
SYSTEM                        161.82     159.12        2.7      98.33       1536        157       101.35 LOCAL      MANUAL

##

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;

OWNER  SEGMENT_NAME                                           SIZE_M
------ -------------------------------------------------- ----------
SYS    AUD$~                                                  160929  ==========>>>>>THIS
SYS    FGA_LOG$~                                                 984
SYS    IDL_UB1$~                                                 256
SYS    SYS_LOB0000000394C00013$$~                                 96
SYS    SOURCE$~                                                   80
SYS    SYS_LOB0000403209C00045$$~                                 71
SYS    C_OBJ#_INTCOL#~                                            37
SYS    IDL_UB2$~                                                  32
SYS    C_TOID_VERSION#~                                           24
SYS    SYS_LOB0000001067C00003$$~                                 20

###script####

#!/bin/bash
export ORACLE_SID=ABC
export ORACLE_HOME=/app/oracle/product/11.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:$PATH
sqlplus "/as sysdba"<<-EOF
set echo on;
set serveroutput on;
set lines 200 pages 200
set time on;
set timing on;
set feedback on
spool TRUNCATE_SYS_AUD.log
Show user;
show parameter db_name
show parameter instance_number
SELECT NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME  FROM V\$DATABASE;
alter session enable parallel ddl;
alter session force parallel ddl parallel 20;
alter session set db_file_multiblock_read_count = 256;
alter session set "_px_max_granules_per_slave"=3000;
alter session set "_px_index_sampling"=3000;
alter session set "_px_dynamic_sample_size"=3000;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
alter session set parallel_force_local=TRUE;
truncate table aud$;
spool off
EOF
exit

##LOG===>

18:48:51 SQL> alter session enable parallel ddl;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session force parallel ddl parallel 20;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set db_file_multiblock_read_count = 256;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set "_px_max_granules_per_slave"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_index_sampling"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> alter session set "_px_dynamic_sample_size"=3000;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

Session altered.

Elapsed: 00:00:00.01
18:48:51 SQL> alter session set parallel_force_local=TRUE;

Session altered.

Elapsed: 00:00:00.00
18:48:51 SQL> truncate table aud$;

Table truncated.

Elapsed: 00:00:02.55
18:48:54 SQL> spool off

###
col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM' ORDER BY BLOCKS desc) where rownum < 11;


OWNER  SEGMENT_NAME                                           SIZE_M
------ -------------------------------------------------- ----------
SYS    FGA_LOG$~                                                 984
SYS    IDL_UB1$~                                                 256
SYS    SYS_LOB0000000394C00013$$~                                 96
SYS    SOURCE$~                                                   80
SYS    SYS_LOB0000403209C00045$$~                                 71
SYS    C_OBJ#_INTCOL#~                                            37
SYS    IDL_UB2$~                                                  32
SYS    C_TOID_VERSION#~                                           24
SYS    C_OBJ#~                                                    20
SYS    SYS_LOB0000001067C00003$$~                                 20

10 rows selected.

#

NAME                          GBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED EXTENT_MAN SEGMEN

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

SYSTEM                        161.82       1.96     159.86       1.21       3968        157         1.25 LOCAL      MANUAL

#

How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ (Doc ID 73408.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=240046292784578&id=73408.1&_afrWindowMode=0&_adf.ctrl-state=12uh2plej6_4

Insufficient SRLs reported by DGMGRL VALIDATE DATABASE-12c

DGMGRL> validate database 'STG_PROD';

  Database Role:    Primary database

  Ready for Switchover:  Yes

DGMGRL> validate database 'stg_prod_dg';

  Database Role:     Physical standby database
  Primary Database:  STG_PROD

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    STG_PROD:     On
    stg_prod_dg:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (STG_PROD)              (stg_prod_dg)
    1         11                      5                       Insufficient SRLs   ======>>>>>>THIS

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (stg_prod_dg)           (STG_PROD)
    1         11                      5                       Insufficient SRLs  ======>>>>>>THIS



This is because if you see the thread#, it is showing as 0 which is incorrect. it has to be 1 ( stand alone DB)


SQL> set pages 200
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13         54 ACTIVE      419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400
         0         17          0 UNASSIGNED  419430400
         0         18          0 UNASSIGNED  419430400
         0         19          0 UNASSIGNED  419430400
         0         20          0 UNASSIGNED  419430400
         0         21          0 UNASSIGNED  419430400
         0         22          0 UNASSIGNED  419430400
         0         23          0 UNASSIGNED  419430400

###

SQL> set pages 2000

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

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

         1         12          0 UNASSIGNED  419430400

         1         13          0 UNASSIGNED  419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

         0         17          0 UNASSIGNED  419430400

         0         18          0 UNASSIGNED  419430400

         0         19          0 UNASSIGNED  419430400

         0         20          0 UNASSIGNED  419430400

         0         21          0 UNASSIGNED  419430400

         0         22          0 UNASSIGNED  419430400

         0         23          0 UNASSIGNED  419430400

##Solution.

SQL> alter database drop standby logfile group 17;

alter database drop standby logfile group 17

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

##Stop the MRP on STANDBY###

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database drop standby logfile group 17;

Database altered.

SQL> alter database drop standby logfile group 18;

Database altered.

SQL> alter database drop standby logfile group 19;

Database altered.

SQL> alter database drop standby logfile group 20;

Database altered.

SQL> alter database drop standby logfile group 21;

Database altered.

SQL> alter database drop standby logfile group 22;

Database altered.

SQL> alter database drop standby logfile group 23;

Database altered.

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

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

         1         12          0 UNASSIGNED  419430400

         1         13         54 ACTIVE      419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

SQL> alter database add standby logfile thread 1 group 17 size 419430400;

Database altered.

SQL> alter database add standby logfile thread 1 group 18 size 419430400;

alter database add standby logfile thread 1 group 19 size 419430400;

alter database add standby logfile thread 1 group 20 size 419430400;

alter database add standby logfile thread 1 group 21 size 419430400;

alter database add standby logfile thread 1 group 22 size 419430400;

alter database add standby logfile thread 1 group 23 size 419430400;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

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

         1         12          0 UNASSIGNED  419430400

         1         13         54 ACTIVE      419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

         1         17          0 UNASSIGNED  419430400

         1         18          0 UNASSIGNED  419430400

         1         19          0 UNASSIGNED  419430400

         1         20          0 UNASSIGNED  419430400

         1         21          0 UNASSIGNED  419430400

         1         22          0 UNASSIGNED  419430400

         1         23          0 UNASSIGNED  419430400

12 rows selected.

Do the same on Primary as well. 

SQL> alter database drop standby logfile group 17;

alter database drop standby logfile group 18;

alter database drop standby logfile group 19;

alter database drop standby logfile group 20;

alter database drop standby logfile group 21;

alter database drop standby logfile group 22;

alter database drop standby logfile group 23;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL>

SQL>

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

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

         1         12          0 UNASSIGNED  419430400

         1         13          0 UNASSIGNED  419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

SQL> alter database add standby logfile thread 1 group 17 size 419430400;

alter database add standby logfile thread 1 group 18 size 419430400;

alter database add standby logfile thread 1 group 19 size 419430400;

alter database add standby logfile thread 1 group 20 size 419430400;

alter database add standby logfile thread 1 group 21 size 419430400;

alter database add standby logfile thread 1 group 22 size 419430400;

alter database add standby logfile thread 1 group 23 size 419430400;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

SQL>

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES

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

         1         12          0 UNASSIGNED  419430400

         1         13          0 UNASSIGNED  419430400

         1         14          0 UNASSIGNED  419430400

         1         15          0 UNASSIGNED  419430400

         1         16          0 UNASSIGNED  419430400

         1         17          0 UNASSIGNED  419430400

         1         18          0 UNASSIGNED  419430400

         1         19          0 UNASSIGNED  419430400

         1         20          0 UNASSIGNED  419430400

         1         21          0 UNASSIGNED  419430400

         1         22          0 UNASSIGNED  419430400

         1         23          0 UNASSIGNED  419430400

12 rows selected.

## Start the MRP on standby 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process , status , sequence#,thread# from v$managed_standby ;

PROCESS   STATUS        SEQUENCE#    THREAD#

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

ARCH      CLOSING              52          1

ARCH      CONNECTED             0          0

ARCH      CLOSING              53          1

ARCH      CLOSING              51          1

RFS       IDLE                 54          1

RFS       IDLE                  0          0

RFS       IDLE                  0          0

RFS       IDLE                  0          0

MRP0      APPLYING_LOG         54          1

9 rows selected.

##Now check the switchover status

DGMGRL> validate database 'STG_PROD';

  Database Role:    Primary database

  Ready for Switchover:  Yes      <-----------Ready for Switchover

DGMGRL> validate database 'stg_prod_dg';

  Database Role:     Physical standby database

  Primary Database:  STG_PROD

  Ready for Switchover:  Yes    <--------This confirms Switchover readiness

  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:

    STG_PROD:     On

    stg_prod_dg:  Off

Get explain plans of a sql query from AWR using DBMS_XPLAN.DISPLAY_AWR

set lines 2000 pages 2000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('7q4fs7yuu5fyk'));

Run SQL tuning advisor for a sql_id

Method 1.  
@?/rdbms/admin/sqltrpt.sql


Method 2. 

a. Create Tuning Task

consider sql_id is 7q4fs7yuu5fyk

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7q4fs7yuu5fyk',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '7q4fs7yuu5fyk_tuning_task11',
                          description => 'Tuning task1 for statement 7q4fs7yuu5fyk');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


b. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '7q4fs7yuu5fyk_tuning_task11'); 


c. Get the Tuning advisor report.

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('7q4fs7yuu5fyk_tuning_task11') from dual;

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';