Quick Tips for Oracle DBAs

PROBLEM #1 : Need to set the environment each time you login to SQLPLUS?

SOLUTION: Edit login.sql or gloging.sql and enter the setting you need.

This will be used each time you login. 

 vi  $ORACLE_HOME/sqlplus/admin/glogin.sql

SET FEEDBACK OFF

SET TERMOUT OFF

COLUMN X NEW_VALUE Y

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> " or  SELECT LOWER(USER || '@' || SYS_CONTEXT('userenv', 'instance_name')) X FROM dual;

SET SQLPROMPT '&Y> '

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

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

SET TERMOUT ON

SET FEEDBACK ON

SET LINESIZE 100

SET TAB OFF

SET TRIM ON

SET TRIMSPOOL ON

PROBLEM #2: Finding the folder sizes in Linux/Solaris

SOLUTION:du -h -s *

2.2G   AUDRPRD

3.6M   AUDRPROD

  11M   Backup

  80G   E1PROD

120K   E1PROD_RESTORE

   0K   lost+found

232M   SOMEPROD

PROBLEM #3: Unix command out is in bytes and it is hard to covert in readable format, try using h as an attribute.

SOLUTION: ls –alt OR ls –alth

ls –alt

drwxr-xr-x   2 oracle   dba        22016 Jul  7 15:27 SOMEPROD

drwxr-xr-x   2 oracle   dba        18944 Jul  7 15:17 AUDRPROD

ls –alth

drwxr-xr-x   2 oracle   dba        22kb Jul  7 15:27 SOMEPROD

Satheesh Shanmugan, Database Administrator
Myriad IT