SQL results format differs between servers.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL results format differs between servers.

  1. #1
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88

    SQL results format differs between servers.

    Here's a good one. I've been losing sleep over something I hope is very simple:

    Formatting from SQL*Plus is different between Test and Prod. DB. When I execute a simple "show parameter NLS" command on test it comes back nice and neat, on production it looks as if it's wrapping.

    I have looked and compared EVERY PARAMETER that I know of (the obvious ones I checked first "linesize", "pagesize").

    If I change the linesize on production from 80 to 100 then the format is nice and neat (even though test's linesize is still 80). I don't want to have two different linesizes per server if possible.

    The different formatting is killing my monitoring queries.

    Here's an Example:
    When I execute the "show paramater NLS" here are the results:

    Test:

    SQL> show parameter nls

    NAME TYPE VALUE
    ------------------------------------ ----------- --------------------------
    nls_calendar string
    nls_comp string
    nls_currency string
    nls_date_format string
    nls_date_language string
    nls_dual_currency string
    nls_iso_currency string
    nls_language string AMERICAN
    nls_length_semantics string BYTE
    nls_nchar_conv_excp string FALSE
    nls_numeric_characters string

    NAME TYPE VALUE
    ------------------------------------ ----------- --------------------------
    nls_sort string
    nls_territory string AMERICA
    nls_time_format string
    nls_time_tz_format string
    nls_timestamp_format string
    nls_timestamp_tz_format string


    Production:
    SQL> show parameter nls

    NAME TYPE
    ------------------------------------ --------------------------------
    VALUE
    ------------------------------
    nls_calendar string

    nls_comp string

    nls_currency string

    nls_date_format string

    nls_date_language string

    NAME TYPE
    ------------------------------------ --------------------------------
    VALUE
    ------------------------------

    nls_dual_currency string

    nls_iso_currency string

    nls_language string
    AMERICAN
    nls_length_semantics string
    BYTE

    NAME TYPE
    ------------------------------------ --------------------------------
    VALUE
    ------------------------------
    nls_nchar_conv_excp string
    FALSE
    nls_numeric_characters string

    nls_sort string

    nls_territory string
    AMERICA
    nls_time_format string

    NAME TYPE
    ------------------------------------ --------------------------------
    VALUE
    ------------------------------

    nls_time_tz_format string

    nls_timestamp_format string

    nls_timestamp_tz_format string

    SQL>
    Last edited by smoothyc; 04-25-2011 at 10:40 AM.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    may be you are formatting TYPE column for some other query. Connect to the new terminal in prod and check once.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    You can check glogin.sql too.

  4. #4
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88
    Hey vnktummala, I'm not formatting any columns. When I login via SQL*Plus on the server, or from Oracle client, or from a third party development tool (ie. Toad) it formats the same.

    My guess is there is some type of database parameter / option that is causing this. I have compare the complete init.ora parameter, set and show parameters between the servers but nothing stands out.

    When I change my linesize to 132 on production (set linesize 132) before executing my query the format is perfect (yet on test the format is perfect with the linesize set to 80).

  5. #5
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88
    Hey Stecal -

    I check the glogin.sql script and both servers have the same following settings:

    -- For backward compatibility
    SET PAGESIZE 14
    SET SQLPLUSCOMPATIBILITY 8.1.7

    -- Used by Trusted Oracle
    COLUMN ROWLABEL FORMAT A15

    -- Used for the SHOW ERRORS command
    COLUMN LINE/COL FORMAT A8
    COLUMN ERROR FORMAT A65 WORD_WRAPPED

    -- Used for the SHOW SGA command
    COLUMN name_col_plus_show_sga FORMAT a24

    -- Defaults for SHOW PARAMETERS
    COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
    COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

    -- Defaults for SET AUTOTRACE EXPLAIN report
    COLUMN id_plus_exp FORMAT 990 HEADING i
    COLUMN parent_id_plus_exp FORMAT 990 HEADING p
    COLUMN plan_plus_exp FORMAT a60
    COLUMN object_node_plus_exp FORMAT a8
    COLUMN other_tag_plus_exp FORMAT a29
    COLUMN other_plus_exp FORMAT a44

  6. #6
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88
    FOUND THE PROBLEM!!!

    This was a good one. The cursor_sharing parameter were different on the two servers (production = 'similar' and test = 'exact').

    There is an Oracle bug (Metalink 241464.1) that will cause formatting issue if the cursor_sharing parameter is set to 'similar'.... see below....

    Thanks for your help and I hope this thread helps someone else.....


    CAUSE DETERMINATION
    ====================
    CURSOR_SHARING = SIMILAR or CURSOR_SHARING = FORCE parameter setting is causing this sqlplus formatting problem. Some code changes in 9203 patchset causes this parameter to mess up SQL*Plus formatting.

    CAUSE JUSTIFICATION
    ====================
    Note 241464.1 - TO_CHAR() , SUBSTR() AND RPAD() in SQL*Plus Display Length Too Long After Upgrading Database Version

    POTENTIAL SOLUTION(S)
    ======================

    alter session set optimizer_mode=choose;
    alter session set cursor_sharing=exact;

    POTENTIAL SOLUTION JUSTIFICATION(S)
    ====================================

    Note 241464.1 - TO_CHAR() , SUBSTR() AND RPAD() in SQL*Plus Display Length Too Long After Upgrading Database Version

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by smoothyc View Post
    This was a good one.
    Indeed!
    Thank you for sharing.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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