Change Character Set

[mzhu]

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.

[ales]

select OWNER,TABLE_NAME,COLUMN_NAME
  from dba_tab_columns
 where DATA_TYPE='CLOB'

See also Metalink note 66320.1.

[Calvin_Qiu]

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.

[julian]

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."