• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1230
  • Last Modified:

pfile or spfile

Hello,
How can i default set
nls_length_semantics="CHAR"
for all session from startup database. it's not working for setting on pfile.


Thanks
0
Aida2
Asked:
Aida2
  • 3
  • 2
1 Solution
 
czuriagaCommented:
First we must know if that parameter is session modifiable and/or system modifiable. It seems to be both of them:
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96536/ch1121.htm

Now, you must create a system trigger to set the required value every logon session

CREATE OR REPLACE TRIGGER system.data_logon_trigger
after logon
ON DATABASE
begin
  execute immediate 'alter session set nls_length_semantics="CHAR"';
end;

If you only wnat to set this value to a specific schema, you must add this:

CREATE OR REPLACE TRIGGER data_logon_trigger
after logon
ON DATABASE
begin
if user = 'SCHEMA_NAME' then
  execute immediate 'alter session set nls_length_semantics="CHAR"';
end if;
end;

0
 
Aida2Author Commented:
Thank you
what about existing columns do you have any idea?
 
0
 
czuriagaCommented:
ummm, I don't understand this question. What does mean "existing columns"?
0
 
Aida2Author Commented:
Hello,
I mean what about the all tables i created before i set the nls...semantics=char.
ex.
i have a table test with columns (a varchar2(4)) i created that one before i set nls..sem..=char.
i can't insert into values(åæøé) because the length of input is more than 4 byt
After i set the nls...seman... =char and create table test2 then char len. of column a is 4char and it's ok
then question is what i should i do with all this column i created before i set nls..=char?

Thank.
0
 
czuriagaCommented:
OK, I didn't know use of the parameter nls_length_semantics.

It seems (http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96536/ch1121.htm) that "Existing columns are not affected". So I think you must re-create the tables.

If you have a lot of tables and each table has a lot of data inside, I recommend you that steps:

- Export the entire schema (exp sys/sys_password owner=your_schema file=file_dump.dmp)
- Get the CREATE TABLE statements (from TOAD, or any tool like that)
- Empty the schema, droping tables, procedures, sequences, views.... (and checking with select * from user_objects that there's nothing inside). Or create a new empty schema "test2"
- In that empty schema, execute the CREATE TABLE statements (at the moment, all empty and now with the ls_length_semantics set to 'CHAR' by the trigger)
- Import schema overriding create errors (imp sys/sys_password fromuser=your_schema touser=your_schema ignore=y file=file_dump.dmp)

And all the data will be dumped inside the tables.

Before to empty your schema "test" (step 3), you can try it in an empty schema "test2" to see if really works.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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