Find the NLS settings on a connection

When making a connection to the database, below query will tell its process IDs on both client and db server:

SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid');

       SID    SERIAL#  PROCESSID   CLIENTPID
------------ ------------  ----------------    -----------------
       180      42146      48824530         26093
26093 is the process ID for this sqlplus session on client/Apps side.
48824530 is the process ID on database server for this session.

In general,
V$SESSION.SID and V$SESSION.SERIAL#  – process ID in database
V$PROCESS.SPID – Shadow process ID on the database server
V$SESSION.PROCESS – Client process ID

If start a new session on the same client where sqlplus is still running, you can verify it is running:
$ ps -ef | grep 26093
ebsdev  26093 25836  0 15:09 pts/1    00:00:00 sqlplus

To find what NLS settings are used to support the connection, run below lines:
$ ps ewww 26093 | tr ' ' '\n' | grep NLS
-DNLS_ASIA
FORMS_OVERRIDE_ENV=NLS_LANG,NLS_NUMERIC_CHARACTERS,NLS_SORT,NLS_DATE_LANGUAGE,NLS_DATE_FORMAT,FORMS_USER_DATE_FORMAT,FORMS_USER_DATETIME_FORMAT,FORMS_OUTPUT_DATE_FORMAT,FORMS_OUTPUT_DATETIME_FORMAT,FORMS_ERROR_DATE_FORMAT,FORMS_ERROR_DATETIME_FORMAT,FORMS_TZFILE,FORMS_DATETIME_SERVER_TZ,FORMS_DATETIME_LOCAL_TZ,FORMS_USER_CALENDAR
NLS_DATE_FORMAT=DD-MON-RR
NLS_DATE_LANGUAGE=
NLS_LANG=American_America.WE8ISO8859P1
NLS_NUMERIC_CHARACTERS=.,
NLS_SORT=BINARY
ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Do the same for other sessions. For example, if you are interested on the listener session, find its process ID first and then see its NLS settings by "ps ewww".
$ ps -ef|grep tnslsnr
ebsdev  23845     1  0 Aug01 ?        00:00:00 $ORACLE_HOME/bin/tnslsnr APPS_EBSDEV -inherit

Similarly, you can do the same with 48824530 on database server to find its NLS settings.

$ ps ewww 48824530 | tr ' ' '\n' | grep NLS
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Below query can help to find parameters for database characterset. Ideally all of three sides shall be consistent.

SQL> SELECT db.parameter as parameter, db.value as database_value,
s.value as session_value,  i.value as instance_value
FROM
nls_database_parameters db
LEFT JOIN
nls_session_parameters s
ON s.parameter = db.parameter
LEFT JOIN
nls_instance_parameters i
ON i.parameter = db.parameter
ORDER BY parameter;

PARAMETER DATABASE_VALUE SESSION_VALUE INSTANCE_VALUE
------------------ ------------------------- ------------------------- -------------------
NLS_CALENDAR GREGORIAN GREGORIAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_COMP BINARY BINARY binary
NLS_CURRENCY $ $
NLS_DATE_FORMAT DD-MON-RR DD-MON-RR DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN AMERICAN
NLS_DUAL_CURRENCY $ $
NLS_ISO_CURRENCY AMERICA AMERICA
NLS_LANGUAGE AMERICAN AMERICAN AMERICAN
NLS_LENGTH_SEMANTICS BYTE BYTE BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE FALSE FALSE
NLS_NUMERIC_CHARACTERS ., ., .,
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_SORT BINARY BINARY binary
NLS_TERRITORY AMERICA AMERICA america
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM TZR TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR HH.MI.SSXFF AM TZR

When NLS_LANG is unset, it defaults to US7ASCII.

Without proper setting on ORA_NLS10, package UTL_FILE may not work correctly. See https://erpondb.blogspot.com/2016/10/troubleshhot-utlfile-error.html