Home » RDBMS Server » Server Utilities » Solving Convertible Data (Oracle 10.2.0.4)  () 2 Votes
icon4.gif  Solving Convertible Data [message #397686] Mon, 13 April 2009 06:47 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAMwvAAFAAAq5EAA0 convertible FLATTS 
AAAMwvAAFAAAq5EAA2 convertible KADOWAKI 
AAAMwvAAFAAAq5EAA3 convertible LADOUCEC 
AAAMwvAAFAAAq5EAA9 convertible PEARCETH 
AAAMwvAAFAAAq5EAAy convertible DEBLOIS 
AAAMwvAAFAAAq5EABA convertible RAWJI 
------------------ ------------------ ----- ------------------------------


This is the CSScan output of my database instance.

I want the above six datas to be converted from WE8ISO8859P1 to UTF8 charecterset.

All the above 6 datas have a space ' ' like charecter (i'm not sure) at the end,

kindly help me.

[Updated on: Mon, 13 April 2009 07:21]

Report message to a moderator

Re: Solving Convertible Data [message #397703 is a reply to message #397686] Mon, 13 April 2009 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Help you to what?
What is your problem?

Regards
Michel
Re: Solving Convertible Data [message #397714 is a reply to message #397703] Mon, 13 April 2009 08:37 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

I want the above six datas to be converted from WE8ISO8859P1 to UTF8 charecterset.

How can the data in the WE8ISO8859P1 charecterset be converted to UTF8 charecterset using a DML statement?
Re: Solving Convertible Data [message #397715 is a reply to message #397686] Mon, 13 April 2009 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
export/import
Re: Solving Convertible Data [message #397716 is a reply to message #397714] Mon, 13 April 2009 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rtnataraj wrote on Mon, 13 April 2009 15:37
I want the above six datas to be converted from WE8ISO8859P1 to UTF8 charecterset.

How can the data in the WE8ISO8859P1 charecterset be converted to UTF8 charecterset using a DML statement?

What actual problem are you trying to solve?

Regards
Michel

Re: Solving Convertible Data [message #397717 is a reply to message #397715] Mon, 13 April 2009 08:55 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Can the export and import solve this problem?

By running the CSAlter what will happen to the above six datas which were not able to convert to the destination charecterset due to the last charecter, " " codepoint is unavailable in the destination charecterset?

Will that charecter be replaced with the default replacement charecter "inverted ?" once after we do the CSAlter?

Re: Solving Convertible Data [message #397718 is a reply to message #397686] Mon, 13 April 2009 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
export/import is the only way I know to "change" DB charactersets

>Can the export and import solve this problem?
This is why it is a Good Thing to test before deploying.
Re: Solving Convertible Data [message #397720 is a reply to message #397686] Mon, 13 April 2009 09:15 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

export/import is the only way,

can you give me the command to export specific rows from a table?
Re: Solving Convertible Data [message #397721 is a reply to message #397686] Mon, 13 April 2009 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>can you give me the command to export specific rows from a table?
Not possible.
CTAS with only desired rows & export it.
Re: Solving Convertible Data [message #397722 is a reply to message #397721] Mon, 13 April 2009 09:29 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

pls tell me what is CTAS?
Re: Solving Convertible Data [message #397723 is a reply to message #397686] Mon, 13 April 2009 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE for "CTAS Oracle"

http://www.orafaq.com/wiki/CTAS

[Updated on: Mon, 13 April 2009 09:35]

Report message to a moderator

Re: Solving Convertible Data [message #397724 is a reply to message #397723] Mon, 13 April 2009 09:36 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Thanks for your information.
i'll try tat and get back to you..
Re: Solving Convertible Data [message #397725 is a reply to message #397720] Mon, 13 April 2009 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what will happen to the above six datas which were not able to convert to the destination charecterset

This is NOT what csscan says. It says "convertible".

Quote:
can you give me the command to export specific rows from a table?

C:\>exp help=y

Export: Release 10.2.0.4.0 - Production on Lun. Avr. 13 16:35:38 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

See QUERY parameter.

Regards
Michel

Re: Solving Convertible Data [message #397727 is a reply to message #397686] Mon, 13 April 2009 09:42 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Quote:
This is NOT what csscan says. It says "convertible".


so we no need to care about the "convertible" datas.
do the "csalter" converts it automatically??

thanx for your reply
Re: Solving Convertible Data [message #397732 is a reply to message #397727] Mon, 13 April 2009 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't use csalter script.
"convertible" means that data are converted to the target code point during process but have not the same code point as in the source character set.

Regards
Michel
Re: Solving Convertible Data [message #397811 is a reply to message #397732] Tue, 14 April 2009 00:54 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

ok.
so the codepoints for the data will only vary for the "convertible" datas but the charecters will be converted as it is. Am I right?
Re: Solving Convertible Data [message #397820 is a reply to message #397811] Tue, 14 April 2009 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Solving Convertible Data [message #397823 is a reply to message #397686] Tue, 14 April 2009 01:19 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

thanx Michel.
Do you hav any idea about CSALTER or ALTER charecterset?
Re: Solving Convertible Data [message #397824 is a reply to message #397820] Tue, 14 April 2009 01:23 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello,

If you have any problem with your data. First you need to fix later use export/import. Othrewise you will get error.

Try to post your source database characterset.

Try to post your csscan logfile (with your command.)

Post your DDL Tables

Post few records (sample records)

Babu

[Updated on: Tue, 14 April 2009 01:25]

Report message to a moderator

Re: Solving Convertible Data [message #397826 is a reply to message #397824] Tue, 14 April 2009 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All of these questions have not been answered in the first post?
What is the purpose of table DDL here?

Regards
Michel
Re: Solving Convertible Data [message #397827 is a reply to message #397826] Tue, 14 April 2009 01:49 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member


I've used the below csscan command.

Quote:
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=UTF8 ARRAY=1024000 LOG=charcheck CAPTURE=Y PROCESS=4


Please review the attached charcheck.txt err file generated by CSScan tool and advice me appropriately.
  • Attachment: charcheck.txt
    (Size: 137.22KB, Downloaded 1934 times)
Re: Solving Convertible Data [message #397833 is a reply to message #397827] Tue, 14 April 2009 02:29 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>What is the purpose of table DDL here?

Becuase I need to check from my end. That's the reason I'm asking table script and data.

>>Please review the attached charcheck.txt err file generated by CSScan tool and advice me appropriately.

Thanks for your details. Hereafter don't attach any script. Instread attach post your script. Follow OraFaq rules.

Also. I'm asking table script and few data's. This is useful to try scan my end also.

Thanks.

Babu
Re: Solving Convertible Data [message #397857 is a reply to message #397833] Tue, 14 April 2009 04:19 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

In the charcheck.txt err file, data given at the last is the only which belongs to the Application
Quote:
User : ILS3_SL_1
Table : USER_INFO
Column: USER_NAME
Type : VARCHAR2(60)
Number of Exceptions : 0
Max Post Conversion Data Size: 18

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAMwvAAFAAAq5EAA0 convertible FLATTS 
AAAMwvAAFAAAq5EAA2 convertible KADOWAKI 
AAAMwvAAFAAAq5EAA3 convertible LADOUCEC 
AAAMwvAAFAAAq5EAA9 convertible PEARCETH 
AAAMwvAAFAAAq5EAAy convertible DEBLOIS 
AAAMwvAAFAAAq5EABA convertible RAWJI 
------------------ ------------------ ----- ------------------------------




all other belongs to the SYSTEM objects,
Do we need to care about those too??
Re: Solving Convertible Data [message #397863 is a reply to message #397857] Tue, 14 April 2009 04:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
User : ILS3_SL_1
Table : USER_INFO
Column: USER_NAME
Type : VARCHAR2(60)
Number of Exceptions : 0
Max Post Conversion Data Size: 18



You don't have problem with your data's. You can upgrade/convert your data to UTF8.

Ref: Doc Id: 444701.1

Thank you in advance.

Babu
Re: Solving Convertible Data [message #397864 is a reply to message #397857] Tue, 14 April 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Do we need to care about those too??

In the same way.

Regards
Michel
Re: Solving Convertible Data [message #397870 is a reply to message #397686] Tue, 14 April 2009 06:13 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

In the Metalink Doc Id: 444701.1

Quote:
When using Csalter / "Alter database character set" CONVERTIBLE for Data Dictionary CHAR, VARCHAR2 and LONG data needs to be adressed (export is only applicable to a few tables, by default do NOT use export to solve this).


how can we handle this type of situation?
Re: Solving Convertible Data [message #397872 is a reply to message #397870] Tue, 14 April 2009 06:22 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


We don't have rights to post metalink content. Please remote/update your last message.

Babu
Re: Solving Convertible Data [message #397875 is a reply to message #397872] Tue, 14 April 2009 06:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


I don't think so; This is not releated your topic.

You don't want to use CSALTER.

Babu
Re: Solving Convertible Data [message #397881 is a reply to message #397875] Tue, 14 April 2009 06:52 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

gentlebabu wrote on Tue, 14 April 2009 17:07

I don't think so; This is not releated your topic.

You don't want to use CSALTER.




I asked "how to solve the CONVERTIBLE datas of Data Dictionary from the DB".
This is certainly related to this topic "Solving Convertible Data".

And pls help me if anyone knows the answer!
Re: Solving Convertible Data [message #397882 is a reply to message #397881] Tue, 14 April 2009 07:07 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


CONVERTIBLE - Mean; Your data's valid. But sometime your data type length diff.

Before export you may need to alter your tables OR you may need to fix those cloums and then import to Target Database (UTF8)

PS: There is NO issues in your logfile.

Babu
Re: Solving Convertible Data [message #397887 is a reply to message #397882] Tue, 14 April 2009 07:38 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Babu,
The scope of my conversion is limited to the tables and columns datas given in the output file.

Quote:
But sometime your data type length diff.


"exceed column size" will be the exception when we have data that needs length to be extended.

But the output file has all of the CONVERTIBLE datas, mostly from System objects and some from Application object.

We can use export for only few tables but what about the rest?

Do you have any suggestions on how to solve these CONVERTIBLE datas of Data Dictionary from the DB?

[Updated on: Tue, 14 April 2009 08:00]

Report message to a moderator

Re: Solving Convertible Data [message #397937 is a reply to message #397887] Tue, 14 April 2009 11:21 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


>>"exceed column size" will be the exception when we have data that needs length to be extended.

I think whlie imporing You won't get any error; becuase in your csscan logfile there is no problem.

Accoring to system object. Your not going to import. Can you tel me your upgrade steps (One character set to another character set).

Babu
Re: Solving Convertible Data [message #397957 is a reply to message #397937] Tue, 14 April 2009 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
becuase in your csscan logfile there is no proble

Wrong, csscan will report these data as "Truncation"

Regards
Michel
Re: Solving Convertible Data [message #397958 is a reply to message #397937] Tue, 14 April 2009 12:32 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
1) Oracle characterset WE8ISO8859P1 means Western European 8 bit ISO 8859 point 1. (ISO 8859-1) also commonly called ISO-1.
2) UTF-8 is an encoding scheme for Unicode, there are several others.
3) In the days of Oracle 8 (from what I recall), there was an Oracle characterset called UTF8. This characterset had an error (from what I recall) so it was superceded by AL32UTF8. You should probably be looking at AL32UTF8 for a database characterset rather than UTF8.

Unicode is a superset of all other charactersets, so that means that any (valid) character in WE8ISO8859P1 can be converted to AL32UTF8. Having incorrect NLS_LANG client settings is a common cause of invalid characters being stored into databases.
Re: Solving Convertible Data [message #398150 is a reply to message #397958] Wed, 15 April 2009 06:34 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Do you have any suggestions on how to solve these CONVERTIBLE datas of Data Dictionary from the attached CSScan err report file?
  • Attachment: charcheck.txt
    (Size: 137.22KB, Downloaded 1977 times)
Re: Solving Convertible Data [message #398202 is a reply to message #398150] Wed, 15 April 2009 12:15 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
Wrong, csscan will report these data as "Truncation"


Mr Mic; Explain me; Problem in User Object ??

Babu
Re: Solving Convertible Data [message #398204 is a reply to message #398202] Wed, 15 April 2009 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your question.

Regards
Michel
Re: Solving Convertible Data [message #398207 is a reply to message #398204] Wed, 15 April 2009 12:31 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
Quote:
becuase in your csscan logfile there is no proble



Wrong, csscan will report these data as "Truncation"


Your saying; What I posted that is wrong information. Please explain us why it's wrong ??

>>csscan will report these data as "Truncation"

Which object data's truncation as per csscan report.

Babu
Re: Solving Convertible Data [message #398216 is a reply to message #398207] Wed, 15 April 2009 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When migrating to AL32UTF8 if some data cannot migrate because of length csscan report them in "Truncation" category not in "Changeless" one (which I think you meant by "no problem").
But maybe I wrongly understood your post...

Regards
Michel
Re: Solving Convertible Data [message #398221 is a reply to message #398216] Wed, 15 April 2009 13:15 Go to previous messageGo to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Thanks Mr Mic,

@rtnataraj; You can go-head with your upgrade.

ps; I think your thinking about sys/system object data's also. Don't need to worry about those data's becuase your not going to export/import those data. You need only focus your users data.

Babu
Previous Topic: SQL LOADER INDEX ISSUE
Next Topic: SCN/data doesn't update after the restore
Goto Forum:
  


Current Time: Fri Mar 29 08:41:02 CDT 2024