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
LVL 1
miyahiraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwertnerCommented:
in initxxx.ora add the lines:

*. NLS_COMP=ANSI;
*. NLS_SORT=GENERIC_BASELETTE

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.