-
Change Character Set
I want to change the character set of database. I did the following steps:
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ;
But I got the error:
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists
Do I have to all columns with CLOB? Our schema does not use CLOB at all, so those CLOBs must be in the schema coming with 9i installation.
How can I find out where CLOBs are ?
Thanks for any help.
-
Code:
select OWNER,TABLE_NAME,COLUMN_NAME
from dba_tab_columns
where DATA_TYPE='CLOB'
See also Metalink note 66320.1.
-
ALTER DATABASE CHARACTER SET statement can be used only when the character set migration is between two single-byte character sets or between two multibye character sets.
-
According to Oracle:
"The ALTER DATABASE [NATIONAL] CHARACTER SET statement does not change character codes. Thus, before changing a fixed width database character set to a varying width character set (like UTF8) in Oracle 8.1.5 or later, you first have to export all tables containing non-NULL CLOB columns, then truncate these tables, then change the database character set and, finally, import the tables back to the database. The import step will perform the required conversion."