sqlplus question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sqlplus question

  1. #1
    Join Date
    Oct 2002
    Posts
    284

    sqlplus question

    Hello all...

    ORacle 9.2

    how do i get username@service_name.hostname when i login sqlplus everytime. ?...

    For ex: my username = system
    host = pedpiora2
    service_name= heat

    so i should get system@heat.pedpiora2> ...

    the reason for this is, i have to login to many DB's and sometimes, i don't know on which database iam working and with what account .

    anyhelp is appreciated

    Ron

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Put this in your login.sql file

    set termout off
    col user_name new_value user_name
    col db_name new_value db_name
    select lower(user) user_name,
    ltrim(rtrim(lower(global_name))) db_name
    from global_name;
    set sqlprompt "&user_name@&db_name> "
    set termout on
    set trimspool on
    set trimout on
    set serveroutput on

    regards
    Hrishy

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Use this script an place it in the working directory of the sqlplus, meaning %oracle_home%/../dbs, call it login.sql

    Code:
    define _editor=vi
    
    set serveroutput on size 1000000
    
    set trimspool on
    set long 5000
    set linesize 100
    set pagesize 9999
    
    column plan_plus_exp format a80
    
    column global_name new_value gname
    set termout off
    select lower(user) || '@' ||
    decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD',
    '8i', global_name ) global_name from global_name;
    set sqlprompt '&gname> '
    set termout on
    and as well as place this one.. call it..connect.sql

    Code:
    set termout off
    connect &1
    @login
    set termout on
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Oct 2002
    Posts
    284
    Hello all

    This is what iam getting now
    ********************************
    system@j2eetest.us.oracle.com> connect sys@j2eetest as sysdba
    Enter password: *******
    Connected.
    system@j2eetest.us.oracle.com>
    *********************************

    Though, i connected as 'sys', i still see it say that iam connected as 'system';


    any clues ?..

    Thanks
    Ron

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    login.sql is only called when sqlplus is started, NOT after a connect statement. You need to call it again after the connect.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . or use Tarry's script as:
    @connect user@sid
    note the @ as first character.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width