Oracle NLS_LANG Setting for Language/Territory/Character Set

Setting NLS_LANG tells Oracle what characterset the client is using so that  Oracle can do conversion if needed from client’s characterset to the database characterset and setting this parameter on the client does not change the client’s characterset. Setting Language and Territory in nls_lang has nothing to do with storing characters in database, it’s controlled by the characterset and of course if the database can store that characterset..Below is the syntax of setting   NLS_LANG .

NLS_LANG=<language>_<territory>.<character set>

Example:  NLS_LANG= BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

To  check session  NLS   session  parameters,( note this doesn’t return the characterset set by NLS_LANG)

SQL> select  *   from   nls_session_parameters ;

To find the NLS_LANG of the database one can run the following SQL:

SQL>select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET','NLS_LANGUAGE',    'LANGUAGE', 'NLS_TERRITORY', 'TERRITORY') name, value from v$nls_parameters WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY');

Sample Output :
NAME                             VALUE
------------          -------------------
LANGUAGE                  AMERICAN
TERRITORY                 AMERICA
CHARACTER SET        WE8MSWIN1252

Setting  NLS_LANG for export/import  :  we encounter character set conversion problems during exporting or importing a database or table(s) then we should check the following information to confirm whether the export/import procedure was performed correctly . 

When exporting/importing one can minimize risk of losing data during import/export by setting NLS_LANG.

1.)  Before starting export set  NLS_LANG to be the same character set of the database being exported which means no conversion takes place, all the data will be stored in the export file as it was stored in the database.

2.) Before starting import set NLS_LANG to be the same value as the it was set during export which means no conversion will take place in the import session, but if the character set of the target database is different the data will automatically be converted when import inserts the data in the database.

3.)  Before starting export set NLS_LANG to be the same character set of the database being imported to which means conversion takes place at this step it will automatically convert during export.

4.) Before starting import set NLS_LANG to be the same value as the it was set during import which means no conversion will take place as it was already converted during export.

5.) Settings on the machine from which u are trying to take the import of the data.Even though the NLSCHAR AND NLS NCHA Settings on the source and destination databases are same unless the console from where u are trying to take export and import also should same other wise u will get all the junk characters .

6.)  Sometimes we make import  and  get  some special character  ( like ?,! )  that means, we need to go 'region settings' and change the location (say)  ”brazil"  and default language make as   “ brazil” . (if we are importing American to brazil).

7.) check  the  export  log file and see what is specified.

Enjoy          J J J

Who is using which UNDO or TEMP segment ?

Undo tablespace is common for all the users for an instance  while temporary tablespace are assigned to users or a single default temporary tablespace is common for all users . To determine determine who is using a particular UNDO or Rollback Segment, use the bwlow query to find it .

SQL> SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser,
          s.program,  r.name undoseg , t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
          FROM sys.v_$rollname  r, sys.v_$session s, sys.v_$transaction t , sys.v_$parameter x
         WHERE s.taddr = t.addr AND  r.usn= t.xidusn(+)  AND  x.name  = 'db_block_size' ;
Output  :
SID_SERIAL    ORAUSER           PROGRAM                          UNDOSEG       Undo
--------------    ------------   ---------------------------------       -----------------   -------
260,7             SCOTT   sqlplus@localhost.localdomain       _SYSSMU4$     8K


To determine the user who is using a TEMP tablespace ,then fire the below query as :

SQL> SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
           a.sid||','||a.serial# SID_SERIAL , a.username, a.program 
           FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
           WHERE p.name  = 'db_block_size'  AND a.saddr = b.session_addr
           ORDER BY b.tablespace, b.blocks; 
Output  :
TABLESPACE    SIZE    SID_SERIAL     USERNAME       PROGRAM
-----------------  -------  --------------      ----------------    --------------------------------
TEMP               24M       260,7                SCOTT        sqlplus@localhost.localdomain
                                     
 

Enjoy         :-) 

How full is the current redo log file?

Here is a query that can tell us how full the current redo log file is. This is useful  when we  need to predict when the next log file will be archived out.

SQL> SELECT le.leseq                      "Current log sequence No",
          100*cp.cpodr_bno/le.lesiz         "Percent Full",
           cp.cpodr_bno                            "Current Block No",
           le.lesiz                                       "Size of Log in Blocks"
           FROM   x$kcccp  cp,    x$kccle  le
           WHERE    le.leseq =CP.cpodr_seq
           AND  bitand(le.leflg,24) = 8 ;

Sample Output :
Current log sequence No      Percent Full        Current Block No         Size of Log in Blocks
-----------------------                -------------           -----------------             ---------------------
                  7                       18.1982422               18635                        102400

Enjoy   J J J