Monitor and instrument long runing query

With this query using the v$session_longops view, you can view any SQL statement that executes for more than 6 seconds.

select  s.inst_id,
        SQL.SQL_TEXT as "OPERATION",
        to_char(START_TIME, 'dd/mm/yyyy hh24:mi:ss') Start_Time,
        to_char(LAST_UPDATE_TIME, 'dd/mm/yyyy hh24:mi:ss') Last_Update_Time,
        round(TIME_REMAINING/60,1) as "MINUTES_REMAINING",
        round((SOFAR/TOTALWORK) * 100,2) as PCT_DONE
from    gv$session s, 
        gv$sqlarea sql, 
        gv$session_longops op
where     
        s.sid=op.sid
and     s.sql_id = sql.sql_id
and     s.sid = op.sid
and     s.status  = 'ACTIVE'
and     op.totalwork > op.sofar
and     s.sid not in (select distinct sid from gv$mystat where rownum < 2)
order by 4 desc;

 

The SET_SESSION_LONGOPS procedure of the DBMS_APPLICATION_INFO package can be used to publish your application information about the progress of long operations.
You can insert and update rows in the v$session_longops view:

DECLARE
        rindex    BINARY_INTEGER;
        slno      BINARY_INTEGER;
        totalwork number;
        sofar     number;
        obj       BINARY_INTEGER;
 
      BEGIN
        rindex := dbms_application_info.set_session_longops_nohint;
        sofar := 0;
        totalwork := 10;
 
        WHILE sofar < 10 LOOP
          -- update obj based on sofar
          -- perform task on object target
 
          sofar := sofar + 1;
          dbms_application_info.set_session_longops(rindex, slno,
            "Operation X", obj, 0, sofar, totalwork, "table", "tables");
        END LOOP;
      END;

 

After instrumentation, v$session_longops view is populated with these informations:

SELECT 
   opname,
   target_desc,
   sofar,
   totalwork,
   time_remaining,
    units
FROM   
   v$session_longops;

 

Author: Vincent Fenoll, Oracle DBA Montreal
Compatibility: Oracle 10g, 11g, 12c

Posted in SQL

For each column, count a value

For each column of the table “my_table”, I want to count how many rows containing “my_value_or_string” there are in the table.

In the script, change “my_table” and “my_value_or_string”.

BEGIN
  declare
      cursor c_my_table is
      select COLUMN_NAME
        from dba_tab_columns
       where TABLE_NAME = 'my_table';
  
    ch_column_name dba_tab_columns.COLUMN_NAME%TYPE;
    n_count        number(20);
    sql_stmt       VARCHAR2(200);
  
  BEGIN
  
    open c_my_table;
    LOOP
      FETCH c_my_table
        INTO ch_column_name;
    
      EXIT WHEN c_my_table%NOTFOUND;
      begin
        sql_stmt := 'select count(*) from my_table where ' ||
                    ch_column_name || '=my_value_or_string';
      
        execute immediate sql_stmt
          into n_count;
        dbms_output.put_Line(ch_column_name || ',' || n_count);
      
      exception
        when others then
          dbms_output.put_Line(ch_column_name || ',' || 'N/A');
        
      end;
    
    END LOOP;
    CLOSE c_my_table;
  
  END;
END;
/

Author: Vincent Fenoll, Oracle DBA Montreal

Trace a specific ORA- error

How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.

For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows

This time, the alert file is not very helpful.

To determine the root cause, event 1422 can be set as follows:

connect /as sysdba 
alter system set events '1422 trace name ERRORSTACK level 3'; 

[…reproduce the issue…]

The event can be turned off again using:

connect /as sysdba

alter system set events '1422 trace name ERRORSTACK off';

In the event 1422 trace file, below sql query was causing error.

*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)

Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;

In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.

The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.

 
Author: Vincent Fenoll, Oracle DBA Montreal

Generate and format trigger DDL

How to format DBMS_METADATA.GET_DDL?

To get a formatted definition of your DDLs, with all the semicolons and avoid lines break (Wrapped lines).
Here is an example to retrieve the DDL of a triggers using the DBMS_METADATA package.

For Oracle >=10:

COLUMN Text FORMAT a2480 WORD_WRAPPED

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

REM Without use of spool, remove "REM"
REM set term on

REM With use of spool (no display on stdout)
set term off


BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/


spool ddl_schema_trigger.sql
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'trigger_name', 'schema_name') Text FROM SYS.DUAL;
spool off

set feed on echo on head on pages 14 term on

REM *****************************************
REM New script ======> ddl_schema_trigger.sql
REM *****************************************

Other useful options you can use:

exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘STORAGE’,false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘SEGMENT_ATTRIBUTES’,false);

The generate all the DDL for a schema it’s here.

Dataguard management without dgbroker

Standby stop start apply log

I want to start or stop redo apply without dgbroker. I also want to know other queries to check Dataguard and standby status
 
 

Starting Redo Apply on standby database

Without Real Time Apply (RTA) on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

With Real Time Apply (RTA)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 
 

Stopping Redo Apply on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Monitoring Redo Apply on Physical Standby Databases

 

Last sequence received and applied

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

 

Standby database process status

select distinct process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

 

If using real time apply

select TYPE, ITEM, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') from v$recovery_progress where ITEM='Last Applied Redo';

 

Author: Vincent Fenoll (Oracle DBA in Montreal)