This content has been marked as final. Show 13 replies
yes, you can. For example:
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
You can see a post with more information here:
INTERNAL_USE means "use restricted to Oracle". It means, do not use unless instructed by Oracle Support!
To change the database character set, perform the following steps:
Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET new_character_set;
SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL;
I suggest you to read the following Notes of MOS before changing the National Character Set:
- *The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g [ID 276914.1]*
The point *17)* is interesting:
Hope this help.
17) How to go from an UTF8 NLS_NCHAR_CHARTERSET to AL16UTF16?
thanks to all.
It sounds magic. Let me try. I will inform you about the result.
Do NOT use the methods indicated above - they are NOT supported and WILL corrupt your database beyond repair.
The correct and supported methods are documented
The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g [ID 276914.1]
unfortunately I receive :
Any idea ?
SQL> ALTER DATABASE CHARACTER SET AL16UTF16; ALTER DATABASE CHARACTER SET AL16UTF16 * ORA-12712: new character set must be a superset of old character set.
To change the national character set to AL16UTF16, you have to:
1. Export or unload content of all NCHAR and NVARCHAR2 columns.
2. Nullify or drop all NCHAR and NVARCHAR2 columns.
3. Run CSSCAN TONCHAR=AL16UTF16
4. Run CSALTER.PLB
5. Reload the saved content of NCHAR and NVARCHAR2 columns.
See documentation links mentioned above. Do not use ALTER DATABASE commands mentioned above. These commands are unsupported and may have disastrous effects if used incorrectly.
On the other hand, Oracle recommendation is that you create your database with the AL32UTF8 database character set, and move all your national character set data to normal VARCHAR2 and CHAR columns. Then, forget about the national character set.
As I stated above, you cannot use the ALTER command to change either NATIONAL or regular CHARACTER SET - were my comments not clear enough ?
See the links Jean and I posted above on how to change the characterset.
Thanks to all.
Syntax was bad. The correct one is :
ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16
And it worked.
Srini, that note (276914.1) does not say that you can not use ALTER (or I did not see). It says if you have table columns bigger then 1000 CHAR or NVARCHAR2 column bigger then 2000 CHAR change them.
I had only two :
then as said in that note I ran \rdbms\admin\catfga.sql.
select distinct OWNER, TABLE_NAME, COLUMN_NAME, CHAR_LENGTH from DBA_TAB_COLUMNS where DATA_TYPE='NVARCHAR2' and CHAR_LENGTH > 200 OWNER TABLE_NAME ------------------------------ ------------------------------ COLUMN_NAME CHAR_LENGTH ------------------------------ ----------- SYS DBA_COMMON_AUDIT_TRAIL SQL_BIND 4000 SYS DBA_COMMON_AUDIT_TRAIL SQL_TEXT 4000
Thank to all.
## Srini, that note (276914.1) does not say that you can not use ALTER (or I did not see).
No, it does not say it directly, but if you look into SQL Reference 11gR2, you will not find the command. It means, it is undocumented and thus unsupported. The note says:
"( using alter database is in a 10g system for the national characterset not really a problem, but csalter is the official way)". The note is correct here. You should use csalter.plb for a supported method, though ALTER DATABASE NATIONAL CHARACTER SET will (unofficially) work as well, provided you do not try any INTERNAL clauses.
thank you Sergiusz.
What is csalter.plb ?
$ORACLE_HOME/rdbms/admin/csalter.plb, a script to run in SQL*Plus, connected as SYSDBA.
user522961 wrote:Documented in the links I posted above ;-)
thank you Sergiusz.
What is csalter.plb ?