If you want to survive in a Linux or Solaris environment, you have to be adept with at least one command-line text editor. DBAs use text editors on a daily basis to manipulate database initialization files, create scripts to automate tasks, modify OS scheduler jobs, and so on. In these environments, you won’t be an efficient database administrator unless you’re proficient with a text editor.

Dozens of text editors are available. To that end, there have been entire books written on the available text editors. The three most common command-line text editors in use are vi, vim, and emacs. This chapter focuses on the vi text editor (pronounced “vee-eye” or sometimes “vie”). We chose to concentrate on this editor for the following reasons:

• The vi editor is universally available on all Linux/Solaris systems.
• Linux/Solaris technologists tend to use vi more than any other editor.
• You can’t consider yourself a true geek unless you know vi.

The goal of this chapter is to give you enough information to efficiently use the vi editor. We don’t cover every facet of vi; instead, we focus on the features that you’ll use most often to perform daily editing tasks. When you first use vi, you might wonder why anybody would use such a confusing text-editing tool. To neophytes, many aspects of vi initially seem counterintuitive.

Not to worry; with some explanation, examples, and hands-on practice, you’ll learn how to use this editing tool to efficiently create and manipulate text files. This chapter contains more than enough material to get you started with vi. The problems described are the most commonly encountered editing tasks.

If you are new to vi, we strongly encourage you to not just read the solutions in this chapter but to actually start up a vi session and practice entering the commands shown in the examples. It’s like riding a bicycle; you can’t learn how to ride until you physically get on the bike and attempt to go forward. It’s the same with vi. You can’t just read about how to use this tool; you have to type commands before the learning takes place.

4-1. Creating a File

Problem

You need to create a text file.

Solution

To create a file named foo.txt, run the vi utility from the command line, as shown here:

$ vi foo.txt

You should now see a blank screen with several tilde (~) graphemes displayed in the far-left column of the screen. Within a file being edited by vi, the ~ character indicates a line that has no text in it. Depending on your version of vi, you might see the name of your file in the bottom-left corner:

"foo.txt" [New file]

To enter text, first type i. The lowercase i puts the editor in insert mode. You can now enter text into the file. To save your changes and exit from vi, first press Escape to get out of insert mode and into command mode, and then type :wq for write and quit:

:wq

You should now be back at the OS command prompt. You can verify that the new file has been created with the ls command:

$ ls foo.txt
foo.txt

How It Works

The most common way to start vi is to provide it with a file name to operate on:

$ vi <filename>

Several options are available when first invoking vi. Table 4-1 lists some of the more commonly used command-line choices.

Once you start a vi session, it’s critical to understand that there are two distinct operating modes:

• Command mode
• Insert mode

The vi editor behaves very differently depending on its mode. When you first enter vi, you are in command mode by default. In this mode, you can enter commands that control the behavior of vi. For example, you can issue commands to do the following:

• Save a file
• Enter insert mode
• Exit vi

When in command mode, everything you enter is interpreted as a command by vi. You can’t enter text into your file while in command mode; you must place vi in insert mode before you can start entering text. Table 4-2 lists several methods of placing vi in insert mode. Keep in mind that these commands are case sensitive. For example, the A command comprises pressing the Shift and A keys simultaneously.

The easiest way to change from command mode to insert mode is to press i on the keyboard. You can then begin entering text at the place onscreen where your cursor is currently located. When in vi insert mode, you can perform two activities:

• Enter text
• Exit from insert mode

While in insert mode, you should see text at the bottom of your screen indicating that you are in the correct mode (this may vary depending on whether you’re using vi or vim):

-- INSERT --

You can now begin typing text.

To exit from insert mode (and back to command mode), press Escape. There’s nothing wrong with pressing Escape multiple times (other than wasting energy). If you are already in command mode and press Escape, you may hear a bell or a beep.

You can exit from vi (back to the OS prompt) after you are in command mode. To save the file and exit, type :wq (write quit):

:wq

If you made changes to a file and want to exit without saving, type :q!, as shown here:

:q!

Table 4-3 details some of the more common exit methods. Keep in mind that you have to be in command mode before you can type a vi exit command. If you don’t know what mode you’re in, press Escape to ensure that you’re in command mode. Notice that these commands are case sensitive. For example, the ZZ command is executed by simultaneously pressing Shift and the Z key twice.

4-2. Maneuvering Within a File

Problem

You want to navigate efficiently within a text file while editing with vi.

Solution

The most intuitive way to move around is by using the up/down/right/left arrows. These keys will work whether you are in command mode or insert mode. However, you might encounter some keyboards on which the up/down/left/right arrows don’t work. In those cases, you have to use the J, K, H, and L keys to move you down, up, left, and right, respectively. You must be in command mode to navigate with these keys. If you’re in insert mode and try to use these keys, you’ll see a bunch of jjj kkkkk hhh llll letters onscreen.

Using these keys may seem cumbersome at first. However, you’ll notice after some time that you can navigate quickly using these keys because you don’t have to move your fingers from their natural typing positions.

How It Works

You can use a myriad of commands for moving around in a text file. Although some of these commands may seem confusing, the navigational commands will soon become second nature to you with a little practice. Keep in mind that the vi editor was designed to allow you to perform most tasks without having to move your hands from the standard keyboard position.

Table 4-4 contains commonly used commands to navigate within vi. Remember that you must be in command mode for these keystrokes to work. Notice that these commands are case sensitive. For example, to navigate to the top of the page, use the 1G command, which is composed of first pressing the 1 key and then simultaneously pressing the Shift and G keys.

4-3. Copying and Pasting

Problem

You want to copy and paste text from one section of a file to another.

Solution

Use the yy command to yank (copy) lines of text. Use the p command to put (paste) lines of text elsewhere in the file. As with all vi commands, ensure that you are in command mode (press Escape) before using a command. The following example copies five lines of text from the current line that the cursor is on and four lines below the current line (for a total of five lines):

5yy

You should see an informational line at the bottom of the screen indicating success (or not) of placing the copied lines in the copy buffer:

5 lines yanked

To paste the lines that have been copied, use the p command. To put the lines beneath the current line your cursor is on, ensure that you are in command mode and issue the p command:

p

You should see an information line at the bottom that indicates the lines were pasted below the line your cursor is on:

5 more lines

How It Works

Copying and pasting are two of the most common tasks of editing a file. Sometimes you may want to cut and paste. This task is similar to the copying and pasting example in the “Solution” section of this recipe. Instead of using the yy command, use the dd (delete) command. For example, to cut five lines of text (inclusive with the line your cursor is currently on), issue the dd command:

5dd

You should see a message at the bottom of the screen indicating that the lines have been cut (deleted):

5 fewer lines

Those lines are now in the buffer and can be pasted anywhere in the file. Navigate to the line you want to place the previously cut lines after, and press the p command:

p

You should see an informational line at the bottom that indicates the lines were pasted after the line your cursor is on:

5 more lines

There are many commands for cutting and pasting text. Table 4-5 describes the copying, cutting, and pasting commands. Notice that these commands are case sensitive. For example, use the X command to delete one character to the left of the cursor, which means pressing the Shift and X keys simultaneously.

4-4. Manipulating Text

Problem

You wonder whether there are some commands to modify the text you’re working on, such as changing a character from lowercase to uppercase.

Solution

Use the ~ (tilde) command to change the case of a character. For example, say you have a string in a file with the text of oracle and you want to change it to Oracle. Place your cursor over the o character. Press Escape to ensure that you’re in command mode. Type the ~ character (which requires you to press the Shift key and the ~ key at the same time). You should see the case of the character change from o to O.

How It Works

Several commands are available for manipulating text. Table 4-6 lists common options used to change text. Notice that these commands are case sensitive. For example, the C command is executed by pressing the Shift and C keys simultaneously.

4-5. Searching for and Replacing Text

Problem

You want to search for all occurrences of a string and replace it with another string.

Solution

If you want to search only for a string, use the / command. The following example searches for the string ora01 in the file:

/ora01

To search for the next occurrence of the string, use the n (next) command:

n

To search backward for a previous occurrence of the string, use the N command:

N

If you want to search for text and replace it, use the s option to search for text and replace it with an alternate string. The following example searches for the string ora01 and replaces it with ora02 everywhere in the file:

:%s/ora001/ora02/g

All occurrences of ora01 should now be displayed as ora02.

How It Works

Searching for strings is one of the most common tasks you’ll perform while editing database initialization files. Table 4-7 lists some of the more common options for searching for text.

4-6. Inserting One File into Another

Problem

While within vi, you want to copy in another file that exists in the current working directory.

Solution

Use the :r command to read in a file. This has the effect of copying in a file and pasting it starting at the current cursor location. This example reads a file named tnsnames.ora into the current file:

:r tnsnames.ora

The previous example assumes that the tnsnames.ora file is in your current working directory. If the file you want to bring in is not in your current working directory, you have need to specify a path name. This example reads in a file from a directory that is not the current working directory:

:r /oracle/product/11.1/network/admin/tnsnames.ora

If you have an OS variable that contains a path, you can use it directly. This example copies in a file contained in a path specified by a variable:

:r $TNS_ADMIN/tnsnames.ora

How It Works

You’ll often need to insert the content of a file into the current file you’re editing. Doing so is a quick and easy way to add text to your current file that you know is stored correctly in a separate file.

You have a few other interesting ways to read in files. This example copies in a file and places it at the beginning of the current file:

:0r tnsnames.ora

The following bit of code reads in the file at the end of the current file:

:$r tnsnames.ora

4-7. Joining Lines

Problem

You have one line of text just after the current line you are editing. You want to join the text after the current line to the end of the current line.

Solution

First, ensure that you are in command mode by pressing Escape. Place your cursor on the first line that you want to join with the line after it. Type the J (capital J) command to join the end of the current line to the start of the line after it.

An example helps to illustrate this concept. Say you have these two sentences in a file:

select table_name
from dba_tables;

If you want to join the first line to the second line, place your cursor anywhere on the first line and type the following:

J

You should now see both lines joined together:

select table_name from dba_tables;

How It Works

You’ll often use the J command to join two lines of code together in a text file. You can also join any number of consecutive lines. For example, suppose that you have the following three lines in a file:

Select
username
from dba_users;

You want the three lines to be joined together on one line. First, place your cursor anywhere on the first line and then type the following while in command mode:

3J

You should now see the three lines joined together, as shown here:

select username from dba_users;

4-8. Running Operating System Commands

Problem

While editing text within vi, you want to run an OS command.

Solution

First make sure you that are in command mode by entering Escape. Use the :! command to run OS commands. For example, the following bit of code runs the OS date command without exiting vi:

:!date

Here is the output for this example:

Sat Feb 10 14:22:45 MST 2008
~
Hit ENTER or type command to continue

Press Enter or Return to get back into vi command mode. To read the output of date directly into the file you’re editing, use this syntax:

:r !date

How It Works

Running OS commands from within vi saves you the hassle of having to exit the utility, run the OS command, and then re-enter the utility. DBAs commonly use this technique to perform tasks such as listing files in a directory, printing the date, or copying files.

The following example runs the ls (list) command from within vi to view files in the current working directory:

:!ls

Once the file of interest is identified, you can read it in with the :r syntax. This example reads the script1.sql file into the file currently being edited:

:r script1.sql

If you want to temporarily place yourself at the shell prompt and run several OS commands, type your favorite shell with the :! syntax. The following example enters the Bash shell:

:!bash

To return to vi, use the exit command to log out of the shell. At this point, you need to press Enter or Return to return to vi command mode:

Hit ENTER or type command to continue

4-9. Repeating a Command

Problem

You are typing commands over and over again. You wonder whether there is a way to repeat the previously entered command.

Solution

Use the . (period) command to repeat the previously entered command. For example, suppose you delete a large section of code, but want to delete only 10 lines at a time. To achieve this, first ensure you’re in command mode (by pressing Escape) and then enter the following:

10dd

To repeat the previous command, type a period:

.

You should see another 10 lines deleted. This technique allows you to quickly repeat the previously run command.

How It Works

You can use the . command to repeat any previously typed command and you will save a great deal of time and typing when using it. If you often retype lengthy commands, consider creating a shortcut to the keystrokes (see recipe 4-13 for details).

4-10. Undoing a Command

Problem

You want to undo the last command you typed.

Solution

To undo the last command or text typed, use the u command. Make sure that you are in command mode and then type u, as shown here:

u

You should see the effects of the command that was typed in previously to the u command being undone.

How It Works

The u command is handy for undoing the previous command. If you want to undo all commands entered on one line, use the U command. Your cursor must be on the last line you changed for this command to work.

If you want to undo changes since the last time you saved the file, type the following:

:e!

Sometimes the previous command is handy if you want to undo all edits to a file and start over. This is quicker than exiting the file with the :q! (quit without saving) command and reopening the file.

4-11. Displaying Line Numbers

Problem

You want to display line numbers in your text file.

Solution

Use the set number command. The following command changes the screen to display the line number on the left side of each row:

:set number

You should now see line numbers on the left side of the screen. The following is a snippet from the init.ora file with the set number option enabled:

1 db_name=RMDB1
2 db_block_size=8192
3 compatible=10.2.0.1.0
4 pga_aggregate_target=200M
5 workarea_size_policy=AUTO
6 sga_max_size=400M
7 sga_target=400M
8 processes=200

How It Works

When you deal with files, it is nice to see a line number to assist with debugging. Use the set number and set nonumber commands to toggle the number display.

4-12. Automatically Configuring Settings

Problem

You want to configure vi to start up with certain settings. For example, you want vi to always start up in the mode of displaying line numbers.

Solution

If you want to customize vi to automatically show line numbers, create a file named .exrc in your home directory and place the desired settings within it. The following example creates a file named .exrc in the home directory:

$ vi $HOME/.exrc

Enter the following text in the .exrc file:

set number

From now on, every time you start vi, the .exrc file is read, and the settings within it are reflected in the files being edited.

How It Works

Setting the line numbers to automatically appear is just one aspect that you can configure in the .exrc file. To view all settable attributes in your environment, issue the following command within vi:

:set all

Here is a very small snippet of the output:

--- Options ---
ambiwidth=single   joinspaces            softtabstop=0
noautoindent       keywordprg=man -s     startofline
noautoread         nolazyredraw          swapfile
noautowrite        lines=30              swapsync=fsync
noautowriteall     nolist                switchbuf=
background=light   listchars=eol:$       tabstop=8

Options that expect a value contain an equals (=) sign in the output. To view the current setting of a feature, use set and the option name. This example displays the term setting:

:set term
term=cygwin

To view which options are different from the defaults, use the set command with no options:

:set

If you use vim, you can place commands in the vim .vimrc startup file. The vim editor also executes the contents of the .exrc file if one is present.

4-13. Creating Shortcuts for Commands

Problem

You are using a certain command over and over again and you wonder whether there is a way to create a shortcut for the command.

Solution

Use the map command to create a shortcut for a sequence of keystrokes. One set of keystrokes that is typed often is xp, which transposes two characters (it performs a delete and then a put). This example creates a macro for the xp command:

:map t xp

You can now use the t command to perform the same function as the xp command.

How It Works

Mapping commands is a useful way of creating shortcuts for frequently used sequences of keystrokes. If you want mappings defined for each vi session, place the mappings in your .exrc file (see recipe 4-12 for details).

To view all defined mappings, type :map without any arguments:

:map

Here is some sample output:

up      ^[OA    k
down    ^[OB    j
left    ^[OD    h
right   ^[OC    l
t       t       xp

To disable a mapping, use the :unmap command. The following example disables the t mapping:

:unmap t

4-14. Setting the Shell Default Text Editor

Problem

You’re editing the cron table on a new database server. The default editor used for cron is emacs, but you want to set your default editor to be vi.

Solution

Use the export command to set the default editor. The following example assumes that you’re using the Bash shell and sets the default editor to the vi utility:

$ export EDITOR=vi

You can verify that the variable has been set as follows:

$ echo $EDITOR
vi

If you’re using the C shell, you can set the EDITOR variable as follows:

$ setenv EDITOR vi

How It Works

Some utilities such as cron inspect the OS EDITOR variable to determine which editor to use (some older systems use the VISUAL variable as well). The following lines can be placed in the HOME/.bashrc startup file to ensure that the editor is automatically set when logging in:

export EDITOR=vi
export VISUAL=$EDITOR

We recommend that you set both the EDITOR and VISUAL variables because some utilities (such as SQL*Plus) reference one or the other.

4-15. Setting the SQL*Plus Text Editor

Problem

You start a SQL*Plus session and want to edit a file using the vi editor. However, you notice that you’re placed within an unfamiliar editor when you issue the EDIT command:

$ sqlplus / as sysdba
SQL> edit test
test.sql: No such file or directory
?
q

In this situation, you should set the default SQL*Plus text editor to be vi.

Solution

You can specify the default text editor used by SQL*Plus in one of two ways:

• Set the OS EDITOR variable (see recipe 4-14 for details). By default, SQL*Plus will use the editor specified within the EDITOR variable.
• Define the SQL*Plus _EDITOR variable. Setting this variable overrides any setting in the OS EDITOR variable.

This example sets the default editor to be used by a SQL*Plus session:

SQL> define _EDITOR=vi

Now when you subsequently edit a file, the vi editor is invoked by default:

SQL> edit test
~
"test.sql" [New File]

You can verify that the editor has been set by using the DEFINE command and specifying only the name of the variable you’re interested in viewing:

SQL> DEFINE _EDITOR
DEFINE _EDITOR         = "vi" (CHAR)

If you just type in DEFINE by itself, all defined variables will display:

SQL> DEFINE
DEFINE _DATE           = "18-APR-15" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
...
DEFINE _EDITOR         = "vi" (CHAR)

How It Works

The solution section demonstrated how to set the default editor for a SQL*Plus session. You’ll most likely want to have the default editor defined automatically for you so that you don’t have to manually set the editor within each session. To automatically have the _EDITOR variable set, define the _EDITOR variable within the glogin.sql server profile file or the login.sql user profile file. For example, the following line can be placed in either the glogin.sql or login.sql file:

define _EDITOR=vi

The glogin.sql file is located in the ORACLE_HOME/sqlplus/admin directory. In Linux or Solaris, the login.sql file is executed if it exists in a directory contained within the SQLPATH OS variable. If the SQLPATH variable hasn’t been defined, SQL*Plus will look for login.sql in the current working directory from which SQL*Plus was invoked.

If the _EDITOR SQL*Plus variable is not set, the OS EDITOR variable will be used to set the SQL*Plus editor. If the EDITOR variable is not set, the VISUAL variable setting will be used. If neither the EDITOR nor VISUAL variable is set, the ed editor will be used as the default editor within SQL*Plus.

4-16. Toggling Syntax Text Color

Problem

You’re using vim or vi to edit a file, and the text appears as multicolored. You’re having a hard time reading the text and want to turn the coloring feature off.

Solution

Within the editor while in command mode, press the Shift key and : simultaneously. This action should place your prompt at the bottom-left side of the screen. You should see the prompt to the right of a : sign. To turn off the syntax coloring, type syntax off and press Return:

:syntax off

You should see the text font colors displayed in white now. If you want to turn the syntax coloring back on, do so as follows:

:syntax on

If you want the syntax coloring to be automatically disabled, you can place the syntax off command in the vim .vimrc startup file or the vi .exrc startup file (see recipe 4-12 for details).

How It Works

Depending on your environment, when you edit a file, some of its text may be shown in various colors. For example, a line that starts with a # sign will be interpreted as a comment line and will therefore appear as dark blue text. This is known as syntax coloring. Sometimes this syntax-colored text can be hard to read, depending on the terminal (e.g., it’s hard to see dark blue against a black terminal background). In these situations, the easiest way to resolve this issue is to turn off the syntax coloring.

If you prefer the syntax coloring to be enabled but are having a difficult time viewing text of a particular color, you can adjust specific syntax coloring schemes. This line of code sets the font color of a comment line to be dark gray:

highlight Comment ctermbg=DarkGray

There are various other colors you can control; for example:

highlight Constant ctermbg=Blue
highlight Cursor ctermbg=Green
highlight Normal ctermbg=Black
highlight Special ctermbg=DarkMagenta
highlight NonText ctermbg=Black

You may want to adjust these colors depending on your terminal type or your ability to see certain colors. For most files that a DBA would edit, the syntax coloring can be more distracting than helpful. However, if you’re editing a programming language source code file (e.g., Java) you’ll most likely find the syntax coloring quite helpful for identifying segments of code such as comments, constants, variables, and so on.