Donald K. Burleson Oracle Tips

Handy UNIX Tips and Tricks for Oracle

While there are hundreds of UNIX commands for managing Oracle, there are a few UNIX tricks that are especially useful for the Oracle professional.  For a complete listing of UNIX commands for Oracle, see the Oracle Press book “Oracle9i UNIX administration Handbook” by Donald K. Burleson.

Locate a file anywhere on the UNIX server - You can use the UNIX find command to locate a particular file.  Please note that in chapter 8 we extend this command to search for all files that contain specific strings:

root> pwd

/
root> find . -print|grep –i dbmspool.sql

./oracle/product/9.1.2/rdbms/admin/dbmspool.sql  

Locate Oracle files that contain certain strings - This is one of the most common shell command for finding all files that contain a specified string.  For example, assume that you are trying to locate a script that queries the v$process table.  You can issue the following command, and UNIX will search all subdirectories, looking in all files for the v$process table.

root> find . -print|xargs grep v\$process

./TX_RBS.sql:        v$process p,
./UNIX_WHO.sql:from     v$session a, v$process b
./session.sql:from v$session b, v$process a

Locate recently created UNIX files - The following command is useful for finding UNIX files that have been recently added to your server.  Part of the job of the Oracle DBA is monitoring the background_dump_dest and the user_dump_dest for trace files, and purging trace files that are no longer required.

The following command lists all files that were created in the past two weeks:

root> find . -mtime -14 -print
.
./janet1_ora_27714.trc
./janet1_ora_27716.trc

Finding large files on a UNIX server - The following command is very useful in cases where a UNIX file system has become full.  As we may know, Oracle will hang whenever Oracle must expand a tablespace and Oracle cannot extend the UNIX filesystem. The script below will display all files that are greater than one megabyte in size.  Note that the size parameter is specified in K-bytes.

root> find . -size +1024 –print

./prodsid_ora_22951.trc

Get list of Oracle products installed in the current $ORACLE_HOME – This command is useful for locating all installed products within your Oracle environment.

cat $ORACLE_HOME/unix.prd |\
nawk -F\" '{ printf ("%-40s %-10s %-20s\n", $6, $2, $4) }' |\
grep '^[^ ]'

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.