cssan utility to change the Character set of database:-
Beware: - Before doing this take a full database backup
1. Create data_file_dir and log_file_dir directories with below command:-
SQL> create directory data_file_dir as ‘some_directory_location’;
SQL> create directory log_file_dir as ‘some_directory_location’;
2. And execute the csminst.sql file as a sys user:-
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
It will create CSMIG user.
3. Execute the below command in OS level:-
$csscan full=Y fromchar=WE8MSWIN1252 tochar=WE8IS08859P15 log=WE8_TO_WE8
capture=Y array=100000
(later it will prompt for processes, enter some number between 1 to 32)
4. Then shutdown the database and startup database in restrict mode.
SQL>shutdown immediate;
SQL>startup restrict;
5. Execute the below file to change the Character Set:-
SQL>@$ORACLE_HOME/rdbms/admin/csalter.plb;
(it will ask for Y/N, press Y)
6. Then shutdown the database and start it normally:-
SQL>shutdown;
SQL>startup;
Check the character set with the below query:-
SQL> select * from v$nls_parameters;
* if there is any error, we can check with below query:-
first login to the CSMIG user and execute it
SQL> select count(*) from csm$errors;
if the output is '0' then it is fine.
