[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2445
  • Last Modified:

Before I change NLS_COMP and NLS_SORT values

Using Oracle 10g on Windows Server 2003.

We need to change the NLS_COMP and NLS_SORT values for accent insensitive comparison in queries (i.e: "á" or "a" should be the same)

We are planning to do:
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTE

So, we hope it allow us to query like:

select * from table_a where text like '%papá%'

Results:
papa
papá

But before to make these changes, I would like to know what are the current settings of NLS_COMP and NLS_SORT. How can I find them? I read about NLS_SESSION_PARAMETERS, but I think it is Oracle 11g stuff.

After I change NLS_COMP and NLS_SORT through ALTER SESSION, should I restart instance service, so the changes will be permanent?
Thank you
0
miyahira
Asked:
miyahira
4 Solutions
 
mohammadzahidCommented:
No, any settings altered at session level do not require service restart. Session changes only valid as long as session is active, that means if you exit SQL Plus session then session settings reset to the default settings.

you can find NLS_SORT and NLS_COMP settings......  
sql> select * from nls_database_parameters;

Hope this answers your question.
0
 
miyahiraAuthor Commented:
Thanks for your responses. Now I have some other doubts:

- NLS_SORT and NLS_COMP should be defined in INI.ORA or PSFILE. I should add this lines in init.ora:
NLS_COMP=ANSI
NLS_SORT=GENERIC_BASELETTER

- However, if they are not, where are those values defined?

This is my INITXXXX.ORA. As you see, there are no NLS_SORT or NLS_COMP parameter:

mtdprod.__db_cache_size=20971520
mtdprod.__java_pool_size=159383552
mtdprod.__large_pool_size=4194304
mtdprod.__shared_pool_size=184549376
*.background_dump_dest='C:\oracle\product\10.1.0\ADMIN\MTDPROD\bdump'
*.compatible='10.1.0.2.0'
*.control_files='C:\oracle\product\10.1.0\ADMIN\MTDPROD\controlfiles\control01.ctl','D:\BD_MTDPROD\CONTROLFILES\control02.ctl','D:\BD_MTDPROD\CONTROLFILES\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.1.0\ADMIN\MTDPROD\cdump'
*.db_block_size=8192
*.db_domain='jne.gob.pe'
*.db_file_multiblock_read_count=8
*.db_name='mtdprod'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mtdprodXDB)'
*.java_pool_size=157286400
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=D:\BD_MTDPROD\ARCHIVELOGS'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=122683392
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=370147328
*.shared_pool_size=157286400
*.sort_area_size=65536
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.1.0\ADMIN\MTDPROD\udump'

However, if I do:
select * from NLS_SESSION_PARAMETERS;
Results:
NLS_SORT      SPANISH
NLS_COMP      BINARY

select * from nls_database_parameters;
I got these results:
NLS_SORT      BINARY
NLS_COMP      BINARY

I don't understand. Where are these values set or defined?
0
 
schwertnerCommented:
You can add them into INITxxx.ora first and to see how they work:

SQL>shutdown immedite
SQL>startup pfile=c:\initxxx.ora

If all runs fine yhen create SPFILE from that inixxx.ora:

SQL>create SPFILE='c:\SPFILEsid.ora' FROM PFILE='c:\initxxx.ora';
SQL>shutdown immedite
Backup the old SPFILE and put there the new one (names should be the same!).

SQL>stratup
0
 
schwertnerCommented:
in initxxx.ora add the lines:

*. NLS_COMP=ANSI;
*. NLS_SORT=GENERIC_BASELETTE

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now