Re: change database character set

From: peter <hhh.database_at_gmail.com>
Date: 12 Dec 2006 21:16:28 -0800
Message-ID: <1165986988.323478.197780@79g2000cws.googlegroups.com>

joel garry wrote:
> aman.oracle.dba wrote:
> > sybrandb wrote:
> > > On Dec 12, 11:13 am, "peter" <hhh.datab..._at_gmail.com> wrote:
> > > > Hi all,
> > > >
> > > > How to change database character set US7ASCII to UTF8 in oracle 9.2 on
> > > > Sun Solaris.
> > > > Please inform.
> > > >
> > > > Thanks
> > >
> > > Please read the SQL reference manual, 'ALTER DATABASE' statement.
> > > Please avoid doc questions.
> > >
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
> > It is there but according to that CLOB will create problem, that's why
> > I am discussing here.
> > Please inform how to solve the CLOB problem.
>
> You need to export the CLOB before changing the character set, and
> import it after.
>
> See "Use Scanner Utility before Altering the Database Character Set"
> metalink Note:123670.1
> . See AL32UTF8 / UTF8 (unicode) Database Character Set Implications
> metalink Note:119119.1 .
>
> If you've done something like insert 8 bit data into your 7 bit
> database, you will have to do more than:
>
> a. Export the current entire database (with NLS_LANG set to <your old
> database character set>)
> b. Create a new database in the AL32UTF8 character set
> c. Import all data into the new database (with NLS_LANG set to <your
> old database character set>)
>
> jg
> --
> @home.com is bogus.
> http://www.signonsandiego.com/uniontrib/20061211/news_1n11sales.html
I am facing these problem : -

SQL> ALTER DATABASE CHARACTER SET AL32UTF8; ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists

SQL> select distinct table_name from dba_tab_cols where data_type like 'CLOB%'; TABLE_NAME

ALL_REPCAT_TEMPLATE_OBJECTS
ALL_REPCAT_TEMPLATE_PARMS
ALL_REPCAT_USER_PARM_VALUES
ALL_RULES
ALL_SDO_MAPS
ALL_SDO_STYLES
ALL_SDO_THEMES
AQ$QS_CBADM_ORDERS_MQTAB_R
AQ$QS_CS_ORDER_STATUS_QT_R

AQ$QS_ES_ORDERS_MQTAB_R
AQ$QS_ES_ORDERS_PR_MQTAB_R TABLE_NAME

AQ$QS_ORDERS_PR_MQTAB_R
AQ$QS_OS_ORDERS_MQTAB_R
AQ$QS_OS_ORDERS_PR_MQTAB_R
AQ$QS_WS_ORDERS_MQTAB_R
AQ$QS_WS_ORDERS_PR_MQTAB_R

ATTRIBUTE_TRANSFORMATIONS$

DBA_IAS_GEN_STMTS
DBA_IAS_GEN_STMTS_EXP
DBA_IAS_POSTGEN_STMTS
DBA_IAS_PREGEN_STMTS
DBA_LOGSTDBY_EVENTS

TABLE_NAME

DBA_REPCAT_EXCEPTIONS
DBA_REPCAT_TEMPLATE_OBJECTS
DBA_REPCAT_TEMPLATE_PARMS
DBA_REPCAT_USER_PARM_VALUES
DBA_RULES
DBA_SDO_MAPS
DBA_SDO_STYLES
DBA_SDO_THEMES

DEF$_LOB
DEF$_TEMP$LOB
DEFLOB TABLE_NAME

DMS_QUEUE_TABLE
EXTERNAL_TAB$
EXU9XTB
FGA_LOG$
JIREFRESHSQL$

KU$_EXTTAB_VIEW
KU$_M_VIEW_VIEW
KU$_XMLSCHEMA_VIEW

LOGSTDBY$EVENTS
LOGSTDBY$PLSQL
METASTYLESHEET TABLE_NAME

ODM_PMML_DTD
ONLINE_MEDIA
PRINT_MEDIA

REPCAT$_EXCEPTIONS
REPCAT$_INSTANTIATION_DDL
REPCAT$_RUNTIME_PARMS
REPCAT$_TEMPLATE_OBJECTS
REPCAT$_TEMPLATE_PARMS
REPCAT$_USER_PARM_VALUES

RULE$
SDO_MAPS_TABLE TABLE_NAME

SDO_STYLES_TABLE
SDO_THEMES_TABLE
SNAP$
STREAMS$_DEF_PROC

USER_REPCAT_TEMPLATE_OBJECTS
USER_REPCAT_TEMPLATE_PARMS
USER_REPCAT_USER_PARM_VALUES
USER_RULES
USER_SDO_MAPS
USER_SDO_STYLES
USER_SDO_THEMES

TABLE_NAME

VIEWCON$
WAREHOUSES
WM$LOCKROWS_INFO
XDB$RESOURCE
_ALL_INSTANTIATION_DDL 71 rows selected. Received on Tue Dec 12 2006 - 23:16:28 CST