Document ID: 1868_1 Subject: COMMON UNIX COMMANDS WITH ORACLE Last Modified: 03 October 1994 Author: Raghu Viswanathan The purpose of this bulletin is to acquaint Unix users to some of the commands 'often' used in conjunction with Oracle. You will probably find yourself using these commands at some point in time, especially if you are a database administrator. If you currently work on Unix platforms, you will recognize many of the commands. I have tried to keep this list as comprehensive as possible to give everyone a good start. The options mentioned with some of the commands are the more common ones used when using oracle. In certain places you will either see (ucb) or (att) to denote differences between the Berkeley and System V universes. Please acquaint yourself with equivalent commands on your unix box if needed. A list of shell variables used with oracle is also provided at the end of this document. Disclaimer: This bulletin is not intended to replace any documentation of any kind but is merely a 'guide'. Whenever in doubt, please refer to the manual pages on your operating system. ---------------------------------------------------------------------- -- 1. man - manual pages options: man 1 ls (ucb) man 6 hangman (ucb) man ls (att or ucb) 2. passwd - changing passwords 3. date - system date 4. who - lists all users logged in who am i - lists who you are (ucb) 5. cal - displays the calendar examples: cal 1990 cal 6 1990 6. pwd - present working directory 7. cd - change directory examples: cd (go to home directory) cd .. (move to the parent directory) cd . (stay in the current directory) cd /usr/bin (changing to a different directory) 8. ls - list contents options: ls -l (long listing) ls -lg (long listing including group) ls -ld (long listing of directory) ls -a (list dot files) ls -lt (sort files by timestamp - useful for oracle trace files) ls -L (listing soft links) 9. more - list a file a screenful at a time example: more tempfile Note: On many att machines you may have to use pg instead 10. cat - continuous listing options: cat -b (ucb - lists line numbers) cat -vt (lists hidden tabs as ^I and formfeeds as ^L) 11. mkdir - make a new directory options: mkdir -p (create intermediate directories also) 12. mv - move a file options: mv -i (prompt in case you are overwriting a file) mv -f (override prompt) 13. cp - copy a file options: cp -i (prompt in case you are overwriting a file) cp -f (override prompt) cp -r (recursive copy for directories) 14. rm - remove a file options: rm -i (prompt before deletion) rm -f (override prompt) rm -r (remove files recursively - useful to remove non-empty directories) 15. rmdir - remove an empty directory 16. chmod - change the permissions mode of a file. examples: chmod +x tempfile (add execute permission) chmod u+x tempfile (add execute for user only) chmod 400 tempfile (change permissions explicitly) chmod 4755 oracle (set the setuid bit on) Note: By setting the setuid bit, other processes can run with an effective uid to be that of the owner of the file. 17. grep - search a file for a string or expression. options: grep -n (print line numbers) grep -v (print lines that don't contain pattern) grep -i (ignore case sensitivity) 18. find - find files by name or by other characteristics example: find . -name sqlplus -print (find the full pathname of sqlplus starting from the current directory) 19. wc - word count options: wc -l (line count) wc -w (word count) wc -c (character count) 20. id - print name, ID, group and group ID 21. ps - display process status options: ps -aux (ucb) ps -ef (att) 22. kill - send a signal to terminate a process. options: kill -9 (signal will always be caught) Note: /usr/include/signal.h contains list of legal signals. 23. df - disk space on file systems. 24. du - disk usage in blocks options: du -s (prints total usage in blocks of directory) du -a (prints usage for each file recursively) Note: a directory itself occupies one block. 25. lpr - send a job to the printer options: -Pprintername (printername is the name of the printer) -h (suppress banner page) -# (number of copies) 26. uname - prints release information options: uname -a 27. nm - print name list Note: T = text U = undefined example: nm libpls.a | grep pcidcl (this is a symbol in libpls.a on some ports) 28. ar - create library archives, add or extract files options: ar d (delete archive) ar x (extract archive) ar t (list contents of archive) ar l (divert temp files to current directory instead of /tmp) example: ar x libpls.a plsima.o (extracts plsima.o from libpls.a and places it in the current directory - the .o file is still in the archive. To delete it, use the d option) 29. ranlib - makes table of contents for an archive 30. ipcs - interprocess communication facilities status options: ipcs -s (print semaphore information) ipcs -m (print shared memory information) ipcs -q (print message queue information) ipcs -b (print size information) Note: SEGSZ - max shared memory size NSEMS - number of semaphores in set. 31. ipcrm - delete ipc facilities options: ipcrm -s ipcrm -m 32. logname - lists login id from env variable LOGNAME 33. hostname - lists host name (ucb) 34. chown - change ownership examples: chown joe myfile Note: In ucb, only root can run this command In att, either root or owner of file can run it Suns have chown -R (for recursive chown of directories) 35. chgrp - change group examples: chgrp hisgroup myfile 36. newgrp - new group Notes: Switch group to that specified. Password may be required. Creates a new shell. 37. file - lists type of file example: file sqlplus (run on a sun) sqlplus: sparc demand paged executable not stripped Note: the type of file may be misleading. 38. ln - links options: ln -s (create a softlink - saves space) 39. su - super-user or switch user 40. dd - file conversion and copy utility example: dd if=myfile of=newfile conv=ucase This converts all lower case letters in myfile to uppercase and puts the results into newfile. Note: Useful with raw devices. 41. diff - file differences. 42. umask - sets default permissions for new files and directories 43. stty - terminal settings options: stty -a (att) stty all (ucb) example: stty erase h (resets erase character to 'h') 44. tty - lists terminal 45. cpio - copy file archives options: cpio -icBdvmu (i = input, c = file header information B = blocking d = create recursive directories v = verbose m = retain modification time u = unconditional) 46. tar - tape archives options: tar xvt (x = extract t = list contents v = verbose) 47. telnet - use TELNET protocol to access another machine 48. rlogin - remote login options: rlogin hostname -l accountname 49. echo - echo command 50. ulimit - (att) - defines the max size of files on some systems. 51. vmstat - report virtual memory statistics. 52. pstat - do determine resource such as swap etc .. options: pstat -t 53. make - this is a command generator. All executables used in oracle are generated from makefiles. Although an understanding of make is not required, it would help to pick up some simple information about makefiles. 54. env - list environment variables (printenv on some machines.) The following list discusses some of the important ones. Environment Variables --------------------- HOME - home directory USER - userid TERM - terminal setting PATH - search path for files LOGNAME - login id PWD - present directory EDITOR - default editor HOST - host name SHELL - current shell (csh = C-shell, sh = Bourne shell, ksh = Korn sh ell) TZ - time zone variable ORACLE_HOME - oracle home directory ORACLE_SID - oracle system identifier ORACLE_LPPROG - print command for oracle applications ORACLE_LPARGS - options to above print command ORAKITPATH - path for resource files. Recall to reset an environment variable: In C-shell: setenv TERM vt100 In Bourne shell: TERM=vt100; export TERM ============ Oracle 8i 9i SQL Scripts and Database Commands Below are some Handy Dandy SQL Scripts Used on a regular basis. You might find them helpful in your daily activities. If you need help or are interested in remote DBA services please Contact Us. Check out the the Books/Manuals that we use Regularly. Also, feel free to check out our Solaris UNIX page or the SQL Server page. Thanks for coming by! Bill Brown Security Grants Resizing A Data File Show All Product Information Show Row Counts For All tables That Have ANALYZE On Select All Users Active In The system Show What a Current User Is Doing Create Count For All Tables Show All Indexes Show All Tables Show Space Used Sum Space By Owner Sum Space By Tablespace Show Reads And Writes By File Name in Oracle DB Show Versions Of Software Identify Segments That Are Getting Close To Their Max-Extent Values Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space Displays Archived Redo Log Information Display Count Historical Archived Log Information From The Control File Shows Current Archive Destinations Backups of Archived Logs Display All Online Redo Log Groups For The Database Show All Datafiles For Tablespace And Oracle Stuff Security Grants grant select on PERSON_TABLE to public with grant option; select * from dba_tab_privs where TABLE_NAME = 'PERSON_TABLE' select * from dba_role_privs where granted_role = 'PORTMAN_TABLE' Resizing A Data File alter database datafile '/u04/oradata/wpk/temp01.dbf' resize 500m; Show All Product Information select * from product_component_version; Show Row Counts For All Tables That Have ANALYZE On select owner table_name, num_rows from dba_tables where num_rows > 0 Select All Users Active In The System select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username Show What A Current User Is Doing select sid, serial#, status, server from v$session where username = 'BROWNBH'; Create Count For All Tables select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name Show All Indexes select owner, index_name, table_type, tablespace_name from dba_indexes where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, index_name, tablespace_name Show All Tables select owner, table_name, table_type, tablespace_name from dba_all_tables where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, table_name, tablespace_name Show Space Used select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' Sum Space By Owner select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner Sum Space by Tablespace select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by tablespace_name Show Reads And Writes By File Name In Oracle DB select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file# Show Versions Of Software select * from V$VERSION Identify Segments That Are Getting Close To Their Max-Extent Values select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents Identifies Segments That Are Getting Close To Running Out Of Contiguous Free Space select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes) Displays Archived Redo Log Information select * from v$database Display Count Historical Archived Log Information From The Control File select count(*) from v$archived_log select min(completion_time) from v$archived_log Shows Current Archive Destinations select * from v$archive_dest Backups Of Archived Logs select count(*) from v$backup_redolog Display All Online Redo Log Groups For The database select * from v$log Show All Datafiles For Tablespace And Oracle Stuff select * from dba_data_files order by tablespace_name, file_name ============ Versions of Solaris Explained SunOS is the core operating system comprising the kernel, utilities and basic libraries. Solaris is the broader environment comprising SunOS, OpenWindows and networking support. In other words, SunOS is a component of Solaris. SunOS and Solaris relate to each other as follows: For example, when one does a 'uname -a' this reports that the server has SunOS 5.6 installed which means that it actually got Solaris 2.6 installed. . SUN OS Version Is Solaris Version SunOS 5.4 Solaris 2.4 SunOS 5.5 Solaris 2.5 SunOS 5.5.1 Solaris 2.5.1 SunOS 5.6 Solaris 2.6 SunOS 5.7 Solaris 2.7 (or 7) SunOS 5.8 Solaris 2.8 (or 8) How Can we tell Solaris OS is running 32-bit or 64-bit? Use the isalist command to determine whether the machine is running the 32-bit or 64-bit operating system. If you are running the 64-bit operating system on an UltraSPARC machine, then isalist will list sparcv9 first How to boot in 64/32 bit mode? To boot a 32-bit kernel, at the ok prompt type: ok boot [disk or net] kernel/unix To boot a 64-bit kernel (default), at the ok prompt type: ok boot [disk or net] kernel/sparcv9/unix ok boot [disk or net] Run job in batch now: at -s now < thejob.sh Show current process active ps -efa Show process information psrinfo -v Show version of unix uname -a Display System Configuration sysdef or prtconf Print VTOC prtvtoc /dev/dsk/c0t0d0s0 Query Disk space df -k disk space in kilobytes du -sk disk space summary in kilobytes How To Configure Sun 450 Hot swap disk drives 1. drvconfig 2. disks Remove all files and sub-directories rm -r * Move all files from one directory to another using tar pipe from directory /var mkdir /var1 cd /var tar cf - . | (cd /var1 && tar xBf -) Directory compare (don't' show files that are the same) dircmp -s /var /var1 Give User execute permission on a file chomod u+x filename gives execute permission to the owner. Find command to find in current directory and sub directory find . -name "dbmslogmnr.sql" -print DATE Command date mmddHHMM[[cc]yy] example "date 022610221998" Get DATE from another unix box rdate pluto Find Command for certain size files find . -size +10000c This example say find all the file > 10000 bytes. Find command to find a word in the directory and sub directory find . -exec grep -ls pkzip {} \; ================== Five Timesaving Unix Commands for Oracle DBAs by Donald K. Burleson 01/15/2001 My book Unix for Oracle DBAs Pocket Reference is the result of my trying for more than 20 years to memorize all of the different Unix commands that a DBA (database administrator) is required to know. As a consultant, I am asked to tune databases on every dialect of Unix, and it is a constant challenge to remember the commands that I need to perform my work. This article presents a sampling of my favorite scripts from the book. A "Change All" Command for Unix The script shown in this section does a search and replace in all files in a directory, replacing one string with another. If I have a Unix directory with hundreds of files and I need to change the ORACLE_SID in each file, this script can make all the changes in seconds. It also creates a backup directory with the original contents of every file that it changed. This script has saved me hundred of hours of tedious editing. #!/bin/ksh tmpdir=tmp.$$ mkdir $tmpdir.new for f in $* do sed -e 's/oldstring/newstring/g'\ < $f > $tmpdir.new/$f done # Make a backup first! mkdir $tmpdir.old mv $* $tmpdir.old/ cd $tmpdir.new mv $* ../ cd .. rmdir $tmpdir.new The for loop that you see in the script causes the sed command to be executed for each file in the current directory. The sed command does the actual search and replace work, and at the same time writes the new versions of any affected files to a temporary directory. To use this script, place the code shown here into a file named chg_all.sh. Whenever you want to make a global change, start by editing the old and new strings in the script file. Then, when executing the script, pass in a file mask as an argument. For example, to only change SQL files, you would execute the command like this: root> chg_all.sh *.sql When the script completes, the string substitution that you requested will be done, and you'll be left with a directory named tmp.old, which contains the original versions of any modified files. A Script to Check Oracle Values on Hundreds of Databases One thing I always needed for Unix was a way to run the same SQL*Plus command on every database, and even databases on other servers. I had a manager who wanted to know the default optimizer mode for every database at a shop that had over 150 databases on 30 database servers. The manager allotted me two days for this task, and he was quite surprised when I provided the correct answer in ten minutes. I did it using the following script: Related Reading Unix for Oracle DBAs Pocket Reference By Donald K. Burleson Table of Contents Index Read Online--Safari # Loop through each host name . . . for host in `cat ~oracle/.rhosts|\ cut -d"." -f1|awk '{print $1}'|sort -u` do echo " " echo "************************" echo "$host" echo "************************" # loop from database to database for db in `cat /etc/oratab|egrep ':N|:Y'|\ grep -v \*|grep ${db}|cut -f1 -d':'"` do home=`rsh $host "cat /etc/oratab|egrep ':N|:Y'|\ grep -v \*|grep ${db}|cut -f2 -d':'"` echo "************************" echo "database is $db" echo "************************" rsh $host " ORACLE_SID=${db}; export ORACLE_SID; ORACLE_HOME=${home}; export ORACLE_HOME; ${home}/bin/sqlplus -s /< /tmp/msg$$ # This script is intended to run starting at # 7:00 AM EST Until midnight EST cat /tmp/msg$$|sed 1,4d | awk '{ \ printf("%s %s %s %s %s %s %s\n", $1, $6, $7,\ $14, $15, $16, $17) }' | while read RUNQUE\ PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU\ IDLE_CPU WAIT_CPU do $ORACLE_HOME/bin/sqlplus -s / <