Oracle about setting the Sqlplus prompt style

Everyone in the daily work, I think 99% are used to Sqlplus to login database, the database management, tuning, configuration. So if there's a lot of databases, we are connected after all is unified sql>, it is possible that the current does not know the connection to the library, when we switch users very often also may not know the current use of that user, I would like to introduce the following, How to configure the sqlplus login configuration file

System: RedHat Linux 5.4

Database: Oracle 10G

Tools: Sqlplus

One, the global model

What is the global mode: When we have configured the sqlplus login configuration file, regardless of which directory to log into the database, you set the "Sqlplus prompt, in any directory entry sqlplus will load this prompt appearance" effect can be rendered

Location of the 1.glogin.sql configuration file

Answer: $ORACLE _home/sqlplus/admin/glogin.sql

$ORACLE _home/sqlplus/admin/glogin.sql with Vim

2. Added string, take default variable

Navigate to the last line of this file and add the following string at the beginning of the other line

Set sqlprompt "_user ' @ ' _connect_identified>"

Meaning: _user on behalf of the current login username _connect_identified connection string name, set global Sqlplus prompt in Glogin.sql user and connect identified is the sqlplus default variable

Note: At this point, the Sqlplus prompt is set to take effect in all directories, @ Replace "￥/#/& can", enter Sqlplus from another directory, and also load the prompt variable

3. Save & Exit

4.sqlplus/as SYSDBA

Now that the Sqlplus prompt has changed, sys means that the administrator is currently logged on, and Mdsoss is my database connection string, so that we don't get mixed up when we log in to several databases.

Sys@mdsoss>

Second, local mode

What is local mode: only valid in the current directory, for example, we configure the Login.sql file in the current directory, then the current directory into the Sqlplus will load the Login.sql file prompt will take effect, the other directory does not take effect

This paper url:http://www.bianceng.cn/database/oracle/201410/45635.htm

1. Create the Login.sql file in the current directory

Example Vim/home/oracle/login.sql

2. Edit Login.sql file, add one line string

Set sqlprompt "_user ' @ ' _connect_identified>"

Meaning: _user on behalf of the current login username _connect_identified connection string name, set global Sqlplus prompt in Glogin.sql user and connect identified is the sqlplus default variable

Note: At this point, the Sqlplus prompt is set to take effect in the current directory, @ Replace "￥/#/& can", enter from other directory sqlplus do not load the prompt variable

3. Save & Exit

4.sqlplus/as SYSDBA

Sys@mdsoss>

5. Local effective test version

If we log in to another directory sqlplus, this is what we have found different points, revert to original state

Sqlplus/as SYSDBA

Sql>

Third, specify the user name and instance name method

1. Edit Glogin.sql and Login.sql files and add the following on the last side

Set term off

Define User_name= ""

Define Instance_name= ""

Column user_name New_value user_name

Column instance_name New_value instance_name

Select lower (user) user_name, instance_name instance_name from v$instance;

Set Sqlprompt ' &user_name@&instance_name> '

Note:--user_name represents the login username, instance_name represents the login instance name sys@mdsoss> @ replaceable "￥/#/& can"

2. Log in to the database using Sqlplus

This way the user name and instance name can be specified when logging in and switching users

Enter value for User:liusheng

Enter value for instance_name:leonarding

Liusheng@leonarding>

Summary: Use the above method can let us in the database ocean, the usual practice can also add, forming habits, so when connecting different databases will not cause do not know which library is connected.