Last time i need to store data in Urdu format in Oracle 11g Database. After insertion data was in this format(????).
Actually DB were configured with default setting. Urdu data can be stored in DB but database character set must be AL32UTF8.
How to migrate character set. I play with CSSCAN.
Step1 :
SQL>
Step 2:
[oracle@linux1 ~]$CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11
:43 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: SYS AS SYSDBA
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CSS-00107: Character set migration utility schema not installed
So CSSCAN utility doesnot installed on your computer, so we want to install the CSSCAN utility. Go to csminst.sql script
Step 3:
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/csminst.sql
Synonym created.
View created.
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Step 4:
[oracle@linux1 ~]$CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11:38:37 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: SYS AS SYSDBA
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1
Current database character set is WE8MSWIN1252.
Enter new database character set name: > AL32UTF8
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 > 64
. process 51 scanning EXFSYS.RLM$EVENTSTRUCT
. process 63 scanning EXFSYS.RLM$RULESETSTCODE
. process 59 scanning EXFSYS.RLM$RULESETPRIVS
. process 27 scanning EXFSYS.RLM$INCRRRSCHACT
. process 62 scanning EXFSYS.RLM$ORDERCLSALS
. process 21 scanning EXFSYS.RLM$RSPRIMEVENTS
. process 31 scanning EXFSYS.RLM$VALIDPRIVS
. process 10 scanning EXFSYS.RLM$DMLEVTTRIGS
. process 60 scanning EXFSYS.RLM$COLLGRPBYSPEC
. process 32 scanning EXFSYS.RLM$PRIMEVTTYPEMAP
. process 5 scanning EXFSYS.RLM$EQUALSPEC
Creating Database Scan Summary Report…
Creating Individual Exception Report…
Scanner terminated successfully.
Step 5:
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 301990352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
Step 6: Run the csalter.plb script
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
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 validity…
begin converting system objects
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>
Step 7:
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 301990352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
Step 8: Check the current character set.
SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’;
PARAMETER VALUE
—————————— —————————————-
NLS_CHARACTERSET AL32UTF8
SQL>
