Re: changing the codeset on the fly

I hope this is the answer to your question

NOTE: ONLY VALID FOR ORACLE7 SERVER !!!       DO NOT EVER APLLY THIS ON AN ORACLE8 DATABASE !!! This article describes how one can change the character set of a database.

It should be done with extreme caution having noted the following

limitations.

The database character set should only be changed if the characters of the

code page of the originating database correspond to the same characters

of the target database, ie. if the database was created with the

characterset US7ASCII and it is to be updated to WE8ISO8859P1. Since these

have the same encoding scheme for the first 127 bits, changing the

character set from US7ASCII to WE8ISO8859P1 will display all characters up

to 127 bits as the same character before and after. In addition, in this

particular example, if any characters have been entered with the 8th bit set, then updating the database characterset to 8 bit will allow that 8th

bit to be displayed. You must not change the characterset from one encoding

scheme to another encoding scheme where the code pages do not correspond.

This will completely scramble your database. In addition, if case*designer

diagrams are stored in the database, this method must not be used. Contact

Worldwide Support for further details.

Before attempting to run any of the scripts below, you must take a full

cold backup of your database. In addition, the procedure must be thoroughly

tested before attempting this on a production instance.

Here's a SQL*PLUS script that allows a database's character set to be

changed to a different encoding scheme without having to rebuild the database.
set echo off

set verify off

rem The data dictionary table that records the database rem character set is sys.props$

rem

rem SQL> describe sys.props$

rem  Name                            Null?    Type

rem  ------------------------------- -------- ----

rem  NAME                            NOT NULL VARCHAR2(30)

rem  VALUE$                                   VARCHAR2(2000)

rem  COMMENT$                                 VARCHAR2(2000)

rem For example:

rem

rem SQL> column c1 format a30

rem SQL> select name c1, value$ c1 from sys.props$;

rem C1                             C1

rem ------------------------------ ------------------------------

rem DICT.BASE                      2

rem NLS_LANGUAGE                   AMERICAN

rem NLS_TERRITORY                  AMERICA

rem NLS_CURRENCY                   $

rem NLS_ISO_CURRENCY               AMERICA

rem NLS_NUMERIC_CHARACTERS         .,

rem NLS_DATE_FORMAT                DD-MON-YY

rem NLS_DATE_LANGUAGE              AMERICAN

rem NLS_CHARACTERSET               WE8DEC

rem NLS_SORT                       BINARY

rem GLOBAL_DB_NAME                 NLSV7.WORLD

rem NLS_CHARACTERSET can be changed by updating its value, for example:

rem update sys.props$

rem set value$ = 'WE8ISO8859P1'

rem Where name = 'NLS_CHARACTERSET';

rem The database has to be shutdown and restarted before the change

rem becomes effective.

rem It is very important to specify the character set name correctly.

rem IMPORTANT NOTE

rem =============

rem If NLS_CHARACTERSET is updated to an invalid value, it will not then

rem be possible to restart the database once it has been shutdown.

rem To recover, it will be necessary to re-create the database, since it

rem cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

rem The character set name should be in uppercase.

rem The new value is not effective until the database has been shutdown and

rem restarted.

rem

rem A suggested procedure is as follows, and can be done by running this

rem script from SQL*Plus when logged into the SYSTEM account.

rem

rem USAGE : SQL> start ch_db.sql <character set>

rem

rem where <character set> is the desired database character set

rem

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','us7ascii') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the

prompt specified character set name is not valid for this installation.

prompt Abort the procedure now with Control-c

prompt To continue, press return

accept ans CHAR

Prompt Check the current value of database character set.

column c1 format a30

select name c1, value$ c1 from sys.props$

where name = 'NLS_CHARACTERSET';

prompt To continue, press return

Prompt Update to new character set

update sys.props$

set value$ = upper('&1')

where name = 'NLS_CHARACTERSET';

set echo off

prompt To continue, press return

accept ans CHAR

Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$

where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and

Prompt then manually type COMMIT; to commit the change. Then shutdown and

Prompt restart the database.

Prompt

Prompt If the value is not updated as required, press return to continue and

Prompt than manually type ROLLBACK; to prevent the change.

prompt To continue, press return

accept ans CHAR

[]

Alexandre Mavel heeft geschreven in bericht <73bopn$89v$1_at_aristote.pratique.fr>...
>
> S_at_lut,
>
> Did anybody manage to change the codeset of a database on a 7.3 rdbms
>and a 10.7 OA on the fly? I mean after it's creation?
>
>Thanks by advance.
>
>---------------------------------------------------------------------------
-
>-----------
>Alexandre Mavel
>amavel_at_partner.auchan.com
>---------------------------------------------------------------------------
-
>-----------
>
>
>
Received on Mon Nov 23 1998 - 08:24:59 CST