How to set the SQLPLUS prompt style in Oracle

Question: In my daily work, I think MySQL plus is used in 99% to log on to the database and manage, optimize, and configure the database. If there are many databases, all of them are unified SQL after connection>, it may happen that we do not know which database to connect, when switching users frequently, it may be unclear which user is currently in use. Next I will introduce how to configure the sqlplus login configuration file.

System: RedHat Linux 5.4
Database: Oracle 10G
Tool: sqlplus

I. Global Mode
What is global mode? After configuring sqlplus to log on to the configuration file, log on to the database in any directory, entering sqlplus in any directory will load this prompt] The effect can be displayed
1. Location of glogin. SQL configuration file
A: $ ORACLE_HOME/sqlplus/admin/glogin. SQL
Run vim $ ORACLE_HOME/sqlplus/admin/glogin. SQL
2. Add a string and use the default variable.
Locate the last line of the file, and press enter to start with another line to add the following string
Set sqlprompt "_ user' @ '_ connect_identified>"
Meaning: _ user represents the current login username _ connect_identified connection string name. In glogin. SQL, set the global sqlplus prompt to indicate that user and connect identified are the default variables of sqlplus.
Note: The setting takes effect at the sqlplus prompt in all directories. @ Replace [$/#/& all]. Entering sqlplus from another directory also loads the prompt variable.
3. Save & Exit
4. sqlplus/as sysdba
The current sqlplus prompt has changed. sys indicates that the current logon is an administrator, and mdsoss indicates the name of my database connection string, in this way, we will not mix the database when logging on to several databases.
Sys @ mdsoss>

Ii. Local Mode
What is the local mode? It only works in the current directory. For example, we configure login in the current directory. SQL file, then entering sqlplus in the current directory will load login. the SQL file prompt will take effect, and other directories will not take effect.
1. Create a login. SQL file in the current directory.
Example: vim/home/oracle/login. SQL
2. Edit the login. SQL file and add a line of strings
Set sqlprompt "_ user' @ '_ connect_identified>"
Meaning: _ user represents the current login username _ connect_identified connection string name. In glogin. SQL, set the global sqlplus prompt to indicate that user and connect identified are the default variables of sqlplus.
Note: The setting takes effect at the sqlplus prompt in the current directory. @ Replace [$/#/& all]. Entering sqlplus from another directory does not load the prompt variable.
3. Save & Exit
4. sqlplus/as sysdba
Sys @ mdsoss>
5. Local beta version
If we log on to sqlplus in another directory, we have found the difference and restored to the original state.
Sqlplus/as sysdba
SQL>

3. Specify the user name and Instance name Method
1. Edit the glogin. SQL and login. SQL files, and add the following content at the end.
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 from v $ instance;
Set sqlprompt '& user_name @ & instance_name>'
Note: -- user_name indicates the login user name, And instance_name indicates the login Instance name sys @ mdsoss >@you can replace [$/#/& all]
2. Use sqlplus to log on to the database
In this way, you can specify the user name and Instance name when logging on to and switching users.
Enter value for user: liusheng
Enter value for instance_name: leonarding
Liusheng @ leonarding>

Summary: using the above methods can make us feel comfortable in the database ocean. We can also add them during normal exercises to form a habit, in this way, when you connect to different databases, you won't know which database to connect.

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings