Auto-Changing SQL Prompt

You have to insert the following line of code in glogin.sql which is usually found in $ORACLE_HOME/sqlplus/admin


set termout off
set echo off
define X=NotConnected
define Y=DBNAME

Column Usr New_Value X
Column DBName New_Value Y


Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;

Select Global_Name DBNAME from Global_Name;

set termout on
set sqlprompt '&X@&Y> '

Connect as an Oracle User Without Knowing the Password

SQL> alter user ldbo grant connect through ksh;

User altered.

SQL> connect n/ksh$1#@apx1112srv;
Connected.
SQL> show user
USER is "LDBO"


2. If we can allow ourselves to change the password, but don’t want to change it permanently, there is a way to change the password back.
The DBA_USERS table contains the PASSWORD column. This column contains the encrypted password of the user.
Follow these steps to change the user’s password:
Get the encrypted password of the user from DBA_USERS table and save it.
Change the password by using the “alter user <user> identified by <pwd>” command.
Perform the following step to change the password back:
Use the “alter user <user> identified by values <encrypted_password>” command.
Use the encrypted password you have and pay attention to the “values” keyword in the command, it specifies that the password given in the command is already encrypted.