10 Replies Latest reply: Nov 14, 2011 2:36 PM by Aketi Jyuuzou RSS

    Let us share everyone's Login.sql

    Aketi Jyuuzou
      Let us share everyone's Login.sql :-)
      This is my "Login.sql"
      set serveroutput on size 1000000
      set pagesize 35
      set lines 100
      set numwidth 9
      
      --set colsep ■
      set colsep "  "
      
      --set null ヌル
      set null null
      
      set trimout off
      set trimspool on
      set arraysize 100
      set verify off
      
      --set SQLP "_date> "
      set SQLP "_USER> "
      --set SQLP "&_USER.@&_CONNECT_IDENTIFIER> "
      
      --sho user
      --sho user
      --sho user
        • 1. Re: Let us share everyone's Login.sql
          523861
          Here's my windows one.

          I'm regularly connecting to many different databases so I have added a bit of a "whoami" at the start of the login.sql:

          def _editor = "C:\Program Files\Notepad++\notepad++.exe"
          set feedback on;
          set pagesize 999;
          set linesize 132;
          --alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
          set serveroutput on size 1000000;
          set autocommit off;
          set pagesize 100;
          set long 20000;
          set ARRAYSIZE 5000
          set time off;
          col notes format a50;
          col txt format a30;
          
          select           'You are:                 '||upper(user) from dual
          union all select 'Logged into:             '||upper(sys_context('userenv','db_unique_name')) from dual
          union all select 'Current edition name is: '||upper(sys_context('userenv','current_edition_name')) edition FROM DUAL
          union all select 'Session ID:              '||upper(sys_context('userenv','sessionid')) from dual
          union all select null from dual
          union all select '---Database Version---' from dual
          union all select banner from v$version;
          
          set exitcommit off;
          I also have a whoami.sql for those times when I forget which database I'm looking at or if I'm mucking around with different editions etc:

          select           'You are:                 '||upper(user)  from dual
          union all select 'Logged into:             '||upper(sys_context('userenv','db_unique_name')) from dual
          union all select 'Session ID:              '||upper(sys_context('userenv','sessionid')) from dual
          union all select 'Current edition name is: '||upper(sys_context('userenv','current_edition_name')) edition FROM DUAL
          union all select 'Database Version:        '||banner from v$version where rownum = 1;
          I'm looking forward to seeing what others have setup :-)
          • 2. Re: Let us share everyone's Login.sql
            Billy~Verreynne
            Mine is a huge list of COL entries - as I use SQL*Plus for 99% of all my Oracle work, including viewing data. So I often edit the file to add column formats into it for the output to render correctly (using a linesize of 200).

            The only other entry is setting the NLS date format. As for the database being connected to - that is displayed via the KDE console tab (it uses tabbed windows). So no need to change the SQL*Plus prompt (and consume valuable real estate space from the display).
            • 3. Re: Let us share everyone's Login.sql
              Sven W.
              Mine is pretty simple. THe usually SET commands are done inside the scripts, if they are called on a regular basis.
              set sqlprompt "_user'@'_connect_identifier> "
              • 4. Re: Let us share everyone's Login.sql
                SomeoneElse
                SET serveroutput on
                SET linesize 10000
                SET trimspool on
                SET tab off
                SET verify off
                SET numwidth 20
                ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


                The long linesize (along with set tab off) allows me to display very wide rows in a sql plus window. And I have the width setting sized accordingly in the DOS window.

                Only problem is that it really screws up DESC which adjusts its output to fit the linesize.  I have another script to work around that.

                And of course the nls_date_format so I always see the full contents of a date column.
                • 5. Re: Let us share everyone's Login.sql
                  Centinul
                  Like Billy I have a large number of column formatting statements but here is the rest:
                  set TERMOUT OFF
                  
                  SET pagesize 5000 
                  SET LONG 10000 
                  SET linesize 160 
                  
                  set editfile "C:\users\xxxxxx\sqlbuffer.sql"
                  define _editor='C:\Program Files\TextPad 5\TextPad.exe'
                  SET feedback ON 
                  alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss'; 
                  
                  host TITLE &&_USER@&&_CONNECT_IDENTIFIER
                  
                  set trimspool on
                  SET TERMOUT ON
                  • 6. Re: Let us share everyone's Login.sql
                    Marwim
                    SET NUMWIDTH 11
                    SET PAGESIZE 1000
                    SET AUTOCOMMIT OFF
                    SET LINESIZE 1000
                    SET ECHO OFF
                    SET HEADING OFF
                    SET FEEDBACK OFF
                    EXEC dbms_session.set_identifier('Igor Fjodorowitsch Strawinski');
                    EXEC dbms_application_info.set_client_info('L''Oiseau de Feu');
                    SET TERMOUT OFF
                    DEFINE gname=dummy
                    COLUMN global_name new_value gname     
                         SELECT  LOWER(user)  || '@' || 
                                   SYS_CONTEXT ('USERENV', 'DB_NAME') global_name
                         FROM     dual;
                    HOST TITLE &gname
                    SET SQLPROMPT '&gname> '
                    SET TERMOUT ON
                    SELECT     TO_CHAR(SYSDATE, '* *  DD.MM.YYYY  * *  HH24:MI:SS  * * ') "Connected"
                    FROM      dual;
                    SET HEADING ON
                    SET FEEDBACK ON
                    SET SQLNUMBER OFF
                    SET SERVEROUTPUT ON SIZE UNL
                    I set identifier and client info to find my sessions easily in the session monitor. The settings are for "normal" SQL statements, other scripts usually have a different set, which is standard within our company so each script will give the same output regardless of which user is executing it.

                    Regards
                    Marcus
                    • 7. Re: Let us share everyone's Login.sql
                      Billy~Verreynne
                      Nice touch setting client info data in v$session - but I prefer that to be consistently done in application code instead.

                      SQL*Plus sessions and their owners are usually easily identified (typically these are Window clients and their client drivers passes the NetBIOS name of the client).

                      What is not so easy to identify is what code other sessions (jobs especially) are executing and at what processing step they are.
                      • 8. Re: Let us share everyone's Login.sql
                        Marwim
                        Hello Billy,

                        this is done for SQL*PLUS sessions that I use for ad hoc queries, to try out something etc. and also when I use SQL Developer. Since most of our developers set a personal client info when they start SQL*PLUS it's easy to find them.
                        typically these are Window clients and their client drivers passes the NetBIOS name of the client
                        Yes, but the identifier is something like a nick or alias each developer can choose himself ;-)
                        What is not so easy to identify is what code other sessions (jobs especially) are executing and at what processing step they are.
                        We usually use dbms_application_info in our applications to identify the current task and, if possible, the progress in long_ops. The first a script does should be something like
                        dbms_application_info.set_module('report','claim_analysis_xy');
                        or
                        dbms_application_info.set_module('Reg test','done 0/387);
                        and it should regularly update it. "Should", because it is not enforced, though mostly done.

                        Regards
                        Marcus
                        • 9. Re: Let us share everyone's Login.sql
                          Billy~Verreynne
                          Marwim wrote:
                          typically these are Window clients and their client drivers passes the NetBIOS name of the client
                          Yes, but the identifier is something like a nick or alias each developer can choose himself ;-)
                          Not when these are corporate tied down and secured machines where even the wallpaper is fixed and cannot be changed. ;-)

                          And you can use NetBIOS to lookup connections on the Windows machine and if it is connected to a NT domain, get the name used for connecting and authenticating with the domain controller.
                          • 10. Re: Let us share everyone's Login.sql
                            Aketi Jyuuzou
                            Thanks I have studied a lot from this thread :-)

                            http://www.shift-the-oracle.com/sqlplus/system-variable/
                            I know Auto Commit, today.
                            SET AutoCommit on
                            And any defined variable of SQLPlus
                            http://www.shift-the-oracle.com/sqlplus/command/define/predefined-variable.html