| Demo |
Changing Character Sets |
Note: Depending on the character sets involved this may result in data loss. Try this in test environments before proceeding in production. |
conn / as sysdba
col parameter format a30
col value format a30
SELECT view_name
FROM dba_views
WHERE view_name LIKE '%NLS%';
SELECT *
FROM gv_$nls_parameters;
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SHUTDOWN IMMEDIATE;
STARTUP;
SELECT *
FROM gv_$nls_parameters; |
Note: I have
received the following email from Gunther Vermeir <gunther.vermeir@oracle.com>
from Oracle and have posted it here, unedited, so that you are all
aware of the issue.
I saw your
post in http://forums.oracle.com/forums/thread.jspa?messageID=4081969#4081969
linking to
http://www.morganslibrary.org/reference/character_sets.html
Kindly or remove the "internal_use" trick or post a decent
warning that if this used without using csscan you will
corrupt data on 10G certainly (and also on lower versions).
I explain in great detail in notes refrecnced in Note:225912.1
Changing the Database Character Set ( NLS_CHARACTERSET ) like
Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8
(Unicode) what steps you need to do to actually do a safe
conversion
in the example you have given using WE8MSWIN1252 the chances
of problems is minimal, but this is ONLY true for when going
from US7ASCII or WE8ISO8859P1 system and a US/West European
env. so this is actually the only exception. For ANY charter
set conversion csscan should be used.
Regards,
Gunther |
I'm not convinced that INTERNAL_USE is a "trick" but I am convinced that posting this note is reasonable
so here it is. |
| |
| CSALTER |
Changing Character Sets |
Note: Depending on the character sets involved this may result in data loss. Try this in test environments before proceeding in production. |
conn / as sysdba
SQL> shutdown immediate;
SQL> startup restrict;
SQL>@?/rdbms/admin/csalter.plb
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
begin converting system objects
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
C:\>
conn / as sysdba
SQL> shutdown immediate;
SQL> startup;
conn csmig/oracle1
SELECT object_name, object_type
FROM user_objects
ORDER BY 2;
exec dbms_stats.gather_schema_stats(USER);
SELECT table_name, num_rows
FROM user_tables;
SELECT * FROM csm$parameters; |
| |
| CSSCAN |
Changing Character Sets |
CSSCAN [parameters]
| Parameter |
Default |
Prompt |
Description |
| ARRAY |
1024000 |
yes |
Array buffer size |
| BOUNDARIES |
- |
no |
List of columns size boundaries for
summary report |
| CAPTURE |
N |
no |
Capture convertible data |
| COLUMN |
- |
no |
List of columns to scan |
| EXCLUDE |
- |
no |
List of tables to exclude |
| FEEDBACK |
- |
no |
Report progress for every n rows |
| FROMCHAR |
- |
no |
Current database character set name |
| FROMNCHAR |
- |
no |
Current database national character
set name |
| FULL |
N |
yes |
Scan entire database |
| HELP |
N |
no |
Show help screen |
| LASTRPT |
N |
no |
Generate report of the previous
database scan |
| LCSD |
N |
no |
Enable language and character set
detection |
| LCSDDATA |
LOSSY |
no |
Define the scope of the language and
character set detection |
| LOG |
scan |
no |
Base file name for report log |
| MAXBLOCKS |
- |
no |
The maximum number of blocks that can
be in a table without the table being split |
| PARFILE |
- |
no |
Parameter file name |
| PRESERVE |
N |
no |
Preserve existing scan results |
| PROCESS |
1 |
yes |
Number of concurrent scan processes |
| QUERY |
- |
no |
Query to apply to restrict output
before scan |
| SUPPRESS |
- |
no |
Maximum number of exceptions logged
for each table |
| TABLE |
- |
yes |
List of tables to scan |
| TOCHAR |
- |
yes |
New character set name |
| TONCHAR |
- |
no |
New national character set name |
| USER |
- |
yes |
Owner of the tables to be scanned |
| USERID |
- |
yes |
Username/password |
|
C:\>
csscan
Character Set Scanner v2.2: Release 11.1.0.7 - Production on Mon Jan 4
18:28:34 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: sys as sysdba
Password:
Connected to:
Oracle database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
conn / as sysdba
SQL>@?\rdbms\admin\csminst.sql
-- password supplied is "oracle1"
conn csmig/oracle1
SELECT object_name, object_type
FROM user_objects
ORDER BY 2;
SQL>exit;
C:\>CSSCAN \"sys/NoWay!@db10g AS SYSDBA\"
FULL=Y
Character Set Scanner v2.2 : Release 11.1.0.7.0 - Production on Tue Jan 4
18:31:03 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
Current database character set is WE8MSWIN1252.
Enter new database character set name: > WE8MSWIN1252
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 4
Enumerating tables to scan...
. process 4 scanning SYS.WRH$_WAITSTAT[AAARFOAACAAABEoAAA]
.
. process 22 scanning GLOBAL.AW$GLOBAL[AAAS2gAAJAAAAH4AAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully. |
