Parm Patram

column
select inst_id,opname, totalwork, sofar, time_remaining from gv$session_longops where totalwork > sofar order by time_remaining;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

cat /dev/raw/raw46 | od -a -N128

#!/bin/bash

#
# login to database and run a query

SQL_HOME=WHERE
LOG_HOME=WHERE
SQL_USER=WHAT
SQL_PASS=WHAT
SQL_HOST=WHAT
SQL_DB=WHAT
SQL_PORT=1521
LOGFILE=${SQL_HOME}/output.log

SQL_LOGIN_STR=”${SQL_HOME}/sqlplus -S ${SQL_USER}/${SQL_PASS}@${SQL_HOST}:${SQL_PORT}/${SQL_DB} ”

cd $SQL_HOME

RESULT=`${SQL_LOGIN_STR} << EOF
SET HEADING OFF
SELECT NAME FROM V\\$DATABASE;
exit;
EOF`

if [ $RESULT == $SQL_DB ]; then
echo `date`”: OK: $RESULT” | sed ’s/\n//g’ >> $LOGFILE
else
echo `date`”: Got Error: $RESULT” | sed ’s/\n//g’ >> $LOGFILE
fi

http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_41.shtml

set wrap on
set pages 20
set lin 200
column name format A25
column type format A15
column seq format 9999
colunn line format 9999
column pos format 999
column text format A100
select name,type,sequence seq,line,position pos,text from dba_errors where owner=’OWNER’ order by type,name;

select group#,l.sequence#,archived,l.status running, lf.status os_status,member from v$log l join v$logfile lf using(group#) order by group#;

NOTES:

• The STATUS column of v$logfile (os_status) is NOT refreshed in real-time. Information is updated on log switch.

You may need to get the block size of a datafile before running dbv (DBVERIFY) on it. The following will do this in sqlplus:

set wrap off
select t.name tablespace, file#, d.name datafile, block_size BS from v$datafile d join v$tablespace t using (ts#);

SELECT a, b, c
FROM
( SELECT a, b, c, ROWNUM rn
  FROM
  ( SELECT a, b, c FROM RECORD
    WHERE AVAIL='Y"
    ORDER BY DATE DESC
  )
  WHERE ROWNUM <= 25
)
WHERE rn >= 21;

This clunky code is the equivalent of mysql's elegant:
select a,b,c from record limit 5 offset 4;

install rlwrap (available from http://utopia.knoware.nl/~hlub/rlwrap/)

then add this to the bash_profile:

alias sqlplus=’rlwrap sqlplus’