Work with Sqlplus Mdsoss/mdsoss, so find out sqlplus about how to set the Sqlplus prompt style
12638 Read 1 comments 2012-03-28 leonarding
Category: Oracle
Absrtact: Everyone in the daily work, I think 99% will use sqlplus tools to log into your database, the database management, tuning, configuration, operation and maintenance. Then if there are more than n databases, we are all unified after the connection sql> prompt, it is possible to occur at present do not know which library to connect to, when we switch users are very frequent when it is also possible to not know which user is currently in use, I would like to introduce you, How to configure the login configuration file of the Sqlplus tool to dynamically load the look of our prompt.
System: RedHat Linux 5.4
Database: Oracle 10G
Tool: Sqlplus
First, the global mode
What is Global mode: When we have configured the Sqlplus tool to load the configuration file, regardless of the directory in which to log in the database, you set the "Sqlplus prompt look, in any directory into the Sqlplus tool will load this prompt appearance" effect can be rendered
1.glogin.sql the location of the configuration file
Answer: $ORACLE _home/sqlplus/admin/glogin.sql
Vim $ORACLE _home/sqlplus/admin/glogin.sql
2. Add the string, take the default variable
Navigate to the last line of the file, and add the following string at the beginning of the other line
Set sqlprompt "_user ' @ ' _connect_identifier>"
or set sqlprompt ' [Email protected]&_connect_identifier> '
Meaning: _user represents the current login user name, _connect_identifier represents the current database connection string name, in Glogin.sql set the global Sqlplus prompt like user and connect Identifier is the default variable for Sqlplus
Note: This is set to log in all Directories Sqlplus tool will take effect, @ Replace with "￥/#/& can", from another directory into the Sqlplus also load the prompt variable
3. Save & Exit
4.sqlplus/as SYSDBA
See now the sqlplus prompt has changed, sys means that the current login is the administrator user, Mdsoss is my database connection string name, so we can log in several databases without confusion.
[Email protected]>
Second, local mode
What is local mode: only in the current directory is valid, for example, we configure the Login.sql file in the current directory, then the current directory into the Sqlplus tool will load Login.sql file prompt will take effect, the other directory login does not take effect
1. Create the Login.sql file in the current directory
Example Vim/home/oracle/login.sql
2. Edit the Login.sql file and add a line of string
Set sqlprompt "_user ' @ ' _connect_identifier>"
or set sqlprompt ' [Email protected]&_connect_identifier> '
Meaning: _user represents the current login user name, _connect_identifier represents the current database connection string name, in Glogin.sql set the global Sqlplus prompt like user and connect Identifier is the default variable for Sqlplus
Note: The settings at this time are logged in the current directory Sqlplus tool prompt to take effect, @ Replace with "￥/#/& can", from another directory entered sqlplus do not load the prompt variable
3. Save & Exit
4.sqlplus/as SYSDBA
[Email protected]>
5. Local effective Beta
If we log in to the other directory sqlplus, it is we have found a different point, revert to the original state
Sqlplus/as SYSDBA
Sql>
Iii. specifying the user name and instance name method
1. Edit the 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 ' &[email protected]&instance_name> '
Note:--user_name represents the user name currently logged in, instance_name represents the currently logged on instance name
[Email protected]> @ replaceable "￥/#/& can be"
2. Log in to the database using Sqlplus
This way, when logging in and switching users, you can specify the user name and the instance name
Enter value for User:liusheng
Enter value for instance_name:leonarding
[Email protected]>
Summary: The use of the above method can let us in the database of the sea, the usual practice can also be added, to form a habit, so that in connection with different databases will not cause do not know which library is connected.
Leonarding Liucheng
2012.03.28
Tianjin &spring
Sharing Technology ~ Fulfilling dreams
Blog:www.leonarding.com
about how to set the Sqlplus prompt style
