SELECT trigger_name = name
, trigger_owner = USER_NAME(uid)
, table_name = OBJECT_NAME(parent_obj)
, isinsert = OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’)
, isupdate = OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’)
, isdelete = OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’)
, isafter = OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’)
, isinsteadof = OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’)
, status = CASE OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) WHEN 1 THEN ‘Disabled’ ELSE ‘Enabled’ END
FROM sysobjects
WHERE type = ‘TR’

Output:
tr_test1_insert dbo test1 1 0 0 1 0 Enabled

So using the following SQL we found the session had reached the max limit of cursors (open_cursors) and thereby found the SQL which was causing the issue and
SQL> select s.username, s.sid, a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
and s.username = 'SCOTT';

USERNAME SID COUNT(1)
—————————— ———- ———-
SCOTT 143 17
SCOTT 89 1000

To display the current limit set in the database
SQL> show parameter open_cursors

NAME TYPE VALUE
———————————— ———– ——-
open_cursors integer 1000

So using V$open_cursor and joining with V$session we were able to find the SQL that wasn’t closing the cursor.
select vo.sql_id, count(1) from v$open_cursor vo, v$session vs
where vs.sid = vo.sid and vs.username = 'SCOTT'
group by vo.sql_id order by count(1);

SQL_ID COUNT(1)
————- ———-
…
9gs5t9bj7q686 21
2na94tfc9ukuk 1000

SQL> select sql_text from v$sqltext where sql_id = ’2na94tfc9ukuk’;

SQL_TEXT
—————————————————————-
select * from table1

set serveroutput on format wrapped
DECLARE
l_vc2_table_owner VARCHAR2(30) := upper('&1');
l_vc2_table_name VARCHAR2(30) := upper('&2');

PROCEDURE get_ind_columns(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2, l_vc2_pad IN VARCHAR2
, l_vc2_out_table_key OUT VARCHAR2)
IS
CURSOR l_cur_ind_columns IS
SELECT column_name FROM dba_ind_columns
WHERE index_owner = l_vc2_owner AND index_name = l_vc2_index_name
ORDER BY column_position;
l_vc2_columns VARCHAR2(100);
BEGIN
FOR l_rec IN l_cur_ind_columns
LOOP
IF l_vc2_columns IS NULL THEN
l_vc2_columns := l_rec.column_name;
ELSE
l_vc2_columns := l_vc2_columns || ' ' || l_rec.column_name;
END IF;
END LOOP;
l_vc2_out_table_key := ' (' || l_vc2_columns || ')';
END;

PROCEDURE print_pk(l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2, l_vc2_pad IN VARCHAR2) IS
CURSOR l_cur_main IS
SELECT di.owner, di.index_name
FROM dba_indexes di, dba_constraints dc
WHERE di.table_owner = l_vc2_table_owner and di.table_name = l_vc2_table_name
AND dc.owner = di.owner and dc.constraint_name = di.index_name
AND di.uniqueness = 'UNIQUE';
l_rec_main l_cur_main%ROWTYPE;
l_vc2_ind_columns VARCHAR2(1000);

FUNCTION check_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2)
RETURN BOOLEAN
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;
l_b_status BOOLEAN;
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
l_b_status := TRUE;
ELSE
l_b_status := FALSE;
END IF;
CLOSE l_cur_fk;
RETURN l_b_status;
END;

PROCEDURE print_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2,
l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2,
l_vc2_pad IN VARCHAR2)
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;

CURSOR l_cur_dcc(l_vc2_owner IN VARCHAR2, l_vc2_constraint_name IN VARCHAR2) IS
SELECT column_name FROM dba_cons_columns
WHERE owner = l_vc2_owner AND constraint_name = l_vc2_constraint_name
ORDER BY position;

l_vc2_cons_columns VARCHAR(500);
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
WHILE l_cur_fk%FOUND
LOOP
l_vc2_cons_columns := '';
FOR l_rec_dcc IN l_cur_dcc(l_rec.owner, l_rec.constraint_name)
LOOP
IF l_vc2_cons_columns IS NULL THEN
l_vc2_cons_columns := l_rec_dcc.column_name;
ELSE
l_vc2_cons_columns := l_vc2_cons_columns || ' ' || l_rec_dcc.column_name;
END IF;
END LOOP;
dbms_output.put_line(l_vc2_pad || ' Table FK: ' || l_rec.owner || '.' || l_rec.table_name
— || ' ' || l_rec.constraint_name
|| ' (' || l_vc2_cons_columns || ')');
print_pk(l_rec.owner, l_rec.table_name, l_vc2_pad || ' ');

FETCH l_cur_fk INTO l_rec;
END LOOP;
END IF;
CLOSE l_cur_fk;
END;

BEGIN
OPEN l_cur_main;
FETCH l_cur_main INTO l_rec_main;
IF l_cur_main%FOUND THEN
WHILE l_cur_main%FOUND
LOOP
IF check_fk(l_rec_main.owner, l_rec_main.index_name) THEN
l_vc2_ind_columns := '';
get_ind_columns(l_rec_main.owner, l_rec_main.index_name, l_vc2_pad, l_vc2_ind_columns);
dbms_output.put_line(l_vc2_pad || 'Table: ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' ' || l_vc2_ind_columns);
print_fk(l_rec_main.owner, l_rec_main.index_name, l_vc2_table_owner, l_vc2_table_name, l_vc2_pad);
END IF;
FETCH l_cur_main INTO l_rec_main;
END LOOP;
END IF;
END;
BEGIN
print_pk(l_vc2_table_owner, l_vc2_table_name, '');
END;
/

Output:
Table: SCOTT.TABLE1 (TABLE1_KEY)
Table FK: SCOTT.TABLE2 (TABLE2_FKEY)
Table: SCOTT.TABLE2 (TABLE2_KEY)
Table FK: SCOTT.TABLE3 (TABLE3_FK)

select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null — to ignore background process
order by last_call_et;

USERNAME Minutes STATUS
—————————— ———- ——–
SYS 0 ACTIVE
SCOTT 0 INACTIVE
SYSTEM 34 INACTIVE
..

$ cat /tmp/whereiz
#!/bin/sh
testx="test -x"

fixpath="`echo $PATH | sed \
-e 's/^:/.:/' \
-e 's/::/:.:/g' \
-e 's/:$/:./'`"

# echo $fixpath
# IFS has a colon, space and a tab
IFS=": "
for command
do
where=""
for direc in $fixpath
do $testx $direc/$command && where="$where $direc/$command"
done
case "$where" in
?*) echo $where ;;
esac
done

Example
$ /tmp/whereiz ls grep
/bin/ls /tmp/ls
/bin/grep

# Set to run $HOME/.logout script when shell is terminated
$ trap ‘. $HOME/.logout’ 0
# display list of signals setup
$ trap
trap — ‘. $HOME/.logout’ EXIT

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id
inner join sysusers objectowner on o.uid = objectowner.uid
WHERE u.name = 'username'
order by o.name, u.name