How to find the last time a session performed any activity?
In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database.
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
..
Explain plan execution plan
Explain plan may not relay the correct execution plan optimizer may run, the following link shows an example
Explain Plan Lies
whereiz – find all version of a command
Using the following script it will find all version of a command in $PATH (from Unix Power Tools)
$ 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
Run a logout script in k-shell
By setting trap one can run a logout script when a k-shell is being terminated. The built-in command trap in k-shell allows one to customize shell when a signal is received.
# Set to run $HOME/.logout script when shell is terminated
$ trap ‘. $HOME/.logout’ 0
# display list of signals setup
$ trap
trap — ‘. $HOME/.logout’ EXIT
How to find permissions granted on user in a database?
Run the following SQL to find permissions granted on users on the database
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
