Vi Cheat Sheet

Vi Cheat Sheet

Vi has two modes insertion mode and command mode. The editor begins in command mode, where the cursor movement and text deletion and pasting occur. Insertion mode begins upon entering an insertion or change command. [ESC] returns the editor to command mode (where you can quit, for example by typing :q!). Most commands execute as soon as you type them except for "colon" commands which execute when you press the return key.

Quitting

---

:x	Exit, saving changes
:q	Exit as long as there have been no changes
ZZ	Exit and save changes if any have been made
:q!	Exit and ignore any changes

Inserting Text

---

i	Insert before cursor
I	Insert before line
a	Append after cursor
A	Append after line
o	Open a new line after current line
O	Open a new line before current line
r	Replace one character
R	Replace many characters

Motion

---

h	Move left
j	Move down
k	Move up
l	Move right
w	Move to next word
W	Move to next blank delimited word
b	Move to the beginning of the word
B	Move to the beginning of blank delimited word
e	Move to the end of the word
E	Move to the end of Blank delimited word
(	Move a sentence back
)	Move a sentence forward
{	Move a paragraph back
}	Move a paragraph forward
0	Move to the beginning of the line
$	Move to the end of the line
1G	Move to the first line of the file
G	Move to the last line of the file
nG	Move to nth line of the file
:n	Move to nth line of the file
fc	Move forward to c
Fc	Move back to c
H	Move to top of screen
M	Move to middle of screen
L	Move to botton of screen
%	Move to associated ( ), { }, [ ]

Deleting Text

---

Almost all deletion commands are performed by typing d followed by a motion. For example, dw deletes a word. A few other deletes are:

x	Delete character to the right of cursor
X	Delete character to the left of cursor
D	Delete to the end of the line
dd	Delete current line
:d	Delete current line

Yanking Text

---

Like deletion, almost all yank commands are performed by typing y followed by a motion. For example, y$ yanks to the end of the line. Two other yank commands are:

yy	Yank the current line
:y	Yank the current line

Changing text

---

The change command is a deletion command that leaves the editor in insert mode. It is performed by typing c followed by a motion. For wxample cw changes a word. A few other change commands are:

C	Change to the end of the line
cc	Change the whole line

Putting text

---

p	Put after the position or after the line
P	Put before the poition or before the line

Buffers

---

Named buffers may be specified before any deletion, change, yank or put command. The general prefix has the form "c where c is any lowercase character. for example, "adw deletes a word into buffer a. It may thereafter be put back into text with an appropriate "ap.

Markers

---

Named markers may be set on any line in a file. Any lower case letter may be a marker name. Markers may also be used as limits for ranges.

mc	Set marker c on this line
`c	Go to beginning of marker c line.
'c	Go to first non-blank character of marker c line.

Search for strings

---

/string	Search forward for string
?string	Search back for string
n	Search for next instance of string
N	Search for previous instance of string

Replace

---

The search and replace function is accomplished with the :s command. It is commonly used in combination with ranges or the :g command (below).

:s/pattern/string/flags	Replace pattern with string according to flags.
g	Flag - Replace all occurrences of pattern
c	Flag - Confirm replaces.
&	Repeat last :s command

Regular Expressions

---

. (dot)	Any single character except newline
*	zero or more occurrences of any character
[...]	Any single character specified in the set
[^...]	Any single character not specified in the set
^	Anchor - beginning of the line
$	Anchor - end of line
\<	Anchor - beginning of word
\>	Anchor - end of word
\(...\)	Grouping - usually used to group conditions
\n	Contents of nth grouping

[...] - Set Examples
[A-Z]	The SET from Capital A to Capital Z
[a-z]	The SET from lowercase a to lowercase z
[0-9]	The SET from 0 to 9 (All numerals)
[./=+]	The SET containing . (dot), / (slash), =, and +
[-A-F]	The SET from Capital A to Capital F and the dash (dashes must be specified first)
[0-9 A-Z]	The SET containing all capital letters and digits and a space
[A-Z][a-zA-Z]	In the first position, the SET from Capital A to Capital Z In the second character position, the SET containing all letters

Regular Expression Examples
/Hello/	Matches if the line contains the value Hello
/^TEST$/	Matches if the line contains TEST by itself
/^[a-zA-Z]/	Matches if the line starts with any letter
/^[a-z].*/	Matches if the first character of the line is a-z and there is at least one more of any character following it
/2134$/	Matches if line ends with 2134
/\(21|35\)/	Matches is the line contains 21 or 35 Note the use of ( ) with the pipe symbol to specify the 'or' condition
/[0-9]*/	Matches if there are zero or more numbers in the line
/^[^#]/	Matches if the first character is not a # in the line
Notes: 1. Regular expressions are case sensitive 2. Regular expressions are to be used where pattern is specified

Counts

---

Nearly every command may be preceded by a number that specifies how many times it is to be performed. For example, 5dw will delete 5 words and 3fe will move the cursor forward to the 3rd occurrence of the letter e. Even insertions may be repeated conveniently with this method, say to insert the same line 100 times.

Ranges

---

Ranges may precede most "colon" commands and cause them to be executed on a line or lines. For example :3,7d would delete lines 3-7. Ranges are commonly combined with the :s command to perform a replacement on several lines, as with :.,$s/pattern/string/g to make a replacement from the current line to the end of the file.

:n,m	Range - Lines n-m
:.	Range - Current line
:$	Range - Last line
:'c	Range - Marker c
:%	Range - All lines in file
:g/pattern/	Range - All lines that contain pattern

Files

---

:w file	Write to file
:r file	Read file in after line
:n	Go to next file
:p	Go to previous file
:e file	Edit file
!!program	Replace line with output from program

Other

---

~	Toggle upp and lower case
J	Join lines
.	Repeat last text-changing command
u	Undo last change
U	Undo all changes to line

General Notes:

1. Before doing anything to a document, type the following command followed by a carriage return: :set showmode

2. VI is CaSe SEnsItiVe!!! So make sure Caps Lock is OFF.

---

Starting and Ending VI

---

Starting VI
vi filename	Edits filename
vi -r filename	Edits last save version of filename after a crash
vi + n filename	Edits filename and places curser at line n
vi + filename	Edits filename and places curser on last line
vi +/string filename	Edits filename and places curser on first occurance of string
vi filename file2 ...	Edits filename, then edits file2 ... After the save, use :n
Ending VI
ZZ or :wq or :x	Saves and exits VI
:w	Saves current file but doesn't exit
:w!	Saves current file overriding normal checks but doesn't exit
:w file	Saves current as file but doesn't exit
:w! file	Saves to file overriding normal checks but doesn't exit
:n,mw file	Saves lines n through m to file
:n,mw >>file	Saves lines n through m to the end of file
:q	Quits VI and may prompt if you need to save
:q!	Quits VI and without saving
:e!	Edits file discarding any unsaved changes (starts over)
:we!	Saves and continues to edit current file

Status

---

:.=	Shows current line number
:=	Shows number of lines in file
Control-G	Shows filename, current line number, total lines in file, and % of file location
l	Displays tab (^l) backslash (\) backspace (^H) newline ($) bell (^G) formfeed (^L^) of current line

Modes

---

Vi has two modes insertion mode and command mode. The editor begins in command mode, where the cursor movement and text deletion and pasting occur. Insertion mode begins upon entering an insertion or change command. [ESC] returns the editor to command mode (where you can quit, for example by typing :q!). Most commands execute as soon as you type them except for "colon" commands which execute when you press the ruturn key.

Inserting Text

---

i	Insert before cursor
I	Insert before line
a	Append after cursor
A	Append after line
o	Open a new line after current line
O	Open a new line before current line
r	Replace one character
R	Replace many characters
CTRL-v char	While inserting, ignores special meaning of char (e.g., for inserting characters like ESC and CTRL) until ESC is used
:r file	Reads file and inserts it after current line
:nr file	Reads file and inserts it after line n
CTRL-i or TAB	While inserting, inserts one shift width

Things to do while in Insert Mode:
CTRL-h or Backspace	While inserting, deletes previous character
CTRL-w	While inserting, deletes previous word
CTRL-x	While inserting, deletes to start of inserted text
CTRL-v	Take the next character literally. (i.e. To insert a Control-H, type Control-v Control-h)

Motion

---

h	Move left
j	Move down
k	Move up
l	Move right
Arrow Keys	These do work, but they may be too slow on big files. Also may have unpredictable results when arrow keys are not mapped correctly in client.
w	Move to next word
W	Move to next blank delimited word
b	Move to the beginning of the word
B	Move to the beginning of blank delimted word
^	Moves to the first non-blank character in the current line
+ or	Moves to the first character in the next line
-	Moves to the first non-blank character in the previous line
e	Move to the end of the word
E	Move to the end of Blank delimited word
(	Move a sentence back
)	Move a sentence forward
{	Move a paragraph back
}	Move a paragraph forward
[[	Move a section back
]]	Move a section forward
0 or |	Move to the begining of the line
n|	Moves to the column n in the current line
$	Move to the end of the line
1G	Move to the first line of the file
G	Move to the last line of the file
nG	Move to nth line of the file
:n	Move to nth line of the file
fc	Move forward to c
Fc	Move back to c
H	Move to top of screen
nH	Moves to nth line from the top of the screen
M	Move to middle of screen
L	Move to botton of screen
nL	Moves to nth line from the bottom of the screen
Control-d	Move forward ½ screen
Control-f	Move forward one full screen
Control-u	Move backward ½ screen
Control-b	Move backward one full screen
CTRL-e	Moves screen up one line
CTRL-y	Moves screen down one line
CTRL-u	Moves screen up ½ page
CTRL-d	Moves screen down ½ page
CTRL-b	Moves screen up one page
CTRL-f	Moves screen down one page
CTRL-I	Redraws screen
z	z-carriage return makes the current line the top line on the page
nz	Makes the line n the top line on the page
z.	Makes the current line the middle line on the page
nz.	Makes the line n the middle line on the page
z-	Makes the current line the bottom line on the page
nz-	Makes the line n the bottom line on the page
%	Move to associated ( ), { }, [ ]

Deleting Text

---

Almost all deletion commands are performed by typing d followed by a motion. For example, dw deletes a word. A few other deletes are:

x	Delete character to the right of cursor
nx	Deletes n characters starting with current; omitting n deletes current character only
X	Delete character to the left of cursor
nX	Deletes previous n characters; omitting n deletes previous character only
D	Delete to the end of the line
d$	Deletes from the cursor to the end of the line
dd or :d	Delete current line
ndw	Deletes the next n words starting with current
ndb	Deletes the previous n words starting with current
ndd	Deletes n lines beginning with the current line
:n,md	Deletes lines n through m
dMotion_cmd	Deletes everything included in the Motion Command (e.g., dG would delete from current position to the end of the file, and d4 would delete to the end of the fourth sentence).
"np	Retrieves the last nth delete (last 9 deletes are kept in a buffer)
"1pu.u.	Scrolls through the delete buffer until the desired delete is retrieved (repeat u.)

Yanking Text

---

Like deletion, almost all yank commands are performed by typing y followed by a motion. For example, y$ yanks to the end of the line. Two other yank commands are:

yy	Yank the current line
:y	Yank the current line
nyy or nY	Places n lines in the buffer-copies
yMotion_cmd	Copies everything from the curser to the Motion Command (e.g., yG would copy from current position to the end of the file, and y4 would copy to the end of the fourth sentence)
"(a-z)nyy or "(a-z)ndd	Copies or cuts (deletes) n lines into a named buffer a through z; omitting n works on current line

Changing text

---

The change command is a deletion command that leaves the editor in insert mode. It is performed by typing c followed by a motion. For example cw changes a word. A few other change commands are:

C	Change to the end of the line
cc or S	Change the whole line until ESC is pressed
xp	Switches character at cursor with following character
stext	Substitutes text for the current character until ESC is used
cwtext	Changes current word to text until ESC is used
Ctext	Changes rest of the current line to text until ESC is used
cMotion_cmd	Changes to text from current position to Motion Command until ESC is used
<< or >>	Shifts the line left or right (respectively) by one shift width (a tab)
n<< or n>>	Shifts n lines left or right (respectively) by one shift width (a tab)
<Motion_cmd or >Motion_cmd	Use with Motion Command to shift multiple lines left or right

Putting text

---

p	Put after the position or after the line
P	Put before the poition or before the line
"(a-z)p or "(a-z)P	Pastes text from a named buffer a through z after or before the current line

Buffers

---

Named buffers may be specified before any deletion, change, yank or put command. The general prefix has the form "c where c is any lowercase character. for example, "adw deletes a word into buffer a. It may thereafter be put back into text with an appropriate "ap.

Markers

---

Named markers may be set on any line in a file. Any lower case letter may be a marker name. Markers may also be used as limits for ranges.

mc	Set marker c on this line
`c	Go to beginning of marker c line.
'c	Go to first non-blank character of marker c line.

Search for strings

---

/string	Search forward for string
?string	Search back for string
n	Search for next instance of string
N	Search for previous instance of string
%	Searches to beginning of balancing ( ) [ ] or { }
fc	Searches forward in current line to char
Fc	Searches backward in current line to char
tc	Searches forward in current line to character before char
Tchar	Searches backward in current line to character before char
?str	Finds in reverse for str
:set ic	Ignores case when searching
:set noic	Pays attention to case when searching
:n,ms/str1/str2/opt	Searches from n to m for str1; replaces str1 to str2; using opt-opt can be g for global change, c to confirm change (y to acknowledge, to suppress), and p to print changed lines
&	Repeats last :s command
:g/str/cmd	Runs cmd on all lines that contain str
:g/str1/s/str2/str3/	Finds the line containing str1, replaces str2 with str3
:v/str/cmd	Executes cmd on all lines that do not match str
,	Repeats, in reverse direction, last / or ? search command

Replace

---

The search and replace function is accomplished with the :s command. It is commonly used in combination with ranges or the :g command (below).

:s/pattern/string/flags	Replace pattern with string according to flags.
g	Flag - Replace all occurences of pattern
c	Flag - Confirm replaces.
&	Repeat last :s command

Regular Expressions

---

. (dot)	Any single character except newline
*	zero or more occurances of any character
[...]	Any single character specified in the set
[^...]	Any single character not specified in the set
\<	Matches beginning of word
\>	Matches end of word
^	Anchor - beginning of the line
$	Anchor - end of line
\<	Anchor - begining of word
\>	Anchor - end of word
\(...\)	Grouping - usually used to group conditions
\n	Contents of nth grouping
\	Escapes the meaning of the next character (e.g., \$ allows you to search for $)
\\	Escapes the \ character

[...] - Set Examples
[A-Z]	The SET from Capital A to Capital Z
[a-z]	The SET from lowercase a to lowercase z
[0-9]	The SET from 0 to 9 (All numerals)
[./=+]	The SET containing . (dot), / (slash), =, and +
[-A-F]	The SET from Capital A to Capital F and the dash (dashes must be specified first)
[0-9 A-Z]	The SET containing all capital letters and digits and a space
[A-Z][a-zA-Z]	In the first position, the SET from Capital A to Capital Z In the second character position, the SET containing all letters
[a-z]{m}	Look for m occurances of the SET from lowercase a to lowercase z
[a-z]{m,n}	Look for at least m occurances, but no more than n occurances of the SET from lowercase a to lowercase z

Regular Expression Examples
/Hello/	Matches if the line contains the value Hello
/^TEST$/	Matches if the line contains TEST by itself
/^[a-zA-Z]/	Matches if the line starts with any letter
/^[a-z].*/	Matches if the first character of the line is a-z and there is at least one more of any character following it
/2134$/	Matches if line ends with 2134
/\(21|35\)/	Matches is the line contains 21 or 35 Note the use of ( ) with the pipe symbol to specify the 'or' condition
/[0-9]*/	Matches if there are zero or more numbers in the line
/^[^#]/	Matches if the first character is not a # in the line
Notes: 1. Regular expressions are case sensitive 2. Regular expressions are to be used where pattern is specified

Counts

---

Nearly every command may be preceded by a number that specifies how many times it is to be performed. For example, 5dw will delete 5 words and 3fe will move the cursor forward to the 3rd occurence of the letter e. Even insertions may be repeated conveniently with this method, say to insert the same line 100 times.

Ranges

---

Ranges may precede most "colon" commands and cause them to be executed on a line or lines. For example :3,7d would delete lines 3-7. Ranges are commonly combined with the :s command to perform a replacement on several lines, as with :.,$s/pattern/string/g to make a replacement from the current line to the end of the file.

:n,m	Range - Lines n-m
:.	Range - Current line
:$	Range - Last line
:'c	Range - Marker c
:%	Range - All lines in file
:g/pattern/	Range - All lines that contain pattern

Shell Functions

---

:! cmd	Executes shell command cmd; you can add these special characters to indicate:% name of current file# name of last file edited
!! cmd	Executes shell command cmd, places output in file starting at current line
:!!	Executes last shell command
:r! cmd	Reads and inserts output from cmd
:f file	Renames current file to file
:w !cmd	Sends currently edited file to cmd as standard input and execute cmd
:cd dir	Changes current working directory to dir
:sh	Starts a sub-shell (CTRL-d returns to editor)
:so file	Reads and executes commands in file (file is a shell script)
!Motion_cmd	Sends text from current position to Motion Command to shell command cmd
!}sort	Sorts from current position to end of paragraph and replaces text with sorted text

Files

---

:w file	Write to file
:r file	Read file in after line
:n	Go to next file
:p	Go to previous file
:e file	Edit file
!!program	Replace line with output from program

VI Settings

---

--noto

Note: Options given are default. To change them, enter type :set option to turn them on or :set nooptioni to turn them off.To make them execute every time you open VI, create a file in your HOME directory called .exrc and type the options without the colon (:) preceding the option
Set	Default	Description
:set ai	noai	Turns on auto indentation
:set all	--	Prints all options to the screen
:set ap	aw	Prints line after d c J m :s t u commands
:set aw	noaw	Automatic write on :n ! e# ^^ :rew ^} :tag
:set bf	nobf	Discards control characters from input
:set dir=tmp	dir = /tmp	Sets tmp to directory or buffer file
:set eb	noed	Precedes error messages with a bell
:set ed	noed	Precedes error messages with a bell
:set ht=	ht = 8	Sets terminal hardware tabs
:set ic	noic	Ignores case when searching
:set lisp	nolisp	Modifies brackets for Lisp compatibility.
:set list	nolist	Shows tabs (^l) and end of line ($)
:set magic	magic	Allows pattern matching with special characters
:set mesg	mesg	Allows others to send messages
:set nooption		Turns off option
:set nu	nonu	Shows line numbers
:set opt	opt	Speeds output; eliminates automatic RETURN
:set para=	para = LIlPLPPPQPbpP	macro names that start paragraphs for { and } operators
:set prompt	prompt	Prompts for command input with :
:set re	nore	Simulates smart terminal on dumb terminal
:set remap	remap	Accept macros within macros
:set report	noreport	Indicates largest size of changes reported on status line
:set ro	noro	Changes file type to "read only"
:set scroll=n	scroll = 11	set n lines for CTRL-d and z
:set sh=shell_path	sh = /bin/sh	set shell escape (default is /bin/sh) to shell_path
:set showmode	nosm	Indicates input or replace mode at bottom
:set slow	slow	Pospone display updates during inserts
:set sm	nosm	Show matching { or ( as ) or } is typed
:set sw=n	sw = 8	Sets shift width to n characters
:set tags=x	tags = /usr/lib/tags	Path for files checked for tags (current directory included in default)
:set term	$TERM	Prints terminal type
:set terse	noterse	Shorten messages with terse
:set timeout		Eliminates one-second time limit for macros
:set tl=n	tl = 0	Sets significance of tags beyond n characters (0 means all)
:set ts=n	ts = 8	Sets tab stops to n for text input
:set wa	nowa	Inhibits normal checks before write commands
:set warn	warn		Warns "no write since last change"
:set window=n	window = n	Sets number of lines in a text window to n
:set wm=n	wm = 0	Sets automatic wraparound n spaces from right margin.
:set ws	ws	Sets automatic wraparound n spaces from right margin.

Key Mapping

---

NOTE: Map allows you to define strings of VI commands. If you create a file called ".exrc" in your home directory, any map or set command you place inside this file will be executed every time you run VI. To imbed control characters like ESC in the macro, you need to precede them with CTRL-v. If you need to include quotes ("), precede them with a \ (backslash). Unused keys in vi are: K V g q v * = and the function keys. Example (The actual VI commands are in blue): :map v /I CTRL-v ESC dwiYou CTRL-v ESC ESC Description: When v is pressed, search for "I" (/I ESC), delete word (dw), and insert "You" (iYou ESC). CTRL-v allows ESC to be inserted
:map key cmd_seq	Defines key to run cmd_seq when pressed
:map	Displays all created macros on status line
:unmap key	Removes macro definition for key
:ab str string	When str is input, replaces it with string
:ab	Displays all abbreviations
:una str	Unabbreviates str

Other

---

~	Toggle upper and lower case
J	Join lines
nJ	Joins the next n lines together; omitting n joins the beginning of the next line to the end of the current line
.	Repeat last text-changing command
u	Undo last change (Note: u in combination with . can allow multiple levels of undo in some versions)
U	Undo all changes to line
;	Repeats last f F t or T search command
:N or :E	You can open up a new split-screen window in (n)vi and then use ^w to switch between the two.

What is the difference between Decode and Case?

1. DECODE can be used Only inside SQL statement But CASE can be used anywhere even as a parameter of a function/procedure
2. DECODE can only compare discrete values (not ranges) continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1 Oracle introduced the searched CASE statement which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting).
3. CASE is almost always easier to read and understand and therefore it's easier to debug and maintain.
4. Another difference is CASE is an ANSI standard whereas Decode is proprietary for Oracle.
5. Performance wise there is not much differences. But Case is more powerful than Decode.

 

DECODE unlike the "searched" CASE treats NULL differently. Normally, including CASE, NULL = NULL

results in NULL, however when DECODE compares NULL with NULL result is TRUE:

 

SQL> SELECT  DECODE(NULL,NULL,-1) decode,

  2          CASE NULL WHEN NULL THEN -1 END case

  3    FROM  DUAL

  4  /

 

    DECODE       CASE

---------- ----------

        -1

As per my experience:

 

1.DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc.

It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

 

 

2.DECODE works with expressions that are scalar values only. CASE can work with predicates and

subqueries in searchable form.

 

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

 

 

SQL> select e.ename,

  2         case

  3           -- predicate with "in"

  4           -- mark the category based on ename list

  5           when e.ename in ('KING','SMITH','WARD')

  6                then 'Top Bosses'

  7           -- searchable subquery

  8           -- identify if this emp has a reportee

  9           when exists (select 1 from emp emp1

10                        where emp1.mgr = e.empno)

11                then 'Managers'

12           else

13               'General Employees'

14         end emp_category

15  from emp e

16  where rownum < 5;

 

ENAME      EMP_CATEGORY

---------- -----------------

SMITH      Top Bosses

ALLEN      General Employees

WARD       Top Bosses

JONES      Managers

 

3. CASE expects datatype consistency, DECODE does not

 

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

 

 

 

SQL> select decode(2,1,1,

  2                 '2','2',

  3                 '3') t

  4  from dual;

 

         T

----------

         2

 

 

SQL> select case 2 when 1 then '1'

  2              when '2' then '2'

  3              else '3'

  4         end

  5  from dual;

            when '2' then '2'

                 *

ERROR at line 2:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

What is the order of execution?

What is the order of execution of below sql??

select, from, join, where, group by, having, order by

When a query is submitted to the database, it is executed in the following order:

1.FROM clause

2.WHERE clause

3.GROUP BY clause

4.HAVING clause

5.SELECT clause

6.ORDER BY clause

So why is it important to understand this?

When a query is executed,

First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given.

Now you have handful of records which are GROUP-ed

And HAVING clause is applied on the result.

As soon as it is completed, the columns mentioned are selected from the corresponding tables.

And finally sorted using ORDER BY clause.

So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Unix basics and interview questions...

"" - double quotes - gives the variable value
'' - single quotes - gives the value inside the quotes
`` - backtits/backquotes - execute the value of variable and returns the result
example
ABC = date
echo "$ABC" -- output : date
echo '$ABC' -- output : $ABC
echo `$ABC` -- output : 19 feb 2014 10:00 am ET

$0 The filename of the current script.
$n These variables correspond to the arguments with which a script was invoked. Here n is a positive decimal number corresponding to the position of an argument (the first argument is $1, the second argument is $2, and so on).
$# The number of arguments supplied to a script.
$* All the arguments are double quoted. If a script receives two arguments, $* is equivalent to $1 $2.
$@ All the arguments are individually double quoted. If a script receives two arguments, $@ is equivalent to $1 $2.
$? The exit status of the last command executed.
$$ The process number of the current shell. For shell scripts, this is the process ID under which they are executing.
$! The process number of the last background command.

grep [option] search_word path
-i - for case sensitive
-c -- to get count
-v -- get the results inversely- means retuns o/p lines which don't contact the search_word

find path search_word what_to_do
nohup command & - used to run the command in background without hangups
command & -- used to run the command in background in Linux - if we use & or bg and if we loged out from session then that process will be killed



How to display the 10th line of a file?
head -10 filename | tail -1
2. How to remove the header from a file?
sed -i '1 d' filename
3. How to remove the footer from a file?
sed -i '$ d' filename
4. Write a command to find the length of a line in a file?
The below command can be used to get a line from a file.
sed –n '<n> p' filename
We will see how to find the length of 10th line in a file
sed -n '10 p' filename|wc -c
5. How to get the nth word of a line in Unix?
cut –f<n> -d' '
6. How to reverse a string in unix?
echo "java" | rev
7. How to get the last word from a line in Unix file?
echo "unix is good" | rev | cut -f1 -d' ' | rev
8. How to replace the n-th line in a file with a new line in Unix?
sed -i'' '10 d' filename       # d stands for delete
sed -i'' '10 i new inserted line' filename     # i stands for insert
9. How to check if the last command was successful in Unix?
echo $?
10. Write command to list all the links from a directory?
ls -lrt | grep "^l"
11. How will you find which operating system your system is running on in UNIX?
uname -a
12. Create a read-only file in your home directory?
touch file; chmod 400 file
13. How do you see command line history in UNIX?
The 'history' command can be used to get the list of commands that we are executed.
14. How to display the first 20 lines of a file?
By default, the head command displays the first 10 lines from a file. If we change the option of head, then we can display as many lines as we want.
head -20 filename
An alternative solution is using the sed command
sed '21,$ d' filename
The d option here deletes the lines from 21 to the end of the file
15. Write a command to print the last line of a file?
The tail command can be used to display the last lines from a file.
tail -1 filename
Alternative solutions are:
sed -n '$ p' filename
awk 'END{print $0}' filename
16. How do you rename the files in a directory with _new as suffix?
ls -lrt|grep '^-'| awk '{print "mv "$9" "$9".new"}' | sh
17. Write a command to convert a string from lower case to upper case?
echo "apple" | tr [a-z] [A-Z]
18. Write a command to convert a string to Initcap.
echo apple | awk '{print toupper(substr($1,1,1)) tolower(substr($1,2))}'
19. Write a command to redirect the output of date command to multiple files?
The tee command writes the output to multiple files and also displays the output on the terminal.
date | tee -a file1 file2 file3
20. How do you list the hidden files in current directory?
ls -a | grep '^\.'

21. List out some of the Hot Keys available in bash shell?
Ctrl+l - Clears the Screen.
Ctrl+r - Does a search in previously given commands in shell.
Ctrl+u - Clears the typing before the hotkey.
Ctrl+a - Places cursor at the beginning of the command at shell.
Ctrl+e - Places cursor at the end of the command at shell.
Ctrl+d - Kills the shell.
Ctrl+z - Places the currently running process into background.
22. How do you make an existing file empty?
cat /dev/null >  filename
23. How do you remove the first number on 10th line in file?
sed '10 s/[0-9][0-9]*//' < filename
24. What is the difference between join -v and join -a?
join -v : outputs only matched lines between two files.
join -a : In addition to the matched lines, this will output unmatched lines also.
25. How do you display from the 5th character to the end of the line from a file?
cut -c 5- filename

26. Display all the files in current directory sorted by size?
ls -l | grep '^-' | awk '{print $5,$9}' |sort -n|awk '{print $2}'

27. Write a command to search for the file 'map' in the current directory?
find -name map -type f

28. How to display the first 10 characters from each line of a file?
cut -c -10 filename

29. Write a command to remove the first number on all lines that start with "@"?
sed '\,^@, s/[0-9][0-9]*//' < filename

30. How to print the file names in a directory that has the word "term"?
grep -l term *
The '-l' option make the grep command to print only the filename without printing the content of the file. As soon as the grep command finds the pattern in a file, it prints the pattern and stops searching other lines in the file.

31. How to run awk command specified in a file?
awk -f filename

32. How do you display the calendar for the month march in the year 1985?
The cal command can be used to display the current month calendar. You can pass the month and year as arguments to display the required year, month combination calendar.
cal 03 1985
This will display the calendar for the March month and year 1985.

33. Write a command to find the total number of lines in a file?
wc -l filename
Other ways to pring the total number of lines are
awk 'BEGIN {sum=0} {sum=sum+1} END {print sum}' filename
awk 'END{print NR}' filename

34. How to duplicate empty lines in a file?
sed '/^$/ p' < filename

35. Explain iostat, vmstat and netstat?
Iostat: reports on terminal, disk and tape I/O activity.
Vmstat: reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat: reports on the contents of network data structures.
36. How do you write the contents of 3 files into a single file?
cat file1 file2 file3 > file
37. How to display the fields in a text file in reverse order?
awk 'BEGIN {ORS=""} { for(i=NF;i>0;i--) print $i," "; print "\n"}' filename
38. Write a command to find the sum of bytes (size of file) of all files in a directory.
ls -l | grep '^-'| awk 'BEGIN {sum=0} {sum = sum + $5} END {print sum}'
39. Write a command to print the lines which end with the word "end"?
grep 'end$' filename
The '$' symbol specifies the grep command to search for the pattern at the end of the line.
40. Write a command to select only those lines containing "july" as a whole word?
grep -w july filename
The '-w' option makes the grep command to search for exact whole words. If the specified pattern is found in a string, then it is not considered as a whole word. For example: In the string "mikejulymak", the pattern "july" is found. However "july" is not a whole word in that string.
41. How to remove the first 10 lines from a file?
sed '1,10 d' < filename
42. Write a command to duplicate each line in a file?
sed 'p' < filename
43. How to extract the username from 'who am i' comamnd?
who am i | cut -f1 -d' '
44. Write a command to list the files in '/usr' directory that start with 'ch' and then display the number of lines in each file?
wc -l /usr/ch*
Another way is
find /usr -name 'ch*' -type f -exec wc -l {} \;
45. How to remove blank lines in a file ?
grep -v ‘^$’ filename > new_filename

46. How to display the processes that were run by your user name ?
ps -aef | grep <user_name>
47. Write a command to display all the files recursively with path under current directory?
find . -depth -print
48. Display zero byte size files in the current directory?
find -size 0 -type f
49. Write a command to display the third and fifth character from each line of a file?
cut -c 3,5 filename
50. Write a command to print the fields from 10th to the end of the line. The fields in the line are delimited by a comma?
cut -d',' -f10- filename
51. How to replace the word "Gun" with "Pen" in the first 100 lines of a file?
sed '1,00 s/Gun/Pen/' < filename
52. Write a Unix command to display the lines in a file that do not contain the word "RAM"?
grep -v RAM filename
The '-v' option tells the grep to print the lines that do not contain the specified pattern.
53. How to print the squares of numbers from 1 to 10 using awk command
awk 'BEGIN { for(i=1;i<=10;i++) {print "square of",i,"is",i*i;}}'
54. Write a command to display the files in the directory by file size?
ls -l | grep '^-' |sort -nr -k 5
55. How to find out the usage of the CPU by the processes?
The top utility can be used to display the CPU usage by the processes.
56. Write a command to remove the prefix of the string ending with '/'.
The basename utility deletes any prefix ending in /. The usage is mentioned below:
basename /usr/local/bin/file
This will display only file
57. How to display zero byte size files?
ls -l | grep '^-' | awk '/^-/ {if ($5 !=0 ) print $9 }'
58. How to replace the second occurrence of the word "bat" with "ball" in a file?
sed 's/bat/ball/2' < filename
59. How to remove all the occurrences of the word "jhon" except the first one in a line with in the entire file?
sed 's/jhon//2g' < filename
60. How to replace the word "lite" with "light" from 100th line to last line in a file?
sed '100,$ s/lite/light/' < filename
61. How to list the files that are accessed 5 days ago in the current directory?
find -atime 5 -type f
62. How to list the files that were modified 5 days ago in the current directory?
find -mtime 5 -type f
63. How to list the files whose status is changed 5 days ago in the current directory?
find -ctime 5 -type f
64. How to replace the character '/' with ',' in a file?
sed 's/\//,/' < filename
sed 's|/|,|' < filename
65. Write a command to find the number of files in a directory.
ls -l|grep '^-'|wc -l
66. Write a command to display your name 100 times.
The Yes utility can be used to repeatedly output a line with the specified string or 'y'.
yes <your_name> | head -100
67. Write a command to display the first 10 characters from each line of a file?
cut -c -10 filename
68. The fields in each line are delimited by comma. Write a command to display third field from each line of a file?
cut -d',' -f2 filename
69. Write a command to print the fields from 10 to 20 from each line of a file?
cut -d',' -f10-20 filename
70. Write a command to print the first 5 fields from each line?
cut -d',' -f-5 filename
71. By default the cut command displays the entire line if there is no delimiter in it. Which cut option is used to supress these kind of lines?
The -s option is used to supress the lines that do not contain the delimiter.
72. Write a command to replace the word "bad" with "good" in file?
sed s/bad/good/ < filename
73. Write a command to replace the word "bad" with "good" globally in a file?
sed s/bad/good/g < filename
74. Write a command to replace the word "apple" with "(apple)" in a file?
sed s/apple/(&)/ < filename
75. Write a command to switch the two consecutive words "apple" and "mango" in a file?
sed 's/\(apple\) \(mango\)/\2 \1/' < filename
76. Write a command to display the characters from 10 to 20 from each line of a file?
cut -c 10-20 filename

77. Write a command to print the lines that has the the pattern "july" in all the files in a particular directory?
grep july *
This will print all the lines in all files that contain the word “july” along with the file name. If any of the files contain words like "JULY" or "July", the above command would not print those lines.
78. Write a command to print the lines that has the word "july" in all the files in a directory and also suppress the filename in the output.
grep -h july *
79. Write a command to print the lines that has the word "july" while ignoring the case.
grep -i july *
The option i make the grep command to treat the pattern as case insensitive.
80. When you use a single file as input to the grep command to search for a pattern, it won't print the filename in the output. Now write a grep command to print the filename in the output without using the '-H' option.
grep pattern filename /dev/null
The /dev/null or null device is special file that discards the data written to it. So, the /dev/null is always an empty file.
Another way to print the filename is using the '-H' option. The grep command for this is
grep -H pattern filename
81. Write a command to print the file names in a directory that does not contain the word "july"?
grep -L july *
The '-L' option makes the grep command to print the filenames that do not contain the specified pattern.
82. Write a command to print the line numbers along with the line that has the word "july"?
grep -n july filename
The '-n' option is used to print the line numbers in a file. The line numbers start from 1
83. Write a command to print the lines that starts with the word "start"?
grep '^start' filename
The '^' symbol specifies the grep command to search for the pattern at the start of the line.
84. In the text file, some lines are delimited by colon and some are delimited by space. Write a command to print the third field of each line.
awk '{ if( $0 ~ /:/ ) { FS=":"; } else { FS =" "; } print $3 }' filename
85. Write a command to print the line number before each line?
awk '{print NR, $0}' filename
86. Write a command to print the second and third line of a file without using NR.
awk 'BEGIN {RS="";FS="\n"} {print $2,$3}' filename
87. How to create an alias for the complex command and remove the alias?
The alias utility is used to create the alias for a command. The below command creates alias for ps -aef command.
alias pg='ps -aef'
If you use pg, it will work the same way as ps -aef.
To remove the alias simply use the unalias command as
unalias pg
88. Write a command to display todays date in the format of 'yyyy-mm-dd'?
The date command can be used to display todays date with time
date '+%Y-%m-%d'

99.For LOOP
1. Rename all ".old" files in the current directory to ".bak":
for i in *.old   do  j=`echo $i|sed 's/old/bak/'`  mv $i $j  done

2. Change all instances of "yes" to "no" in all ".txt" files in the current directory. Back up the original files to ".bak".
for i in *.txt do  j=`echo $i|sed 's/txt/bak/'`  mv $i $j   sed 's/yes/no/' $j > $i  done
3. Loop thru a text file containing possible file names. If the file is readable, print the first line, otherwise print an error message:
for i in `cat file_list.txt` do  if test -r $i  
  then      
   echo "Here is the first line of file: $i"      
   sed 1q $i  
else
echo "file $i cannot be open for reading."      fi  done

How to print/display the first line of a file?
$> head -1 file.txt
$> sed '2,$ d' file.txt
How to print/display the last line of a file?
$> tail -1 file.txt
$> sed -n '$ p' test
How to display n-th line of a file?
$> sed –n '<n> p' file.txt
$> sed –n '4 p' test
$> head -<n> file.txt | tail -1
$> head -4 file.txt | tail -1
How to remove the first line / header from a file?
$> sed '1 d' file.txt
$> sed '1 d' file.txt > new_file.txt
$> mv new_file.txt file.txt
$> sed –i '1 d' file.txt
How to remove the last line/ trailer from a file in Unix script?
$> sed –i '$ d' file.txt
How to remove certain lines from a file in Unix?
$> sed –i '5,7 d' file.txt
How to remove the last n-th line from a file?
$> sed –i '96,100 d' file.txt   # alternative to command [head -95 file.txt]
$> tt=`wc -l file.txt | cut -f1 -d' '`;sed –i "`expr $tt - 4`,$tt d" test

How to check the length of any line in a file?
$> sed –n '<n> p' file.txt
$> sed –n '35 p' file.txt | wc –c
How to check if a file is present in a particular directory in Unix?
$> ls –l file.txt; echo $?
How to check all the running processes in Unix?
$> ps –ef
$> ps aux
$>ps -e -o stime,user,pid,args,%mem,%cpu

Combine multiple Rows to a Column – Oracle
SELECT SUBSTR (SYS_CONNECT_BY_PATH (NAME , ','), 2) FRUITS_LIST
FROM (SELECT NAME , ROW_NUMBER () OVER (ORDER BY NAME ) RN,
COUNT (*) OVER () CNT
FROM FRUITS)
WHERE RN = CNT
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1;

What is command to check space in Unix
df -k

What is command to kill last background Job
kill $!
What is difference between diff and cmp command
cmp -It compares two files byte by byte and displays first mismatch.
diff -It displays all changes required to make files identical.

What does $# stands for
It will return the number of parameters passed as command line argument.


1. Write command to list all the links from a directory?
In this UNIX command interview questions interviewer is generally checking whether user knows basic use of "ls" "grep" and regular expression etc
You can write command like:
ls -lrt | grep "^l"


2. Create a read-only file in your home directory?
This is a simple UNIX command interview questions where you need to create a file and change its parameter to read-only by using chmod command you can also change your umask to create read only file.
touch file
chmod 400 file

3. How will you find which operating system your system is running on in UNIX?
By using command "uname -a" in UNIX

4. How will you run a process in background? How will you bring that into foreground and how will you kill that process?
For running a process in background use "&" in command line. For bringing it back in foreground use command "fg jobid" and for getting job id you use command "jobs", for killing that process find PID and use kill -9 PID command. This is indeed a good Unix Command interview questions because many of programmer not familiar with background process in UNIX.

5. How do you know if a remote host is alive or not?
You can check these by using either ping or telnet command in UNIX. This question is most asked in various Unix command Interview because its most basic networking test anybody wants to do it.


6. How do you see command line history in UNIX?
Very useful indeed, use history command along with grep command in unix to find any relevant command you have already executed. Purpose of this Unix Command Interview Questions is probably to check how familiar candidate is from available tools in UNIX operation system.

7. How do you copy file from one host to other?
Many options but you can say by using "scp" command. You can also use rsync command to answer this UNIX interview question or even sftp would be ok.

8. How do you find which process is taking how much CPU?
By using "top" command in UNIX, there could be multiple follow-up UNIX command interview questions based upon response of this because “TOP” command has various interactive options to sort result based upon various parameter.

9. How do you check how much space left in current drive ?
By using "df" command in UNIX. For example "df -h ." will list how full your current drive is. This is part of anyone day to day activity so I think this Unix Interview question will be to check anyone who claims to working in UNIX but not really working on it.

10. What is the difference between Swapping and Paging?
Swapping:
Whole process is moved from the swap device to the main memory for execution. Process size must be less than or equal to the available main memory. It is easier to implementation and overhead to the system. Swapping systems does not handle the memory more flexibly as compared to the paging systems.
Paging:
Only the required memory pages are moved to main memory from the swap device for execution. Process size does not matter. Gives the concept of the virtual memory. It provides greater flexibility in mapping the virtual address space into the physical memory of the machine. Allows more number of processes to fit in the main memory simultaneously. Allows the greater process size than the available physical memory. Demand paging systems handle the memory more flexibly.

1. What is difference between ps -ef and ps -auxwww?
This is indeed a good Unix Interview Command Question and I have faced this issue while ago where one culprit process was not visible by execute ps –ef command and we are wondering which process is holding the file.
ps -ef will omit process with very long command line while ps -auxwww will list those process as well.

2. How do you find how many cpu are in your system and there details?
By looking into file /etc/cpuinfo for example you can use below command:
cat /proc/cpuinfo

3. What is difference between HardLink and SoftLink in UNIX?
I have discussed this Unix Command Interview questions  in my blog post difference between Soft link and Hard link in Unix

4. What is Zombie process in UNIX? How do you find Zombie process in UNIX?
When a program forks and the child finishes before the parent, the kernel still keeps some of its information about the child in case the parent might need it - for example, the parent may need to check the child's exit status. To be able to get this information, the parent calls 'wait()'; In the interval between the child terminating and the parent calling 'wait()', the child is said to be a 'zombie' (If you do 'ps', the child will have a 'Z' in its status field to indicate this.)
Zombie : The process is dead but have not been removed from the process table.

5. What is "chmod" command? What do you understand by this line “r-- -w- --x?

6. There is a file some where in your system which contains word "UnixCommandInterviewQuestions” How will find that file in Unix?
By using find command in UNIX for details see here 10 example of using find command in Unix

7. In a file word UNIX is appearing many times? How will you count number?
grep -c "Unix" filename

8. How do you set environment variable which will be accessible form sub shell?
By using export   for example export count=1 will be available on all sub shell.

9. How do you check if a particular process is listening on a particular port on remote host?
By using telnet command for example “telnet hostname port”, if it able to successfully connect then some process is listening on that port. To read more about telnet read networking command in UNIX

10. How do you find whether your system is 32 bit or 64 bit ?
Either by using "uname -a" command or by using "arch" command.


1. How do you find which processes are using a particular file?
By using lsof command in UNIX. It wills list down PID of all the process which is using a particular file.

2. How do you find which remote hosts are connecting to your host on a particular port say 10123?
By using netstat command execute netstat -a | grep "port" and it will list the entire host which is connected to this host on port 10123.

3. What is nohup in UNIX?

4. What is ephemeral port in UNIX?
Ephemeral ports are port used by Operating system for client sockets. There is a specific range on which OS can open any port specified by ephemeral port range.

5. If one process is inserting data into your MySQL database? How will you check how many rows inserted into every second?
Purpose of this Unix Command Interview is asking about "watch" command in UNIX which is repeatedly execute command provided with specified delay.

6. There is a file Unix_Test.txt which contains words Unix, how will you replace all Unix to UNIX?
You can answer this Unix Command Interview question by using SED command in UNIX for example you can execute sed s/Unix/UNIX/g fileName.

7. You have a tab separated file which contains Name, Address and Phone Number, list down all Phone Number without there name and Addresses?
To answer this Unix Command Interview question you can either you AWK or CUT command here. CUT use tab as default separator so you can use
cut -f3 filename.

8. Your application home directory is full? How will you find which directory is taking how much space?
By using disk usage (DU) command in Unix for example du –sh . | grep G  will list down all the directory which has GIGS in Size.

9. How do you find for how many days your Server is up?
By using uptime command in UNIX

10. You have an IP address in your network how will you find hostname and vice versa?
This is a standard UNIX command interview question asked by everybody and I guess everybody knows its answer as well.
By using nslookup command in UNIX, you can read more about networking command in UNIX here.

ORA_ROWSCN: The pseudo Column

ORA_ROWSCN is a pseudocolumn of any table which has the most recent change information to a given row.

Oracle has an ORA_ROWSCN pseudocolumn which reports the last known change time for a row in a table. The “time” shows a commit SCN number of last transaction modifying the row, not a real timestamp though. It is important to note that unless the ROWDEPENDECIES are enabled, then the last SCN is known only at data block level, not row level, rowscn’s for all rows in a block would report whatever SCN is in the last change SCN in block header.
ORA_ROWSCN and SCN_TO_TIMESTAMP. Using this ORA_ROWSCN column and SCN_TO_TIMESTAMP function, the last date or timestamp can be found when a table or record updated.

Here is an example to get the ORA_ROWSCN value when a row updated.

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

hum…”not absolutely precise”. Should I understand “absolutely not precise” ?

Let’s see that.

create table test(col1 varchar2(10)); insert into test values('1'); insert into test values('2'); select ora_rowscn, col1 from test; ORA_ROWSCN COL1 ---------- ---------- 9.8420E+12 1

Let’s try to have a better display:

select  to_char(cast(scn_to_timestamp(ora_rowscn) as date),           'DD/MM/YYYY HH24:MI:SS') ora_rowscn_date,         col1 from    test; ORA_ROWSCN_DATE     COL1 ------------------- ---------- 02/08/2013 16:41:30 1 02/08/2013 16:41:30 2

Ok. something different if I commit?

ORA_ROWSCN_DATE     COL1 ------------------- ---------- 02/08/2013 16:43:30 1 02/08/2013 16:43:30 2

Yes. ORA_ROWSCN is set to insertion SCN first, then to commit SCN.

What about inserting a new value?

insert into test values('3'); ORA_ROWSCN_DATE     COL1 ------------------- ---------- 02/08/2013 16:43:30 1 02/08/2013 16:43:30 2 02/08/2013 16:43:30 3

Ha? Funny, may last insertion SCN is the same as the last commited one. Seems to be copied.

Ok. something different if I commit?

ORA_ROWSCN_DATE     COL1 ------------------- ---------- 02/08/2013 16:46:48 1 02/08/2013 16:46:48 2 02/08/2013 16:46:48 3

All records are now updated with the latest commit SCN.

Looking at the extract of the doc above, I understand this is because all the records are stored into the same block. Let’s check that:

select  to_char(cast(scn_to_timestamp(ora_rowscn) as date),           'DD/MM/YYYY HH24:MI:SS') ora_rowscn_date,         col1,         dbms_rowid.rowid_block_number(rowid) block_number from    test; ORA_ROWSCN_DATE     COL1       BLOCK_NUMBER ------------------- ---------- ------------ 02/08/2013 16:46:48 1                 90625 02/08/2013 16:46:48 2                 90625 02/08/2013 16:46:48 3                 90625

That’s true (it was obvious with so little number of record :) ).

Let’s see if we have records scatterred into different blocks:

create table test2 as select rownum col1 from dba_objects where rownum <=3000; col ora_rowscn_date for a25       select  dbms_rowid.rowid_block_number(rowid) block_number,         to_char(cast(scn_to_timestamp(ora_rowscn) as date),           'DD/MM/YYYY HH24:MI:SS') ora_rowscn_date,         count(*) from    test2 group by         dbms_rowid.rowid_block_number(rowid),         to_char(cast(scn_to_timestamp(ora_rowscn) as date),           'DD/MM/YYYY HH24:MI:SS') ;   BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:38:22              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658

What about inserting a new value?

insert into test2 values (999); BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:38:22              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47486 05/08/2013 15:41:42                1        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658

I can see my new value stored into a new block whith insertion SCN.
What happen if I commit?

BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:38:22              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658        47486 05/08/2013 15:42:18                1

I get the commit SCN for my record.
What about inserting another new record?

insert into test2 values (1000); BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:38:22              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658        47486 05/08/2013 15:42:18                2

I can see my second new record stored into the same block as the first one. The SCN did not change.
What happen if I Commit?

BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:38:22              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658        47486 05/08/2013 15:43:56                2

I get the commit SCN for my two new records in the same block.

What about updating an existing record?

update test2 set col1=9999 where col1=1;    BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:38:22              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658        47486 05/08/2013 15:43:56                2

Nothing happened, no new SCN. Now I commit.

BLOCK_NUMBER ORA_ROWSCN_DATE             COUNT(*) ------------ ------------------------- ----------        47481 05/08/2013 15:45:41              658        47484 05/08/2013 15:38:22              658        47485 05/08/2013 15:38:22              368        47482 05/08/2013 15:38:22              658        47483 05/08/2013 15:38:22              658        47486 05/08/2013 15:43:56                2

I can see my commit SCN for all the records in the same block as the record I updated.

This clearly shows that the ora_rowscn is tracked at block level.

When inserting data, all the inserted records get the same SCN as the first inserted record, when they go into the same block(s), until commit.
When commit happens, all the records in the block(s) where data has been inserted get the same commit SCN.

When updating data, all the updated records do not get their SCN updated, until commit command is ran.
When commit happens, all the records in the same block as the updated record get the commit SCN.

SCN_TO_TIMESTAMP

SCN_TO_TIMESTAMP is a new function, in Oracle 10g, which is used to convert the SCN value generated, using ORA_ROWSCN coumn, into timestamp. SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP datatype. This function is useful any time you want to know the timestamp associated with an SCN.

Here we pass the scn value generated in the above query.

SELECT scn_to_timestamp(353845494) FROM emp WHERE empno=7839;

SCN_TO_TIMESTAMP(353845494)
------------------------------------------------
02-SEP-08 03.20.20.000000000 PM

SCN_TO_TIMESTAMP function can also be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row.

SELECT scn_to_timestamp(ORA_ROWSCN) FROM emp WHERE empno=7839;

SCN_TO_TIMESTAMP(ORA_ROWSCN)
-------------------------------------------------------
02-SEP-08 03.20.20.000000000 PM