Change Character Set
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Change Character Set

  1. #1
    Join Date
    Mar 2001
    Posts
    109

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

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Code:
    select OWNER,TABLE_NAME,COLUMN_NAME
      from dba_tab_columns
     where DATA_TYPE='CLOB'
    See also Metalink note 66320.1.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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."
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width