Home » RDBMS Server » Server Administration » Character Set Change
Character Set Change [message #233561] Thu, 26 April 2007 11:04 Go to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Hi,

I am trying to change the database characterset in Oracle 9i from US7ASCII to UTF8. I have taken a full export of the db.But while executing the ALTER DATABASE CHARACTER SET UTF8 I got the below mentioned error. I had gone through the metalink for this issue and got the workaround for few tables like METASTYLESHEET,RULE$,JOB$and WR* tables. But I have got totally 43 CLOB tables. Let me know if it is fine to drop these tables and proceed with the character set change.

ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists

SQL> select distinct table_name, owner from dba_tab_cols where data_type
2 'CLOB%';

TABLE_NAME OWNER
------------------------------ ------------------------------
ALL_REPCAT_TEMPLATE_OBJECTS SYS
ALL_REPCAT_TEMPLATE_PARMS SYS
ALL_REPCAT_USER_PARM_VALUES SYS
ALL_RULES SYS
ATTRIBUTE_TRANSFORMATIONS$ SYS
DBA_IAS_GEN_STMTS SYS
DBA_IAS_GEN_STMTS_EXP SYS
DBA_IAS_POSTGEN_STMTS SYS
DBA_IAS_PREGEN_STMTS SYS
DBA_LOGSTDBY_EVENTS SYS
DBA_REPCAT_EXCEPTIONS SYS

TABLE_NAME OWNER
------------------------------ ------------------------------
DBA_REPCAT_TEMPLATE_OBJECTS SYS
DBA_REPCAT_TEMPLATE_PARMS SYS
DBA_REPCAT_USER_PARM_VALUES SYS
DBA_RULES SYS
DEF$_LOB SYSTEM
DEF$_TEMP$LOB SYSTEM
DEFLOB SYS
EXTERNAL_TAB$ SYS
EXU9XTB SYS
FGA_LOG$ SYS
JIREFRESHSQL$ SYS

TABLE_NAME OWNER
------------------------------ ------------------------------
KU$_EXTTAB_VIEW SYS
KU$_M_VIEW_VIEW SYS
KU$_XMLSCHEMA_VIEW SYS
LOGSTDBY$EVENTS SYSTEM
LOGSTDBY$PLSQL SYSTEM
METASTYLESHEET SYS
REPCAT$_EXCEPTIONS SYSTEM
REPCAT$_INSTANTIATION_DDL SYSTEM
REPCAT$_RUNTIME_PARMS SYSTEM
REPCAT$_TEMPLATE_OBJECTS SYSTEM
REPCAT$_TEMPLATE_PARMS SYSTEM

TABLE_NAME OWNER
------------------------------ ------------------------------
REPCAT$_USER_PARM_VALUES SYSTEM
RULE$ SYS
SNAP$ SYS
STREAMS$_DEF_PROC SYS
USER_REPCAT_TEMPLATE_OBJECTS SYS
USER_REPCAT_TEMPLATE_PARMS SYS
USER_REPCAT_USER_PARM_VALUES SYS
USER_RULES SYS
VIEWCON$ SYS
_ALL_INSTANTIATION_DDL SYS

43 rows selected.
Re: Character Set Change [message #233618 is a reply to message #233561] Thu, 26 April 2007 23:03 Go to previous messageGo to next message
KrishnaBoppana
Messages: 12
Registered: March 2007
Location: Boston, MA
Junior Member
Safest way would be to
1) export database
2) recreate database with UTF8 as character set
3) Import the data back into DB

Krishna Boppana

[Updated on: Sun, 27 January 2008 09:21] by Moderator

Report message to a moderator

Re: Character Set Change [message #233723 is a reply to message #233618] Fri, 27 April 2007 04:24 Go to previous messageGo to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Thanks for the reply.

But I have the requirement from the project team to change the characterset in the existing database. Can you give me some workaround to get rid of the issue.
Re: Character Set Change [message #233807 is a reply to message #233561] Fri, 27 April 2007 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>But I have the requirement from the project team to change the characterset in the existing database. Can you give me some workaround to get rid of the issue.
I have the requirement to teach a pig how to fly.
Can YOU give me the lessons to get rid of this issue?

Simply put, the requirement is totally unrealistic.
icon7.gif  Re: Character Set Change [message #233812 is a reply to message #233561] Fri, 27 April 2007 10:31 Go to previous message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Razz I got your point. I will go with the creation of a new database for this requirement. Thanks. The only thing which is worrying me is that there are some convincing documents saying that the character set change can be done without issues. And there might issues only after the character set change.
Previous Topic: Oracle 8i DB in Windows XP Pro cannot access from client
Next Topic: JVM
Goto Forum:
  


Current Time: Sun Dec 04 23:00:46 CST 2016

Total time taken to generate the page: 0.08949 seconds