Changing SQL*Plus prompt

Below is the straight forward syntax according to your choice.

SQL> set sqlprompt 'nadvi@TESTDATABASE> '

nadvi@TESTDATABASE>

And is below one is querying database to specify exact user & hostname:

SQL> undefine usr db

col usr new_value usr

col db new_value db

set termout off

select lower(user) usr, instance_name db

from v$instance

/

set termout on

set sqlprompt '&&usr.@&&db.> 'SQL> SQL> SQL> SQL>   2    3

USR                            DB

------------------------------ ----------------

sys                            testdatabase

SQL> SQL>sys@testdatabase>