Forum Stats

  • 3,758,932 Users
  • 2,251,475 Discussions
  • 7,870,439 Comments

Discussions

sqlcl 18.4 show parameters not working

daniel_hauke
daniel_hauke Member Posts: 211 Bronze Badge
edited Feb 1, 2019 10:43AM in SQLcl

Hello everybody,

i am a bit confused. But it seems that the command show parameter for e.g.: show parameter processes isn't working in sqlcl.

When i execute this command nothing is shown, i only get an empty prompt back.

The same command works fine with sqlplus or SQL Developer.

Can anybody tell me if this is a wanted behaviour or is this a bug.

I am working under OL 7.6 and i am using sqlcl Version 18.4. My Oracle Database are all on 12.1.0.2

Greetings Daniel

Glen Conway

Best Answer

  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Feb 1, 2019 4:09AM Accepted Answer

    Hi Glen,

    i found the problem. The issue occured due to a incorrectly set environment variable, the variable NLS_LANG.

    We are setting this variable in our bash_profile when connecting to our server. We have our own environmet scripts to set different variables which are needed. In this second script we are setting a new, wrong NLS_LANG parameter.

    So this behaviour was due to wrong set environement variable NLS_LANG.

    With the following NLS_LANG parameter set, the show parameter command is not working:

    NLS_LANG=American_America.WE8ISO8859P15SYS @ db_orcl >show parameter processesSYS @ db_orcl >

    Setting the NLS_LANG parameter to this one and the show parameter command is working:

    NLS_LANG=American_AmericaSYS @ db_orcl >show parameter processes;NAME                      TYPE    VALUE------------------------- ------- -----aq_tm_processes           integer 1db_writer_processes       integer 7gcs_server_processes      integer 0global_txn_processes      integer 1job_queue_processes       integer 1000log_archive_max_processes integer 4processes                 integer 1000

    So i fixed this in our scripts on all our database machines. Now the command works fine.

    Thanks!

    Greetings Daniel

    Glen Conway

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jan 30, 2019 12:12PM

    It should work fine if you have the correct privilege.  Make sure your user has been granted "select on v_$parameter".

  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Jan 31, 2019 1:30AM

    Hi Geln,

    i have done this with the sys user. I am also able to do a select on the v_$parameter View. So this is not a problem with wrong privileges.

    If i issue the following command, it won't show me anything:

    In SQLcl:

    SYS @ dbe001e >show parameter trace

    SYS @ dbe001e >show parameters TRACE

    SYS @ dbe001e >select * from v_$parameter;

      NUM NAME                             TYPE VALUE         DISPLAY_VALUE   DEFAULT_VALUE   ISDEFAULT   ISSES_MODIFIABLE   ISSYS_MODIFIABLE   ISPDB_MODIFIABLE   ISINSTANCE_MODIFIABLE   ISMODIFIED   ISADJUSTED   ISDEPRECATED   ISBASIC   DESCRIPTION                                                                 UPDATE_COMMENT                HASH   CON_ID

       41 lock_name_space                     2                               NULL            TRUE        FALSE              FALSE              FALSE              FALSE                   FALSE        FALSE        TRUE           FALSE     lock name space used for generating lock names for standby/clone database                           1022980314        0

       42 processes                           3 1000          1000            0               FALSE       FALSE              FALSE              FALSE              FALSE                   FALSE        FALSE        FALSE          TRUE      user processes                                                                                      4162014761        0

       43 sessions                            3 1568          1568            4294967295      TRUE        FALSE              IMMEDIATE          TRUE               TRUE                    FALSE        FALSE        FALSE          TRUE      user and system sessions                                                                            3194028855        0

       44 timed_statistics                    1 TRUE          TRUE            FALSE           TRUE        TRUE               IMMEDIATE          TRUE               TRUE                    FALSE        FALSE        FALSE          FALSE     maintain internal timing statistics                                                                 2224114877        0

       45 timed_os_statistics                 3 0             0               0               TRUE        TRUE               IMMEDIATE          TRUE               TRUE                    FALSE        FALSE        FALSE          FALSE     internal os statistic gathering interval in seconds                                                 2425331770        0

       46 resource_limit                      1 TRUE          TRUE            TRUE            TRUE        FALSE              IMMEDIATE          TRUE               TRUE                    FALSE        FALSE        FALSE          FALSE     master switch for resourc

    In SQLPLUS:

    SQL> show parameters trace

    NAME                                 TYPE        VALUE

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

    log_archive_trace                    integer     0

    sec_protocol_error_trace_action      string      TRACE

    sql_trace                            boolean     FALSE

    trace_enabled                        boolean     TRUE

    tracefile_identifier                 string

    For both connection i used sql / as sysdba or sqlplus / as sysdba.

    Greetings Daniel

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jan 31, 2019 11:54AM

    Hmmm, of course, my test cases were a bit different.

    In a Windows 10 environment with an Oracle 18.3 Instant Client and Java 1.8u201 this is what I saw yesterday:

    1) Oracle Developer Day VM under Virtual Box with Oracle18.3 -- running "show parameters" on the HR schema works fine.

    2) Oracle Developer Day VM under Virtual Box with Oracle12.2 -- running "show parameters" on the HR schema works fine.

    3) Locally installed Oracle 11g XE -- running "show parameters" on the HR schema produced nothing.  To work, must grant select on v_$parameter to HR.

    Full disclosure:

    1) I run SQL Developer with a default environment (System Properties -> Environment Variables...)

    2) But for SQLcl I always run a setup script (bat file) first.

    3) The setup script I run by default (where "show parameters" works fine with SQLcl 18.4) is (edited for brevity and annonimity)

    set TNS_ADMIN=C:\app\xxxx\product\18.3.0.0\instantclient_18_3set ORACLE_HOME=C:\app\xxxx\product\18.3.0.0\instantclient_18_3set JAVA_HOME=C:\Program Files\Java\jdk1.8.0_201set SQLCL_HOME=C:\Tools\sqlcl\18_4\sqlcl\binset SQLPATH=C:\Tools\sqlscriptsset STARTUP_SQLCL=C:\Tools\sqlscriptsPATH=%ORACLE_HOME%;%JAVA_HOME%\bin;C:\windows\system32;C:\windows;C:\Users\xxxx\AppData\Local\Microsoft\WindowsApps;cd C:\Tools\sqlcl\18_4\sqlcl\bin

    4) But if I try to use an old setup script I configured specifically for the Oracle 11g XE install, then "show parameters" does not work on 11g XE!  Here is that script:

    PATH=C:\oraclexe\app\oracle\product\11.2.0\server\bin;C:\ProgramData\Oracle\Java\javapath;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\ThinkPad\Bluetooth Software\;C:\Program Files\ThinkPad\Bluetooth Software\syswow64;C:\Program Files\Broadcom\WHL\;C:\Program Files\Broadcom\WHL\SysWow64\;set ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\serverset ORACLE_SERVICE=xeset ORACLE_SID=xeset TNS_ADMIN=C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMINset ORA_TZFILE=set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

    and, for completeness, where the java.exe in C:\ProgramData\OracleJava\javapath is:

    C:\ProgramData\Oracle\Java\javapath>java -versionjava version "1.8.0_201"Java(TM) SE Runtime Environment (build 1.8.0_201-b09)Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

    So, I have no real explanation as to why the old setup script fails us with regard to "show parameters".  I have not maintained it, but it mostly still seems to work.  There is no jre in XE's ORACLE_HOME.  Perhaps there is some bug in the old 11.2.0.2 jdbc driver that got fixed so more recent jdbc driver versions work fine?

    Good luck with working through this issue in your environment!

    daniel_hauke
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Feb 1, 2019 4:09AM Accepted Answer

    Hi Glen,

    i found the problem. The issue occured due to a incorrectly set environment variable, the variable NLS_LANG.

    We are setting this variable in our bash_profile when connecting to our server. We have our own environmet scripts to set different variables which are needed. In this second script we are setting a new, wrong NLS_LANG parameter.

    So this behaviour was due to wrong set environement variable NLS_LANG.

    With the following NLS_LANG parameter set, the show parameter command is not working:

    NLS_LANG=American_America.WE8ISO8859P15SYS @ db_orcl >show parameter processesSYS @ db_orcl >

    Setting the NLS_LANG parameter to this one and the show parameter command is working:

    NLS_LANG=American_AmericaSYS @ db_orcl >show parameter processes;NAME                      TYPE    VALUE------------------------- ------- -----aq_tm_processes           integer 1db_writer_processes       integer 7gcs_server_processes      integer 0global_txn_processes      integer 1job_queue_processes       integer 1000log_archive_max_processes integer 4processes                 integer 1000

    So i fixed this in our scripts on all our database machines. Now the command works fine.

    Thanks!

    Greetings Daniel

    Glen Conway
  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Feb 1, 2019 10:43AM

    Excellent!  The same fix (dropping the ".WE8MSWIN1252" from NLS_LANG) also works in my setup script when using an 11g XE ORACLE_HOME.  Thanks!