Friday, March 24, 2006
COMMON UNIX COMMANDS WITH ORACLE
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
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
Unix commands reference card
Unix commands reference card
Environment Control
Command Description
cd d Change to directory d
mkdir d Create new directory d
rmdir d Remove directory d
mv f1 [f2...] d Move file f to directory d
mv d1 d2 Rename directory d1 as d2
passwd Change password
alias name1 name2 Create command alias (csh/tcsh)
alias name1="name2" Create command alias (ksh/bash)
unalias name1[na2...] Remove command alias na
ssh nd Login securely to remote node
exit End terminal session
setenv name v Set env var to value v (csh/tcsh)
export name="v" set environment variable to value v (ksh/bash)
Output, Communication, & Help
Command Description
lpr -P printer f
or
lp -d printer f Output file f to line printer
script [f] Save terminal session to f
exit Stop saving terminal session
mailx username Send mail to user
man name Unix manual entry for name
Process Control
Command Description
CTRL/c * Interrupt processes
CTRL/s * Stop screen scrolling
CTRL/q * Resume screen output
sleep n Sleep for n seconds
jobs Print list of jobs
kill % Kill job n
ps Print process status stats
kill -9 n Remove process n
CTRL/z * Suspend current process
stop %n Suspend background job n
cmmd& Run cmmd in background
bg [%n] Resume background job n
fg [%n] Resume foreground job n
exit Exit from shell
Environment Status
Command Description
ls [d] [f...] List files in directory
ls -1 [f...] List files in detail
alias [name] Display command aliases
printenv [name] Print environment values
quota Display disk quota
date Print date & time
who List logged in users
whoami Display current user
finger [username] Output user information
chfn Change finger information
pwd Print working directory
history Display recent commands
! n Submit recent command n
File Manipulation
Command Description
vi [f] Vi fullscreen editor
emacs [f] Emacs fullscreen editor
ed [f] Text editor
wc f Line, word, & char count
cat f List contents of file
more f List file contents by screen
cat f1 f2 >f3 Concatenates f1 & f2 into f3
chmod mode f Change protection mode of f
cmp f1 f2 Compare two files
cp f1 f2 Copy file f1 into f2
sort f Alphabetically sort f
split [-n] f Split f into n-line pieces
mv f1 f2 Rename file f1 as f2
rm f Delete (remove) file f
grep 'ptn' f Outputs lines that match ptn
diff f1 f2 Lists file differences
head f Output beginning of f
tail f Output end of f
Compiler
Command Description
cc [-o f1] f2 C compiler
lint f Check C code for errors
f77 [-o f1] f2 Fortran77 compiler
pc [-o f1] f2 Pascal compiler
Working with NFS files
Files saved on the UITS central Unix computers Steel, the Parallel PC cluster, Solar/Lunar, and the Research SP are stored on the Network File Server (NFS). That means that your files are really on one disk, in directories named for the central Unix hosts on which you have accounts.
No matter which of these computers you are logged into, you can get to your files on any of the others. Here are the commands to use to get to any system directory from any other system:
cd /N/u/username/PPPC/
cd /N/u/username/Cobalt/
cd /N/u/username/Solar/
cd /N/u/username/Steel/
cd /n/u/username/SP/
Be sure you use the capitalization just as you see above, and substitute your own username for "username".
For example, if Jessica Rabbit is logged into her account on Steel, and wants to get a file on her SP account, she would enter:
cd /N/u/jrabbit/SP/
Now when she lists her files, she'll see her SP files, even though she's actually logged into Steel.
You can use the ordinary Unix commands to move files, copy files, or make symbolic links between files. For example, if Jessica Rabbit wanted to move "file1" from her Steel directory to her SP directory, she would enter:
mv -i /N/u/jrabbit/Steel/file1 /N/u/jrabbit/SP/
This shared file system means that you can access, for example, your SP files even when you are logged into Steel, and vice versa. However, if you are logged into the SP, you can only use the software installed on SP -- only users' directories are linked together, not system directories.
Abbreviations used in this document
CTRL/x hold down control key and press x
d directory
env environment
f filename
n number
nd computer node
prtr printer
ptn pattern
var variable
[y/n] yes or no
[] optional arg
... list
http://infohost.nmt.edu/tcc/help/unix/unix_cmd.html
http://www.computerhope.com/unix.htm
http://oracle.oreilly.com/news/unix_oracle_0101.html
http://www.ss64.com/ora/index.html
http://www.ss64.com/bash/index.html
http://www.oracle-base.com/dba/DBACategories.php
http://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0
http://viu.eng.rpi.edu/lab/1/s1_4.html
The most frequently used commands in UNIX/LINUX
Submitted by prasannakrao on Tue, 04/13/2004 - 10:54. General Interest | Quick Tips & Newbies
The following are the most frequently used commands in UNIX/LINUX:
01. "cd" - used to change directory
E.g.: cd /home/oracle - this will go inside the "/home/oracle" directory
The "chdir" command also does the same
02. "mkdir" - used to create a directory
E.g.: mkdir /home/oracle - this will create the "/home/oracle" directory
The "chdir" command also does the same
03. "ls" - used to list the contents of any directory
E.g.: ls /home/oracle - this will list the contents of the "/home/oracle" directory
04. "cp" - used to copy files
E.g.: cp /home/oracle/1.txt /home - this will copy the file "/home/oracle/1.txt" into the directory "/home"
05. "mv" - used to move files
E.g.: mv /home/oracle/1.txt /home - this will move the file "/home/oracle/1.txt" into the directory "/home"
06. "rm" - used to remove/delete a file
E.g.: rm /home/oracle/1.txt - this will remove the file "/home/oracle/1.txt"
07. "clear" - used to clear the screen
08. "pwd" - used to display the current working directory (pwd=print working dir)
09. "chmod" - used to change the permission of a file
E.g.: chmod 755 /home/oracle/1.txt - this will give all the rights except the right to edit the file 1.txt
10. "man" - provides in depth information about the requested command (man=manual)
E.g.: man pwd - this will show the online manual on the command `pwd`
11. "vi" - invokes the vi editor (vi=visual)
E.g.: vi /home/oracle/1.txt - this will invoke the vi editor for editing the file 1.txt
12. "ps" - lists the processes that are currently running
13. "find" - used to locate a particular file/folder
E.g.: find /home/oracle -type f -name 1.txt - this will look for the file 1.txt under the /home/oracle folder
14. "touch" - used to change file access and modification time or create a new file
E.g.: touch /home/oracle/1.txt - this will create a new file named 1.txt if not already present. Else, it will change the file access time
15. "more" - used to display text one screen at a time, from a file
E.g.: more /home/oracle/1.txt - this will display the contents of 1.txt one screen at a time
16. "cat" - usually used to look at the contents of a file
E.g.: cat /home/oracle/1.txt - this will display the contents of 1.txt
17. "cal" - used to display the calendar for a month and year
18. "date" - used to display the current system date
19. "exit" - used to exit from a program or shell
20. "who" - used to see which user is connected to the system
Environment Control
Command Description
cd d Change to directory d
mkdir d Create new directory d
rmdir d Remove directory d
mv f1 [f2...] d Move file f to directory d
mv d1 d2 Rename directory d1 as d2
passwd Change password
alias name1 name2 Create command alias (csh/tcsh)
alias name1="name2" Create command alias (ksh/bash)
unalias name1[na2...] Remove command alias na
ssh nd Login securely to remote node
exit End terminal session
setenv name v Set env var to value v (csh/tcsh)
export name="v" set environment variable to value v (ksh/bash)
Output, Communication, & Help
Command Description
lpr -P printer f
or
lp -d printer f Output file f to line printer
script [f] Save terminal session to f
exit Stop saving terminal session
mailx username Send mail to user
man name Unix manual entry for name
Process Control
Command Description
CTRL/c * Interrupt processes
CTRL/s * Stop screen scrolling
CTRL/q * Resume screen output
sleep n Sleep for n seconds
jobs Print list of jobs
kill % Kill job n
ps Print process status stats
kill -9 n Remove process n
CTRL/z * Suspend current process
stop %n Suspend background job n
cmmd& Run cmmd in background
bg [%n] Resume background job n
fg [%n] Resume foreground job n
exit Exit from shell
Environment Status
Command Description
ls [d] [f...] List files in directory
ls -1 [f...] List files in detail
alias [name] Display command aliases
printenv [name] Print environment values
quota Display disk quota
date Print date & time
who List logged in users
whoami Display current user
finger [username] Output user information
chfn Change finger information
pwd Print working directory
history Display recent commands
! n Submit recent command n
File Manipulation
Command Description
vi [f] Vi fullscreen editor
emacs [f] Emacs fullscreen editor
ed [f] Text editor
wc f Line, word, & char count
cat f List contents of file
more f List file contents by screen
cat f1 f2 >f3 Concatenates f1 & f2 into f3
chmod mode f Change protection mode of f
cmp f1 f2 Compare two files
cp f1 f2 Copy file f1 into f2
sort f Alphabetically sort f
split [-n] f Split f into n-line pieces
mv f1 f2 Rename file f1 as f2
rm f Delete (remove) file f
grep 'ptn' f Outputs lines that match ptn
diff f1 f2 Lists file differences
head f Output beginning of f
tail f Output end of f
Compiler
Command Description
cc [-o f1] f2 C compiler
lint f Check C code for errors
f77 [-o f1] f2 Fortran77 compiler
pc [-o f1] f2 Pascal compiler
Working with NFS files
Files saved on the UITS central Unix computers Steel, the Parallel PC cluster, Solar/Lunar, and the Research SP are stored on the Network File Server (NFS). That means that your files are really on one disk, in directories named for the central Unix hosts on which you have accounts.
No matter which of these computers you are logged into, you can get to your files on any of the others. Here are the commands to use to get to any system directory from any other system:
cd /N/u/username/PPPC/
cd /N/u/username/Cobalt/
cd /N/u/username/Solar/
cd /N/u/username/Steel/
cd /n/u/username/SP/
Be sure you use the capitalization just as you see above, and substitute your own username for "username".
For example, if Jessica Rabbit is logged into her account on Steel, and wants to get a file on her SP account, she would enter:
cd /N/u/jrabbit/SP/
Now when she lists her files, she'll see her SP files, even though she's actually logged into Steel.
You can use the ordinary Unix commands to move files, copy files, or make symbolic links between files. For example, if Jessica Rabbit wanted to move "file1" from her Steel directory to her SP directory, she would enter:
mv -i /N/u/jrabbit/Steel/file1 /N/u/jrabbit/SP/
This shared file system means that you can access, for example, your SP files even when you are logged into Steel, and vice versa. However, if you are logged into the SP, you can only use the software installed on SP -- only users' directories are linked together, not system directories.
Abbreviations used in this document
CTRL/x hold down control key and press x
d directory
env environment
f filename
n number
nd computer node
prtr printer
ptn pattern
var variable
[y/n] yes or no
[] optional arg
... list
http://infohost.nmt.edu/tcc/help/unix/unix_cmd.html
http://www.computerhope.com/unix.htm
http://oracle.oreilly.com/news/unix_oracle_0101.html
http://www.ss64.com/ora/index.html
http://www.ss64.com/bash/index.html
http://www.oracle-base.com/dba/DBACategories.php
http://forums.oracle.com/forums/thread.jspa?threadID=305918&tstart=0
http://viu.eng.rpi.edu/lab/1/s1_4.html
The most frequently used commands in UNIX/LINUX
Submitted by prasannakrao on Tue, 04/13/2004 - 10:54. General Interest | Quick Tips & Newbies
The following are the most frequently used commands in UNIX/LINUX:
01. "cd" - used to change directory
E.g.: cd /home/oracle - this will go inside the "/home/oracle" directory
The "chdir" command also does the same
02. "mkdir" - used to create a directory
E.g.: mkdir /home/oracle - this will create the "/home/oracle" directory
The "chdir" command also does the same
03. "ls" - used to list the contents of any directory
E.g.: ls /home/oracle - this will list the contents of the "/home/oracle" directory
04. "cp" - used to copy files
E.g.: cp /home/oracle/1.txt /home - this will copy the file "/home/oracle/1.txt" into the directory "/home"
05. "mv" - used to move files
E.g.: mv /home/oracle/1.txt /home - this will move the file "/home/oracle/1.txt" into the directory "/home"
06. "rm" - used to remove/delete a file
E.g.: rm /home/oracle/1.txt - this will remove the file "/home/oracle/1.txt"
07. "clear" - used to clear the screen
08. "pwd" - used to display the current working directory (pwd=print working dir)
09. "chmod" - used to change the permission of a file
E.g.: chmod 755 /home/oracle/1.txt - this will give all the rights except the right to edit the file 1.txt
10. "man" - provides in depth information about the requested command (man=manual)
E.g.: man pwd - this will show the online manual on the command `pwd`
11. "vi" - invokes the vi editor (vi=visual)
E.g.: vi /home/oracle/1.txt - this will invoke the vi editor for editing the file 1.txt
12. "ps" - lists the processes that are currently running
13. "find" - used to locate a particular file/folder
E.g.: find /home/oracle -type f -name 1.txt - this will look for the file 1.txt under the /home/oracle folder
14. "touch" - used to change file access and modification time or create a new file
E.g.: touch /home/oracle/1.txt - this will create a new file named 1.txt if not already present. Else, it will change the file access time
15. "more" - used to display text one screen at a time, from a file
E.g.: more /home/oracle/1.txt - this will display the contents of 1.txt one screen at a time
16. "cat" - usually used to look at the contents of a file
E.g.: cat /home/oracle/1.txt - this will display the contents of 1.txt
17. "cal" - used to display the calendar for a month and year
18. "date" - used to display the current system date
19. "exit" - used to exit from a program or shell
20. "who" - used to see which user is connected to the system
Unix for Oracle Database Administrators
The Article is a quick reference describing, how to create complex one-line Unix commands in addition to some most often used Unix commands used by Oracle database administrators.
Building Unix Commands
One of the most confounding things for the Unix neophyte is being confronted with a complex Unix command. The cryptic nature of Unix is such that even the most seasoned Unix professional may have trouble deciphering such a command.
Regarding the terms commands and scripts, you should note that any command may become a script if it is encapsulated into a file for execution. Hence, find . –print can be a command if executed from the prompt, or a script if placed into a file.
Building a Complex Unix Command from Scratch
A complex, one-line script is really composed of several simpler commands connected by the pipe operator (|). We will start by how to build a one-line script to kill all the Oracle processes on your server. Then we will see how to build one-line script to find files that contain a specific text string.
A script to kill all Oracle processes
This is a common Unix script used by Oracle DBAs when a database is locked up, and Server Manager cannot be used to stop the database in more “gentle“ fashion.
To begin, the Unix kill command is used to kill a process. The basic format of the kill command is as follows:
Kill –9 PID1 PID2 PID3 …PIDn
The trick is to be able to identify and kill only the Oracle processes. That’s done by stringing several commands together. The resulting one-line script looks like this:
ps –ef|grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’|xargs kill –9
We ‘ll walk through the process of building the command. To begin, we want to get a list of active processes on the server. We can do that using the following command:
ps –ef
If we execute ps –ef on our server, we’ ll see a long list of processes - both for Oracle and for many other things. However, we want to limit your output to only those processes that are related to the Oracle Database. The grep command can be used to do this. Oracle background process names always begin with “ora_”, so piping the output of ps –ef through grep “ora_” will remove all but the Oracle background processes. For example:
>ps –ef |grep “ora_”
oracle 12011 1 0 Dec 06 … ora_dbwr_JAP
oracle 12789 20202 0 12:10:55 0:00 grep ora_
oracle 13202 1 0 Dec 06 … ora_smon_JAP
oracle 14983 1 0 Dec 06 … ora_arch_JAP
oracle 10209 1 0 Dec 06 … ora_pmon_JAP
oracle 2090 1 0 Dec 06 … ora_reco_JAP
oracle 10404 1 0 Dec 06 … ora_lgwr_JAP
oracle 10403 1 0 Dec 06 … ora_dbwr_TEST
oracle 10401 1 0 Dec 06 … ora_lgwr_TEST
In the above output as we can see it includes the process that’s running grep command. Pipe this output through grep –v grep to remove the grep command, so you don’t kill your own process. The –v option makes grep work in a way that’s opposite its usual manner. Whereas grep finds and includes strings, grep –v excludes strings. In this next example, we’ ll see that the grep line is now missing from the output:
>ps –ef |grep “ora_”|grep –v grep
oracle 12011 1 0 Dec 06 … ora_dbwr_JAP
oracle 13202 1 0 Dec 06 … ora_smon_JAP
oracle 14983 1 0 Dec 06 … ora_arch_JAP
oracle 10209 1 0 Dec 06 … ora_pmon_JAP
oracle 2090 1 0 Dec 06 … ora_reco_JAP
oracle 10404 1 0 Dec 06 … ora_lgwr_JAP
oracle 10403 1 0 Dec 06 … ora_dbwr_TEST
oracle 10401 1 0 Dec 06 … ora_lgwr_TEST
Next, we should filter out all processes except those for the current ORACLE_SID. That way we delete the background processes only for that one instance instead of for all instances (if there are multiple database instance running). Do that by grepping for the SID name:
>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID
oracle 12011 1 0 Dec 06 … ora_dbwr_JAP
oracle 13202 1 0 Dec 06 … ora_smon_JAP
oracle 14983 1 0 Dec 06 … ora_arch_JAP
oracle 10209 1 0 Dec 06 … ora_pmon_JAP
oracle 2090 1 0 Dec 06 … ora_reco_JAP
oracle 10404 1 0 Dec 06 … ora_lgwr_JAP
Now that we have an accurate list of processes that you want to kill, you can use the awk command to get the process ID (PID) for each of these processes. The PID is in the second column, so we will use the awk ‘{print $2}’ command to display only that column:
>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’
12011
13202
14983
10209
2090
10404
Now we have a list of process Id numbers for the Oracle background processes. For the last step, we use the xargs command to pipe the list of PIDs to the kill command. For example:
>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’|xargs kill –9
Now that we’ve created this compound command, we can assign it to a Unix alias or we can put it in a file and make it a shell script so that we can execute it with a single short command.
Note: Not all shells support aliases. For example, if we are using the Bourne shell we will not be able to use aliases.
The following command assigns the new compound command to an alias named kill_oracle:
alias kill_oracle = “ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’|xargs kill –9”
By placing the command to create the alias in your .profile file, we’ll have it available every time you sign on to Unix. By using an alias, you encapsulate the command without the burden of placing the command into a script file. Now, entering the alias kill_oracle at the command prompt will cause your command to run, which will kill all Oracle background processes for the instance to which $ORACLE_SID points.
A script to find all files containing a specific string
In Unix, it is not easy to find files that contain specific strings. Now we will explore a way to quickly build a command that will allow us to find a file that contains a particular character string.
Using commands such as xargs, we can quickly generate Unix scripts to perform many useful tasks. Suppose that we have created a script to create database TEST. Unfortunately, we have completely forgotten the name and location of the script file, and we need a Unix command to locate it. The example here demonstrates how you can leverage the xargs command to quickly create a complex command that searches for our lost file.
We will begin by writing a command that will display all filenames on the server. This syntax is quite simple in Unix, as the find command can be used to return a list of every file on the server starting from your current directory:
>find . –print
/home/oracle/wylie/sqlnet.log
/home/oracle/wylie/abc.sql
/home/oracle/wylie/tablespace.sql
/home/oracle/wylie/create1.sql
/home/oracle/wylie/export.dmp
/home/oracle/wylie/create2.sql
/home/oracle/wylie/create3.sql
/home/oracle/wylie/a.txt
We now have a complete list of all the Unix files under our current directory. The next step is to pipe this list of filenames to the grep command to search for files containing the string CREATE_DB_TEST. Because the grep command accepts a filename as an argument, you can use xargs to execute a grep command to search each file for the string we need:
find . –print|xargs grep –i create_db_test
The –i option tells grep to ignore case. We can execute this new command at the Unix prompt, and we’ll see that it quickly finds the file we are seeking:
>find . –print|xargs grep –i create_db_test
This ability to take a basic Unix command and pipe the output into another command is a fundamental principle of Unix shell programming for Oracle.
Unix Server Environment
Here are some handy Unix commands that will make it easier for use to navigate in our Unix environment. Firstly we will see at commands that can be automatically executed when we sign on to Unix as the Oracle user. There is a special file in our home directory in which we can place Unix commands that we want automatically executed when we sign on to the system. If we use Korn shell, this file is named .profile. if we use C shell, it will be called .cshrc.
We will also see how to create a standard Unix prompt, wrap SQL in Unix script, and write a utility to quickly change all files in a directory.
Set a Standard Unix Prompt
Placing the following code snippet in our .profile file will give you a Unix prompt that identifies our current server name, database name, and working directory. This can help prevent you from accidentally running a command against the wrong database. Note that I have my prompt go to a new line after displaying the information, so that I have a full 79 characters in which to type my Unix commands.
#*********************************************************************
# Standard Unix Prompt
#*********************************************************************
PS1=”
`hostname`*\${ORACLE_SID}-\${PWD}
>”
Here is what the prompt looks like after we have executed the PS1 command shown in the previous example. Note now the prompt changes when you change directories.
agj1*JAP-/home/oracle
>pwd
/home/oracle
agj1*JAP-/home/oracle
>cd /home2/dmp/treasury
agj1*JAP-/home2/dmp/treasury
>
Create Useful Unix Aliases for Oracle
Here we will see how we can place a list of helpful Unix aliases in the .profile file of a Unix Oracle User.
An alias is a Unix shortcut whereby we can define a short name to use in place of long Unix command. For example, we can create a shortcut called “log” that would execute the Unix cd (change directory) command to take us to the Unix directory where our alert log is located:
alias log = ‘cd $DBA/$ORACLE_SID/bdump’
The following example shows how aliases such as these can be used in place of typing a long command:
$log
$pwd
/home/oracle/app/oracle/admin/JAP/bdump
$
Any alias can be removed easily with the Unix unalias command. For example, to remove the log alias, we would enter the command unalias log.
Place a SQL*Plus Script in a Unix Shell Wrapper
Novice to Unix often find it convenient to execute SQL commands directly from the Unix prompt, without having to enter SQL*Plus each time. The following script shows how to create a Unix shell wrapper for any set of SQL*Plus commands. The Unix script in this example is named run_sql.ksh, and it invokes SQL*Plus to execute a SELECT statement followed by the SQL*Plus script contained in the file /home/oracle/abc.sql:
$cat run_sql.ksh
#!/bin/ksh
#First, we must set the environment . . . . .
ORACLE_SID=mysid
export ORACLE_SID
ORACLE_HOME=\`cat /etc/oratab|grep ^$OARCLE_SIDcut –f2 –d’:’`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/passwd<
SELECT * FROM v\$database;
@/home/oracle/abc.sql
exit
!
We can also execute a script directly from the command line, provided we have set ORACLE_HOME and ORACLE_SID in our Unix environment. For example:
$sqlplus system/manager @abc
Building Unix Commands
One of the most confounding things for the Unix neophyte is being confronted with a complex Unix command. The cryptic nature of Unix is such that even the most seasoned Unix professional may have trouble deciphering such a command.
Regarding the terms commands and scripts, you should note that any command may become a script if it is encapsulated into a file for execution. Hence, find . –print can be a command if executed from the prompt, or a script if placed into a file.
Building a Complex Unix Command from Scratch
A complex, one-line script is really composed of several simpler commands connected by the pipe operator (|). We will start by how to build a one-line script to kill all the Oracle processes on your server. Then we will see how to build one-line script to find files that contain a specific text string.
A script to kill all Oracle processes
This is a common Unix script used by Oracle DBAs when a database is locked up, and Server Manager cannot be used to stop the database in more “gentle“ fashion.
To begin, the Unix kill command is used to kill a process. The basic format of the kill command is as follows:
Kill –9 PID1 PID2 PID3 …PIDn
The trick is to be able to identify and kill only the Oracle processes. That’s done by stringing several commands together. The resulting one-line script looks like this:
ps –ef|grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’|xargs kill –9
We ‘ll walk through the process of building the command. To begin, we want to get a list of active processes on the server. We can do that using the following command:
ps –ef
If we execute ps –ef on our server, we’ ll see a long list of processes - both for Oracle and for many other things. However, we want to limit your output to only those processes that are related to the Oracle Database. The grep command can be used to do this. Oracle background process names always begin with “ora_”, so piping the output of ps –ef through grep “ora_” will remove all but the Oracle background processes. For example:
>ps –ef |grep “ora_”
oracle 12011 1 0 Dec 06 … ora_dbwr_JAP
oracle 12789 20202 0 12:10:55 0:00 grep ora_
oracle 13202 1 0 Dec 06 … ora_smon_JAP
oracle 14983 1 0 Dec 06 … ora_arch_JAP
oracle 10209 1 0 Dec 06 … ora_pmon_JAP
oracle 2090 1 0 Dec 06 … ora_reco_JAP
oracle 10404 1 0 Dec 06 … ora_lgwr_JAP
oracle 10403 1 0 Dec 06 … ora_dbwr_TEST
oracle 10401 1 0 Dec 06 … ora_lgwr_TEST
In the above output as we can see it includes the process that’s running grep command. Pipe this output through grep –v grep to remove the grep command, so you don’t kill your own process. The –v option makes grep work in a way that’s opposite its usual manner. Whereas grep finds and includes strings, grep –v excludes strings. In this next example, we’ ll see that the grep line is now missing from the output:
>ps –ef |grep “ora_”|grep –v grep
oracle 12011 1 0 Dec 06 … ora_dbwr_JAP
oracle 13202 1 0 Dec 06 … ora_smon_JAP
oracle 14983 1 0 Dec 06 … ora_arch_JAP
oracle 10209 1 0 Dec 06 … ora_pmon_JAP
oracle 2090 1 0 Dec 06 … ora_reco_JAP
oracle 10404 1 0 Dec 06 … ora_lgwr_JAP
oracle 10403 1 0 Dec 06 … ora_dbwr_TEST
oracle 10401 1 0 Dec 06 … ora_lgwr_TEST
Next, we should filter out all processes except those for the current ORACLE_SID. That way we delete the background processes only for that one instance instead of for all instances (if there are multiple database instance running). Do that by grepping for the SID name:
>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID
oracle 12011 1 0 Dec 06 … ora_dbwr_JAP
oracle 13202 1 0 Dec 06 … ora_smon_JAP
oracle 14983 1 0 Dec 06 … ora_arch_JAP
oracle 10209 1 0 Dec 06 … ora_pmon_JAP
oracle 2090 1 0 Dec 06 … ora_reco_JAP
oracle 10404 1 0 Dec 06 … ora_lgwr_JAP
Now that we have an accurate list of processes that you want to kill, you can use the awk command to get the process ID (PID) for each of these processes. The PID is in the second column, so we will use the awk ‘{print $2}’ command to display only that column:
>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’
12011
13202
14983
10209
2090
10404
Now we have a list of process Id numbers for the Oracle background processes. For the last step, we use the xargs command to pipe the list of PIDs to the kill command. For example:
>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’|xargs kill –9
Now that we’ve created this compound command, we can assign it to a Unix alias or we can put it in a file and make it a shell script so that we can execute it with a single short command.
Note: Not all shells support aliases. For example, if we are using the Bourne shell we will not be able to use aliases.
The following command assigns the new compound command to an alias named kill_oracle:
alias kill_oracle = “ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’|xargs kill –9”
By placing the command to create the alias in your .profile file, we’ll have it available every time you sign on to Unix. By using an alias, you encapsulate the command without the burden of placing the command into a script file. Now, entering the alias kill_oracle at the command prompt will cause your command to run, which will kill all Oracle background processes for the instance to which $ORACLE_SID points.
A script to find all files containing a specific string
In Unix, it is not easy to find files that contain specific strings. Now we will explore a way to quickly build a command that will allow us to find a file that contains a particular character string.
Using commands such as xargs, we can quickly generate Unix scripts to perform many useful tasks. Suppose that we have created a script to create database TEST. Unfortunately, we have completely forgotten the name and location of the script file, and we need a Unix command to locate it. The example here demonstrates how you can leverage the xargs command to quickly create a complex command that searches for our lost file.
We will begin by writing a command that will display all filenames on the server. This syntax is quite simple in Unix, as the find command can be used to return a list of every file on the server starting from your current directory:
>find . –print
/home/oracle/wylie/sqlnet.log
/home/oracle/wylie/abc.sql
/home/oracle/wylie/tablespace.sql
/home/oracle/wylie/create1.sql
/home/oracle/wylie/export.dmp
/home/oracle/wylie/create2.sql
/home/oracle/wylie/create3.sql
/home/oracle/wylie/a.txt
We now have a complete list of all the Unix files under our current directory. The next step is to pipe this list of filenames to the grep command to search for files containing the string CREATE_DB_TEST. Because the grep command accepts a filename as an argument, you can use xargs to execute a grep command to search each file for the string we need:
find . –print|xargs grep –i create_db_test
The –i option tells grep to ignore case. We can execute this new command at the Unix prompt, and we’ll see that it quickly finds the file we are seeking:
>find . –print|xargs grep –i create_db_test
This ability to take a basic Unix command and pipe the output into another command is a fundamental principle of Unix shell programming for Oracle.
Unix Server Environment
Here are some handy Unix commands that will make it easier for use to navigate in our Unix environment. Firstly we will see at commands that can be automatically executed when we sign on to Unix as the Oracle user. There is a special file in our home directory in which we can place Unix commands that we want automatically executed when we sign on to the system. If we use Korn shell, this file is named .profile. if we use C shell, it will be called .cshrc.
We will also see how to create a standard Unix prompt, wrap SQL in Unix script, and write a utility to quickly change all files in a directory.
Set a Standard Unix Prompt
Placing the following code snippet in our .profile file will give you a Unix prompt that identifies our current server name, database name, and working directory. This can help prevent you from accidentally running a command against the wrong database. Note that I have my prompt go to a new line after displaying the information, so that I have a full 79 characters in which to type my Unix commands.
#*********************************************************************
# Standard Unix Prompt
#*********************************************************************
PS1=”
`hostname`*\${ORACLE_SID}-\${PWD}
>”
Here is what the prompt looks like after we have executed the PS1 command shown in the previous example. Note now the prompt changes when you change directories.
agj1*JAP-/home/oracle
>pwd
/home/oracle
agj1*JAP-/home/oracle
>cd /home2/dmp/treasury
agj1*JAP-/home2/dmp/treasury
>
Create Useful Unix Aliases for Oracle
Here we will see how we can place a list of helpful Unix aliases in the .profile file of a Unix Oracle User.
An alias is a Unix shortcut whereby we can define a short name to use in place of long Unix command. For example, we can create a shortcut called “log” that would execute the Unix cd (change directory) command to take us to the Unix directory where our alert log is located:
alias log = ‘cd $DBA/$ORACLE_SID/bdump’
The following example shows how aliases such as these can be used in place of typing a long command:
$log
$pwd
/home/oracle/app/oracle/admin/JAP/bdump
$
Any alias can be removed easily with the Unix unalias command. For example, to remove the log alias, we would enter the command unalias log.
Place a SQL*Plus Script in a Unix Shell Wrapper
Novice to Unix often find it convenient to execute SQL commands directly from the Unix prompt, without having to enter SQL*Plus each time. The following script shows how to create a Unix shell wrapper for any set of SQL*Plus commands. The Unix script in this example is named run_sql.ksh, and it invokes SQL*Plus to execute a SELECT statement followed by the SQL*Plus script contained in the file /home/oracle/abc.sql:
$cat run_sql.ksh
#!/bin/ksh
#First, we must set the environment . . . . .
ORACLE_SID=mysid
export ORACLE_SID
ORACLE_HOME=\`cat /etc/oratab|grep ^$OARCLE_SIDcut –f2 –d’:’`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/passwd<
SELECT * FROM v\$database;
@/home/oracle/abc.sql
exit
!
We can also execute a script directly from the command line, provided we have set ORACLE_HOME and ORACLE_SID in our Unix environment. For example:
$sqlplus system/manager @abc
Killing the Oracle DBMS_JOB
Take control of Oracle's queue with a step by step approach to getting rid of those pesky DBMS_JOBs.
Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.
If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.
A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn't very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately--never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn't work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn't an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.
Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn't much good information out there. This is why I want to share my method, so that you won't be stuck up against the wall with this problem and nowhere to turn, as I was.
Lets first go through a few different methods of viewing the information about job queues.
Viewing scheduled dbms_jobs
When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.
scheduled_dbms_jobs.sql
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
What Jobs are Actually Running
A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.
running_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
What Sessions are Running the Jobs
Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.
session_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.
Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session
Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill '9 spid
The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.
9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.
ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.
Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.
Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.
If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.
A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn't very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately--never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn't work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn't an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.
Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn't much good information out there. This is why I want to share my method, so that you won't be stuck up against the wall with this problem and nowhere to turn, as I was.
Lets first go through a few different methods of viewing the information about job queues.
Viewing scheduled dbms_jobs
When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.
scheduled_dbms_jobs.sql
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
What Jobs are Actually Running
A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.
running_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
What Sessions are Running the Jobs
Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.
session_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.
Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session
Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill '9 spid
The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.
9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.
ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.
Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.
