Pro Oracle Database 11g Administration (16 page)

\l

Base name of the shell’s terminal device

\n

Newline

\r

Carriage return

\s

Name of the shell

\t

Time in 24-hour HH:MM:SS format

\T

Time in 12-hour HH:MM:SS format

\@

Time in 12-hour a.m./p.m. format

\A

Time in 24-hour HH:MM format

\u

Current shell

\v

Version of the Bash shell

\V

Release of the Bash shell

\w

Current working directory

\W

Base name of the current working directory

\!

History number of command

\$

If the effective UID is 0, then displays #; otherwise, displays $

The variables available for use with your command prompt vary somewhat by operating system and shell. For example, in a Korn shell environment, the hostname variable displays the server name in the operating system prompt:

$ export PS1="[`hostnamè]$ "

53

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

Customizing Your SQL Prompt

DBAs often use SQL*Plus to perform daily administrative tasks. Often, you’ll work on servers that contain multiple databases. Obviously, each database contains multiple user accounts. When connected to a database, you can run the following commands to verify your username and database connection: SQL> show user;

SQL> select name from v$database;

A more efficient way to determine your username and SID is to set your SQL prompt to display that information. For example:

SQL> SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '

An even more efficient way to configure your SQL prompt is to have it automatically run the SET

SQLPROMPT command when you log in to SQL*Plus. Follow these steps to fully automate this: 1. Create a file named login.sql, and place in it the SET SQLPROMPT command.

2. Set your SQLPATH operating system variable to include the directory location of login.sql. In this example, the SQLPATH operating system variable is set in the

.bashrc operating system file, which is executed each time a new shell is logged in to or started. Here is the entry:

export SQLPATH=$HOME/scripts

3. Create a file named login.sql in the HOME/scripts directory. Place the following line in the file:

SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '

4. To see the result, you can either log out and log back in to your server, or source the .bashrc file directly:

$ . ./.bashrc

Now, log in to SQL. Here is an example of what the SQL*Plus prompt looks like: SYS@devdb1>

If you connect to a different user, this should be reflected in the prompt: SQL> conn system/foo

The SQL*Plus prompt now displays

SYSTEM@devdb1

Setting your SQL prompt is an easy way to remind yourself which environment and user you’re currently connected as. This will help prevent you from accidentally running a SQL statement in the wrong environment. The last thing you want is to think you’re in a development environment, and then discover that you’ve run a script to delete objects while connected in a production environment.

Table 3–2 contains a complete list of SQL*Plus variables that you can use to customize your prompt.

54

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

Table 3–2.
Predefined SQL*Plus Variables

Variable Description

_CONNECT_IDENTIFIER

Connection identifier, such as the Oracle SID

_DATE

Current date

_EDITOR

Editor used by the SQL EDIT command

_O_VERSION

Oracle version

_O_RELEASE

Oracle release

_PRIVILEGE

Privilege level of the current connected session

_SQLPLUS_RELEASE

SQL*Plus release number

_USER

Current connected user

Creating Shortcuts for Frequently Used Commands

In Linux/Unix environments, you can use two common methods to create shortcuts to other commands: create aliases for often-repeated commands, and use functions to create a shortcut for a group of commands. The following sections describe ways you can use these two techniques.

Using Aliases

You’ll often need to navigate to various directories on a server. For example, one such location is the database background process-logging directory. To navigate to this directory, you have to type something similar to this:

$ cd /ora01/app/oracle/admin/O10R24/bdump

You can use the alias command to create a shortcut to accomplish the same task. An
alias
is a simple mechanism for creating a short piece of text that executes other shell commands. This example creates an alias named bdump that changes directories to a background location that is dependent on the value of the ORACLE_SID variable:

$ alias bdump='cd /ora01/app/oracle/admin/$ORACLE_SID/bdump'

Now you can type bdump, which does the same thing as changing your current working directory to the Oracle background dump directory.

To show all aliases that have been defined, use the alias command with no arguments: $ alias

Listed next are some common examples of alias definitions you can use: alias l.='ls -d .*'

55

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

alias ll='ls -l'

alias lsd='ls -altr | grep ^d'

alias bdump='cd /ora01/app/oracle/admin/$ORACLE_SID/bdump'

alias sqlp='sqlplus "/ as sysdba"'

alias shutdb='echo "shutdown immediate;" | sqlp'

alias startdb='echo "startup;" | sqlp'

If you want to remove an alias definition from your current environment, use the unalias command. The following example removes the alias for lsd:

$ unalias lsd

Using a Function

You can also use a function to create command shortcuts. The following line of code creates a simple function named bdump:

$ function bdump { cd /ora01/app/oracle/admin/$ORACLE_SID/bdump; }

You can now type bdump at the command line to change your working directory to the Oracle background dump directory.

Using functions is usually preferable over using aliases. Functions are more powerful than aliases because of features such as the ability to operate on parameters passed in on the command line, and allowing for complex coding.

To demonstrate the power of functions, consider a scenario in which you have different versions of databases installed on a server, and the background-dump destination differs depending on the version.

With a function, you can build in the logic that checks to see which version of the database you’re using and navigates accordingly:

function bdump {

echo $ORACLE_HOME | grep 11 >/dev/null

if [ $? -eq 0 ]; then

lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')

cd $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace

else

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump

fi

} # bdump

The previous lines of function code would be difficult to replicate using an alias. With functions, you can code as much logic as you need and pass in variables if required.

DBAs commonly establish functions by setting them in the HOME/.bashrc file. A better way to manage functions is to create a file that stores only function code, and call that file from the .bashrc file.

It’s also better to store special-purpose files in directories that you’ve created for these files. For example, create a directory named bin under HOME; in the bin directory, create a file named dba_fcns; and place in it your function code. Now, call the dba_fcns file from the .bashrc file. Here is an example of an entry in a .bashrc file:

56

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

. $HOME/bin/dba_fcns

Listed next is a small sample of some of the types of functions you can use:

#-----------------------------------------------------------#

# find largest files below this point

function flf {

find . -ls | sort -nrk7 | head -10

}

#-----------------------------------------------------------#

# find largest directories consuming space below this point

function fld {

du -S . | sort -nr | head -10

}

#------------------------------------------

# view alert log

function valert {

echo $ORACLE_HOME | grep 11 >/dev/null

if [ $? -eq 0 ]; then

lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')

view $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace/alert_$ORACLE_SID.log else

view $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log

fi

} # valert

#-----------------------------------------------------------#

If you ever wonder whether a shortcut is an alias or a function, use the type command to verify a command’s origin. This example verifies that bdump is a function:

$ type bdump

Here is the output:

bdump is a function

bdump ()

{

echo $ORACLE_HOME | grep 11;

if [ $? -eq 0 ]; then

lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]');

cd $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace;

else

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump;

fi

}

Rerunning Commands Quickly

When there are problems with a database server, you need to be able to quickly run commands from the operating system prompt. You may be having some sort of performance issue and want to run commands that navigate you to directories that contain log files, or you may want to display the top consuming processes from time to time. In these situations, you don’t want to waste time having to retype command sequences.

57

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

One timesaving feature of the Bash shell is that it has several methods for editing and rerunning previously executed commands. The following list highlights several options available for manipulating previously typed commands:

• Scrolling with the up and down arrow keys

• Using Ctrl+P and Ctrl+N

• Listing the command history

• Searching in reverse

• Setting the command editor

Each of these techniques is described briefly in the following sections.

Scrolling with the Up and Down Arrow Keys

You can use the up arrow to scroll up through your recent command history. As you scroll through previously run commands, you can rerun a desired command by pressing the Enter or Return key.

If you want to edit a command, use the Backspace key to erase characters, or use the left arrow to navigate to the desired location in the command text. After you’ve scrolled up through command stack, use the down arrow to scroll back down through previously viewed commands.

■
Note
If you’re familiar with Windows, scrolling through the command stack is similar to using the DOSKEY

utility.

Pressing Ctrl+P and Ctrl+N

The Ctrl+P keystroke (pressing the Ctrl and P keys at the same time) displays your previously entered command. If you’ve pressed Ctrl+P several times, you can scroll back down the command stack by pressing Ctrl+N (pressing the Ctrl and N keys at the same time).

Listing the Command History

You can use the history command to display commands that the use previously entered: $ history

Depending on how many commands have previously been executed, you may see a lengthy stack.

You can limit the output to the last
n
number of commands by providing a number with the command.

For example, the following lists the last five commands that were run: $ history 5

58

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

Here is some sample output:

273 cd -

274 grep -i ora alert.log

275 ssh -Y -l oracle 65.217.177.98

276 pwd

277 history 5

To run a previously listed command in the output, use an exclamation point (!, sometimes called the
bang
) followed by the history number. In this example, to run the pwd command on line 276, use ! as follows:

$ !276

To run the last command you ran, use !!, as shown here:

$ !!

Searching in Reverse

Press Ctrl+R, and you’re presented with the Bash shell reverse-search utility: $ (reverse-i-search)`':

From the reverse-i-search prompt, as you type each letter, the tool automatically searches through previously run commands that have text similar to the string you entered. As soon as you’re presented with the desired command match, you can rerun the command by pressing the Enter or Return key. To view all commands that match a string, press Ctrl+R repeatedly. To exit the reverse search, press Ctrl+C.

Setting the Command Editor

You can use the set -o command to make your command-line editor be either vi or emacs. This example sets the command-line editor to be vi:

$ set -o vi

Now, when you press Esc+K, you’re placed in a mode where you can use vi commands to search through the stack of previously entered commands.

For example, if you want to scroll up the command stack, you can use the K key; and similarly, you can scroll down using the J key. When in this mode, you can use the slash (/) key and then type a string to be searched for in the entire command stack.

■
Tip
Before you attempt to use the command editor feature, be sure you’re thoroughly familiar with either the vi or emacs editor.

A short example will illustrate the power of this feature. Say you know that you ran the ls -altr command about an hour ago. You want to run it again, but this time without the r (reverse-sort) option.

To enter the command stack, press Esc+K:

$ Esc+K

59

CHAPTER 3 ■ CONFIGURING AN EFFICIENT ENVIRONMENT

You should now see the last command you executed. To search the command stack for the ls command, type /ls and then press Enter or Return: