Object references

select * from dba_dependencies where referenced_name='xyz'

Session and lock details in oracle

SELECT sess.process,sess.BLOCKING_SESSION, sess.SID, sess.serial#, sess.status, sess.action,
       sess.BLOCKING_SESSION_STATUS, sess.state, sess.username, sess.schemaname,
       sql.sql_text
  FROM v$session sess, v$sql SQL
 WHERE sql.sql_id(+) = sess.sql_id
   AND sess.type = 'USER';

Get details on Locks

SELECT a.session_id, a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER",
       b.object_name, b.object_type, a.locked_mode
  FROM (SELECT object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE
           FROM v$locked_object) a,
       (SELECT object_id, owner, object_name, object_type FROM dba_objects) b
 WHERE a.object_id = b.object_id;

Unattended SFTP

To do unattended SFTP, say for a cron job.
Do the following:

• On the source machine, Go to home/.ssh folder
• Run ssh-keygen -trsa
• Do not enter any password when prompted
• cat **.pub
• copy the public key
• On the destination machine: Go to home/.ssh
• vi authorized_keys, and paste the public key

Importantly: the access rights need to be set

• .ssh folder has to be drwxr-xr-x
• authorized_keys has to be -rw——-
• *.pub has to be -rw-r–r–

Alter Materialized view in Oracle

Apparently, there is no alter script for materialized view. You need to drop/recreate it. But there seems to be a simpler way.

Check out this link. 

SFTP EOF bash

I had this script where I performed SFTP on a linux box. The issue I had was that after SFTP was done, the remaining commands in the script did not get executed.

sftp $USER@$REMOTEHOST << EOF
mget $REMOTEPATH/$FILENAME $LOCALPATH
bye
EOF
echo “Unzipping “$LOCALPATH/$FILENAME >> $LOGFILE
gzip -df $LOCALPATH/$FILENAME

The gzip or the echo after EOF never got executed. Apparently, the problem was that the EOF, shouldnt have a any leading or trailing spaces. It should be at the start of the line.

Anyway, EOF means, hey , whatever command.. from now on, all the commands you get, treat them as command line instructions until you meet EOF again.