5 thoughts on “Login.sql – The Answer To DBA Questions Where, And Who Am I?

  1. Hello Diana.

    Good post. This is the one I use

    REM Disabling the output
    set termout off
    define gname=idle
    column global_name new_value gname

    select REGEXP_SUBSTR(SYS_CONTEXT(‘USERENV’,’HOST’),'(^[a-zA-Z0-9]+)’)||’.’||SYS_CONTEXT(‘USERENV’,’INSTANCE_NAME’)||'(‘|| user||’)’ global_name from dual;
    set sqlprompt ‘&gname> ‘

    REM Enable the output
    define _editor=vi
    set long 99999
    set serveroutput on
    alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;
    set termout on

  2. Thanks Diana,
    I modified glogin.sql to spool SQL commands and their output to file as they go on. It helped me in many situation to analyze command outputs.
    — My code is given below. It will also set prompt to connected instance and user.

    — === Custom code ===
    set serveroutput on size 100000
    set trimspool on
    set long 5000
    set linesize 800
    set pagesize 100
    set pause off
    set termout off
    define gname=idle
    column global_name new_value gname
    column stime new_value sptime
    select upper(user)||’@’||upper(NAME) global_name,
    ‘C:\Adinath\Work\SQLLog\’||upper(user)||’_’||upper(NAME)||’_’||to_char(sysdate,’FMDD_MON_RR_HH12_MI_PM’)||’.txt’ stime
    from v$database;
    set sqlprompt ‘&gname> ‘
    set termout on
    COLUMN INSTANCE_NAME FORMAT A15 HEADING “Instance Name”
    COLUMN HOST_NAME FORMAT A15 HEADING “Server Name”
    COLUMN STARTUP_TIME FORMAT A30 HEADING “Startup Time”
    COLUMN STATUS FORMAT A10 HEADING “Status”
    COLUMN LOGINS FORMAT A15 HEADING “Login Allowed ?”
    COLUMN UPDAYS HEADING “Days up”
    COLUMN VERSION HEADING “Version”
    COLUMN PLATFORM_NAME FORMAT A20 HEADING “Operat. System”
    COLUMN OPEN_MODE FORMAT A15 HEADING “Open Mode”
    COLUMN LOG_MODE FORMAT A15 HEADING “Archive Mode”
    COLUMN CREATED HEADING “Created On”

    SELECT I.INSTANCE_NAME,HOST_NAME,D.PLATFORM_NAME,VERSION,D.OPEN_MODE,D.LOG_MODE,
    STATUS,LOGINS,TO_CHAR(CREATED,’FMDD-MONTH-RRRR’) CREATED,
    TO_CHAR(STARTUP_TIME,’FMDD-MONTH-RRRR HH24:MI’) STARTUP_TIME,TRUNC(SYSDATE)-TRUNC(STARTUP_TIME) UPDAYS
    FROM V$INSTANCE I,V$DATABASE D
    WHERE I.INSTANCE_NAME = D.NAME;
    set pause on
    set pause “”
    prompt Spool file is &sptime
    spool ‘&sptime’

    Thanks,
    Adinath

Leave a Reply

Your email address will not be published. Required fields are marked *