Re: Oracle 7.3.4 SQL*Plus
From: Don Miller <dmiller_at_nsf.org>
Date: Thu, 25 Feb 1999 08:48:41 -0500
Message-ID: <36d552f1.0_at_news.ic.net>
Introduction
~~~~~~~~~~~~
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:
Received on Thu Feb 25 1999 - 14:48:41 CET
Date: Thu, 25 Feb 1999 08:48:41 -0500
Message-ID: <36d552f1.0_at_news.ic.net>
Article-ID:
<Note:13856.1
</cgi-bin/notes/og.pl?note:13856.1>>
Alias: OLS:105600.874
Circulation: PUBLISHED (EXTERNAL)
Folder: NLS
Platform: GENERIC Generic issue
Topic: * Using NLS
Subject: V7: CHANGING DATABASE CHARACTER SET
Modified-Date: 14-AUG-1998 08:40:58
Document-Type: BULLETIN
Content-Type: TEXT/PLAIN
Impact: MEDIUM
Component: RDBMS
Alias: OLS:105600.874
Circulation: PUBLISHED (EXTERNAL)
Folder: NLS
Platform: GENERIC Generic issue
Topic: * Using NLS
Subject: V7: CHANGING DATABASE CHARACTER SET
Modified-Date: 14-AUG-1998 08:40:58
Document-Type: BULLETIN
Content-Type: TEXT/PLAIN
Impact: MEDIUM
Component: RDBMS
Introduction
~~~~~~~~~~~~
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:
* This is not an officially supported
method of changing the characterset.
You should only be using this bulletin if Oracle Support have confirmed
the steps are valid for the character set change you wish to perform.
You should only be using this bulletin if Oracle Support have confirmed
the steps are valid for the character set change you wish to perform.
* Do NOT attempt to use the steps here
against an Oracle8 database.
Doing so will raise an internal error:
ORA-00600: internal error code, arguments: [adbdrv2], []
and further use of the database could result in unrecoverable corruption.
Doing so will raise an internal error:
ORA-00600: internal error code, arguments: [adbdrv2], []
and further use of the database could result in unrecoverable corruption.
* 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.
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.
cold backup of your database. In addition, the procedure must be thoroughly
tested before attempting this on a production instance.
Changing the Character
Set
~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is a SQL*PLUS script that allows a database character set to be
changed to a different encoding scheme without having to rebuild the database.
set echo off
set verify off
~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is a SQL*PLUS script that allows a database 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 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
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 ------------------------------ ------------------------------
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 set value$ = 'WE8ISO8859P1'
rem Where name = 'NLS_CHARACTERSET';
rem The database has to be shutdown and
restarted before the change
rem becomes effective.
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 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
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 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
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';
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 value$ = upper('&1')
where name = 'NLS_CHARACTERSET';
set echo off
prompt To continue, press return
accept ans CHAR
accept ans CHAR
Prompt Check the new value of database
character set
select name c1, value$ c1 from
sys.props$
where name = 'NLS_CHARACTERSET';
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 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
accept ans CHAR
------------------------------------------------------------------------
Keywords: NLS;NLS_LANG;RDBMS;CHARACTER SET
Keywords: NLS;NLS_LANG;RDBMS;CHARACTER SET