I'm looking for the parameter nls_length_semantics from SQL*Plus and SQLDeveloper by using the command
SHOW PARAMETER nls_length_semantics
With both tools I expect to get the same result, since the SHOW PARAMETER command looks in spfile, but I get different outputs. With SQLDeveloper I get BYTE while with SQL*Plus I get CHAR, I've already checked manually the pfile from spfile and the parameter is set to CHAR.
I tried to look in SQLDeveloper > Tools > Preferences > NLS and is set to BYTE, but this setting is for the session as far as I know.
Why don't I get the same output, CHAR?
the SHOW PARAMETER command looks in spfile
No, it queries the SYS.V_$PARAMETER view; from the SQL*Plus documentation:
You need SELECT ON V_$PARAMETER object privileges to use the PARAMETERS clause
If you do an alter session set nls_length_semantics = byte in your SQL*Plus session then your show command will also now say BYTE.
If you query V$PARAMETER directly:
select * from v$parameter where name = 'nls_length_semantics'
then you will see that ISSES_MODIFIABLE is TRUE, and if you do alter session the ISMODIFIED valued changes from FALSE to MODIFIED, and the VALUE changes to match your alter statement.
If you want to see the system (rather than session) value then query NLS_DATABASE_PARAMETERS directly, but the active session value is probably more useful anyway.