Cleaning AUD$

Notes:
SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt (Doc ID 1362997.1)
DBMS_AUDIT_MGMT.INIT_CLEANUP Fails With ORA-46267 (Doc ID 1508787.1)

This is a procedure to clean the AUD$ table to avoid locks during 
the truncate process. Even being online process is recommended to be 
executed in a maintenance window. For small environments or non-prod you
can try execute online, without stop the application.

Script: purge_job.sql ==> Run this script as SYS database user account.

=> Please before start take the current size of AUD$ on DBA_SEGMENTS.
select sum(bytes)/(1024*1024) mb 
from dba_segments where segment_name='AUD$';

=> Create a new tablespace based on the value returned on the previous 
select.
create tablespace AUDTBS datafile '/oradata/db/audtbs1.dbf' 
size <VALUE>g autoextend on next 128m maxsize 20g;

=> Take the current number of rows, just to check after <OPTIONAL>
select count(*) from aud$;

prompt start of the script

set serveroutput on
prompt Change based on our customization done 
update dam_config_param$ set string_value='AUDTBS' 
where audit_trail_type#=1 and param_id=22;
commit;

prompt First Step: init cleanup (if not already)

BEGIN
IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 12);
else
 dbms_output.put_line('Cleanup for STD was already initialized');
end if;
end;
/

prompt revert back to default values again
update dam_config_param$ set string_value='SYSAUX' 
where audit_trail_type#=1 and param_id=22;
commit;

prompt set last archive timestamp to older than 7 days

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - 7);
end;
/

prompt setup a purge job

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

prompt call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP 
regularly to advance the last archive timestamp

create or replace 
procedure set_archive_retention (retention in number default 7) as
begin
 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 last_archive_time => sysdate - retention);
end;
/

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'advance_archive_timestamp',
job_type => 'STORED_PROCEDURE',
job_action => 'SET_ARCHIVE_RETENTION',
number_of_arguments => 1,
start_date => SYSDATE,
repeat_interval => 'freq=hourly;interval=12' ,
enabled => false,auto_drop => FALSE);
dbms_scheduler.set_job_argument_value(
job_name =>'advance_archive_timestamp',
argument_position =>1,argument_value => 7);
DBMS_SCHEDULER.ENABLE('advance_archive_timestamp');
End;
/

BEGIN
DBMS_SCHEDULER.run_job (
job_name => 'advance_archive_timestamp',use_current_session => FALSE);
END;
/

prompt End of the script


To verify the purge status and configured jobs status execute 
the following queries.

select min(NTIMESTAMP#) from aud$;
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE 
from dba_scheduler_jobs where job_name='ADVANCE_ARCHIVE_TIMESTAMP';
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE 
from dba_scheduler_jobs where job_name='STANDARD_AUDIT_TRAIL_PJ';

 

Formatting Query Results

--- The Print Table Procedure from Tom Kyte:
--- Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

Using this you can have the SPOOL vertically instead of the standard way... 

1.) Compile the procedure:

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/

2.) Example to use:

[oracle@hqsdboitt01bt / SID : kdbpp]$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 18 12:02:43 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SYS @  > set serveroutput on
SYS @  > exec print_table('select * from v$instance');
INSTANCE_NUMBER               : 1
INSTANCE_NAME                 : kdbpp
HOST_NAME                     : hqsdboitt01bt.kclx.kneip.com
VERSION                       : 11.2.0.4.0
STARTUP_TIME                  : 16-nov-2016 18:28:11
STATUS                        : OPEN
PARALLEL                      : NO
THREAD#                       : 1
ARCHIVER                      : STOPPED
LOG_SWITCH_WAIT               :
LOGINS                        : ALLOWED
SHUTDOWN_PENDING              : NO
DATABASE_STATUS               : ACTIVE
INSTANCE_ROLE                 : PRIMARY_INSTANCE
ACTIVE_STATE                  : NORMAL
BLOCKED                       : NO
-----------------

PL/SQL procedure successfully completed.

FORMAT_CALL_STACK

This function formats the current call stack. 
This can be used on any stored procedure or trigger to access the call stack. 
This can be useful for debugging.

http://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73240

1.) Procedure to execute FORMAT_CALL_STACK:

CREATE OR REPLACE PROCEDURE PR_FORMAT_CALL_STACK AS
BEGIN
  DBMS_OUTPUT.put_line('---------------------------------------------------');
  DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
  DBMS_OUTPUT.put_line('---------------------------------------------------');
END;
/

2.) Procedure to test:


create or replace procedure pr_test_stack (p_stack in char) as

cursor c1 is select * from emp;

begin

for xx in c1
loop
    
    dbms_output.put_line(xx.emp_name || ' dep id:' || to_char(xx.id_dep));
    
end loop;

    if upper(p_stack) ='Y' then
        pr_format_call_stack;
    end if;

end;
/

3.) Testing

set serveroutput on;
SQL> exec pr_test_stack('y');
emp 1 dep id:1
emp 2 dep id:1
emp 3 dep id:1
emp 4 dep id:2
emp 5 dep id:2
emp 6 dep id:2
emp 7 dep id:3
emp 8 dep id:3
emp 9 dep id:3
---------------------------------------------------
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFAE1321688         4  procedure SYS.PR_FORMAT_CALL_STACK
00007FFADFB68D80        15  procedure SYS.PR_TEST_STACK
00007FFAE12B5118         1  anonymous block

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

PL/SQL procedure successfully completed.

SQL> exec pr_test_stack('n');
emp 1 dep id:1
emp 2 dep id:1
emp 3 dep id:1
emp 4 dep id:2
emp 5 dep id:2
emp 6 dep id:2
emp 7 dep id:3
emp 8 dep id:3
emp 9 dep id:3

PL/SQL procedure successfully completed.

Checking BALANCE IO on ASM

-- Reference:
-- https://community.oracle.com/docs/DOC-995178

prompt "Checking balance IO on ASM...";

SELECT dg.group_number "GROUP#",
       dg.name,
       DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1 - df.sum_io / total_dg.total_io), -1, 0, (1 - df.sum_io / total_dg.total_io)))) "IO_BALANCED"
  FROM (SELECT d.group_number group_number,
                 SUM (ABS ((d.reads + d.writes) - tot.avg_io)) sum_io
            FROM v$asm_disk_stat d,
                 (SELECT group_number,
                           SUM (reads) + SUM (writes),
                           DECODE (COUNT (*), 0, 0, (SUM (reads) + SUM (writes)) / COUNT (*)) avg_io
                      FROM v$asm_disk_stat
                     WHERE header_status = 'MEMBER'
                  GROUP BY group_number) tot
           WHERE header_status = 'MEMBER' AND tot.group_number = d.group_number
        GROUP BY d.group_number) df,
       (SELECT group_number,
                 SUM (reads) + SUM (writes) total_io
            FROM v$asm_disk_stat
           WHERE header_status = 'MEMBER'
        GROUP BY group_number) total_dg,
        V$ASM_DISKGROUP dg
 WHERE df.group_number = total_dg.group_number
 AND df.group_number = dg.group_number;

Using LIKE on Long Data Types

-- Constraints SEARCH_CONDITION
-- Carlos Magno

create or replace function getlong_const( p_owner in varchar2,
                                          p_table_name in varchar2,
                                          p_cons_name in varchar2) return varchar2
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor,
'select search_condition from dba_constraints where owner = :x and table_name=:y and constraint_name=:z ',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_owner );
dbms_sql.bind_variable( l_cursor, ':y', p_table_name );
dbms_sql.bind_variable( l_cursor, ':z', p_cons_name );


dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
   dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getlong_const;
/

select * 
from dba_constraints 
where getlong_const(owner, table_name, constraint_name) like '%NOT NULL%';

Daily Scripts – Part 2

-- Datafile Tuning
 select vtablespace.name tablespace_name,
 vdatafile.name datafile_name,
 vfilestat.phyrds physical_reads,
 vfilestat.phywrts physical_writes,
 vfilestat.avgiotim average_time_for_io
 from v$datafile vdatafile,
 v$filestat vfilestat,
 v$tablespace vtablespace
 where vdatafile.file# = vfilestat.file#
 and vdatafile.ts# = vtablespace.ts#
 order by 3 desc ,4 desc;
-- Tuning Queries, to identify SQL commands to improve
 * DISK:
 prompt 'Gathering information by Disk ...';
 set lin 300
 set pages 10000
select exec, buff, disk, num_rows, sql_text, ROUND(buff/exec,2) "Buffer_Exec", ROUND(num_rows / exec,2) "rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Blocks per Row",
 round(disk/exec,0) Disk_Exec,address, hash
 from (
 select sum(executions) exec, sum(buffer_gets) buff, sum(disk_reads) disk, sum(rows_processed) num_rows,
 min(sql_text) sql_text, min(address) address, min(hash_value) hash
 from gv$sql
 WHERE COMMAND_TYPE <> 47
 group by address
 order by 3 desc)
 where rownum<20;
* BUFFER GET
 prompt 'Gathering information per Buffer gets ...';
select * from
 (
 select exec, buff, disk, num_rows, sql_text, round(buff/exec,2) "Buffer_Exec", round(num_rows / exec,2) "num_rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Block per Row",
 address, hash_value
 from (
 select
 sum(executions) exec,
 sum(buffer_gets) buff,
 sum(disk_reads) disk,
 sum(rows_processed) num_rows,
 min(sql_text) sql_text,
 min(address) address,
 min(hash_value) hash_value
 from gv$sql
 where COMMAND_TYPE <> 47
 group by address, hash_value
 order by 2 desc)
 where rownum<20)
 order by 8 desc;
* CPU
 prompt 'Gathering information per CPU ...';
select exec, cpu, round(cpu / exec,2) cpu_exec,buff, disk, num_rows, sql_text, round(buff/exec,2) "Buffer_Exec", round(num_rows / exec,2) "num_rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Blocks per row",
 address, hash_value
 from (
 select
 sum(executions) exec,
 sum(buffer_gets) buff,
 sum(disk_reads) disk,
 sum(cpu_time) cpu,
 sum(rows_processed) num_rows,
 min(sql_text) sql_text,
 min(address) address,
 min(hash_value) hash_value
 from gv$sql
 where COMMAND_TYPE <> 47
 group by address
 order by 4 desc)
 where rownum<20
 order by 3 desc;
-- Library Cache PIN
 set lin 100
 set timing off
 COL event FORMAT a20 TRUNC;
 tti "Users Waiting for Library Cache Pins";
SELECT sid, event, p1raw, seconds_in_wait, wait_time
 FROM sys.v_$session_wait
 WHERE
 event = 'library cache pin'
 AND
 state = 'WAITING';
tti "Object blocker ..."
 COL owner format a8
 COL object format a70
 SELECT kglnaown AS owner, kglnaobj as Object
 FROM sys.x$kglob
 WHERE kglhdadr='&P1RAW';
tti "Waiting users ..."
 col SID_SERIAL format a12
 SELECT s.sid||','||s.serial# SID_SERIAL,s.username, kglpnmod "Mode Held", kglpnreq "Request"
 FROM sys.x$kglpn p, sys.v_$session s
 WHERE p.kglpnuse = s.saddr
 AND kglpnhdl   = '&P1RAW';
set lin 1000
 set timing on
-- Long OPS
 select distinct a.inst_id, a.start_time, a.last_update_time,
 round((a.time_remaining /60 ),0) minutos_restante, round((a.elapsed_seconds /60 ),0) minutos_percorridos,
 round((a.time_remaining / (a.elapsed_seconds + a.time_remaining)) * 100,2) "% Faltante",
 decode(round((a.time_remaining /60 ),0),0,'Finalizado','Executando ...') Status,
 b.sid, b.serial#, b.username, c.address, b.program, b.module, b.action, c.address, c.sql_text, a.message, a.units
 from GV$SESSION_LONGOPS a, Gv$session b, Gv$sql c
 where a.sid=b.sid and
 a.serial#=b.serial# and
 b.sql_address=c.address(+) and
 a.time_remaining> 0;

Daily Scripts – Part 1

Below some useful scripts to help on daily tasks.
--- Wait Events:
 col event format a80;
 select count(1), decode(event,'db file scattered read',event || ' - **** FULL TABLE SCAN ****',EVENT) Event
 from gv$session_wait
 group by event
 order by 1 desc;
-- Wait Events per user:
 select distinct a.inst_id,
 B.EVENT, d.spid OSPID, A.SID, A.SERIAL#, A.USERNAME, A.STATUS, A.MACHINE, A.PROGRAM, A.MODULE, c.SQL_TEXT, c.address, b.p1,b.p1raw, b.p2, b.p2raw, b.p3, round((a.last_call_et / 60),2) Minutos_Executando
 from gv$session A, gV$SESSION_WAIT B, gv$sql c, gv$process d
 where
 A.SID=B.sid AND
 b.EVENT = '&Event' and
 a.SQL_ADDRESS=c.address(+) and
 a.paddr = d.addr
 order by username, machine;
-- Session per IO
 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 ;
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes, c.object, c.type, e.statistic_name, e.value
 FROM v$session s, v$sess_io i, v$access c, dba_objects d, v$segment_statistics e
 WHERE s.sid = i.sid and
 i.sid=c.sid and i.sid=&sid and
 c.object=d.object_name and
 c.type=d.object_type and
 e.obj#=d.OBJECT_ID and
 e.value>0
 ORDER BY 9 desc;
-- Latches
 select distinct a.inst_id,
 B.EVENT, d.name "Tipo do Latch", A.SID, A.SERIAL#, A.USERNAME, A.STATUS, A.MACHINE, A.PROGRAM, A.MODULE,
 B.P1, B.P2, c.SQL_TEXT, c.address,
 round(a.last_call_et/(60),0) Minutes
 from gv$session A, gV$SESSION_WAIT B, gv$sql c, gV$LATCH D
 where
 A.SID=B.sid AND
 b.EVENT like '%latch%' and
 a.SQL_ADDRESS=c.address(+) AND
 b.p2=d.LATCH#
 order by username, machine;
-- Free Space on Tablespaces:
 select a.tablespace_name, round((a.free_mb / b.df_size) * 100,2) free_perc , a.free_mb, b.df_size
 from
 (
 SELECT ROUND(SUM(BYTES) / (1024*1024),0) free_mb, TABLESPACE_NAME
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME
 ORDER BY 1 ASC, 2 ASC
 ) a,
 (SELECT ROUND(SUM(BYTES) / (1024*1024),0) df_size, TABLESPACE_NAME
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME
 ) b
 where a.tablespace_name = b.tablespace_name
 order by 2 asc
 /
--Archives
 select count(*), round(sum(blocks*block_size)/ (1024*1024),0)  size_MB, to_char(first_time,'hh24') HOUR
 from v$archived_log
 where trunc(first_time)=trunc(sysdate) and
 dest_id=1
 group by to_char(first_time,'hh24')
 order by 3;
select thread#, max(sequence#)
 from gv$archived_log
 where trunc(first_time)=trunc(sysdate)
 group by thread#
 /
--Archive MAP
 set pages 200
 set lines 199
 set linesize 132
 alter session set nls_date_format='dd-mon-rr';
 select * from (
 SELECT TO_DATE(first_time,'dd-mon-rr') day,
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
 TO_CHAR(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
 FROM gV$LOG_HISTORY
 where thread# = inst_id
 GROUP BY   to_Date(first_time,'dd-mon-rr')
 order by 1 desc)
 where rownum < 25;
-- Background Process
 select vb.name NOME, vp.program PROCESSNAME,
 vp.spid THREADID, vs.sid SID
 from v$session vs, v$process vp, v$bgprocess vb
 where vb.paddr <> '00' and
 vb.paddr = vp.addr and
 vp.addr = vs.paddr;
--MySession
 select username, sid, serial#, program, osuser, paddr, status
 from v$session
 where audsid
 in
 (select userenv('SESSIONID')  from dual);
--Checking PSU applied
 COLUMN   ACTION FORMAT   a20;
 COLUMN   NAMESPACE FORMAT   a11;
 COLUMN   COMMENTS FORMAT   a40;
 COLUMN   ACTION_TIME FORMAT   a30;
 COLUMN   BUNDLE_SERIES FORMAT   a30;
 COLUMN   VERSION FORMAT   a15;
prompt "Execute as SYS"
 select * from registry$history;
--Redo Information
 col member format a50
select a.thread#, a.group#, b.member,  round(bytes/1024,0) kb, a.members, a.status, b.status ST_LogFile
 from v$log a, v$logfile b
 where
 a.group#=b.group#
 order by 1,2;
SELECT name, value
 FROM v$sysstat
 WHERE name = 'redo log space requests';
prompt "Contention if > 1%";
 SELECT
 substr(ln.name, 1, 20), gets, misses, immediate_gets, immediate_misses ,
 round((misses / gets) * 100,2) taxa,
 round(immediate_misses / decode((immediate_gets+immediate_misses),0,1,(immediate_gets+immediate_misses)),2) taxa_immediate
 FROM v$latch l, v$latchname ln
 WHERE ln.name in ('redo allocation', 'redo copy')
 and ln.latch# = l.latch#;
--Active Sessions
 select distinct a.inst_id, a.server, c.spid OSPID, a.sid,a.serial#, a.username, a.osuser, d.event, a.machine, a.program, a.module, b.sql_text, b.address, round(a.last_call_et / 60) minutes
 from
 gv$session a, gv$sql b, gv$process c, gv$session_wait d
 where a.status = 'ACTIVE' and
 a.sql_address=b.address and
 a.inst_id=b.inst_id and
 a.paddr = c.addr and
 a.inst_id=c.inst_id and
 a.sid=d.sid(+) and
 a.inst_id=d.inst_id(+)
 /