This could be because the character endpoint exists in both charactersets. Win1252 is a superset of 8859. You're lucky. 9 affected rows is a walk in the park. Reloaded is certainly the way to go. I wrote an interactive perl cgi script to query the database and let me select charactersets from a drop down until I had visually identified the correct characterset for identified problem characters. I then wrote a process to correct millions of rows as part of the process. DMU provides a lot of this functionality now but was pretty immature and buggy when I was attempting to use it. I was also dealing with 20T+ databases with a 6 hour window and had to get pretty creative. Unfortunately csalter requires a clean scan, this was the hardest hurdle to circumvent. On Oct 24, 2014 4:08 PM, "Scott Canaan" <srcdco@xxxxxxx> wrote: > Actually, the report lists the character sets at the end. > Unfortunately, in my case, it lists 2 different character sets for the > “bad” 9 rows: > > > > CHARACTER SET NUMBER PERCENTAGE > > ------------------------------ ---------------- ---------------- > > UNKNOWN 350400 99.997% > > WE8ISO8859P1 7 0.002% > > WE8MSWIN1252 2 0.001% > > ------------------------------ ---------------- ---------------- > > Total 350409 100.000% > > > > [Language detection result] > > > > LANGUAGE NUMBER PERCENTAGE > > ------------------------------ ---------------- ---------------- > > UNKNOWN 350400 99.997% > > ENGLISH 9 0.003% > > ------------------------------ ---------------- ---------------- > > Total 350409 100.000% > > > > Which tells me that the data was entered on 2 different PCs with different > character sets. I’m going to have to insist that they re-enter this data > once the conversion (and application upgrade) is complete. > > > > Scott Canaan ’88 (srcdco@xxxxxxx) > > (585) 475-7886 – work (585) 339-8659 – cell > > “Life is like a sewer, what you get out of it depends on what you put into > it.” – Tom Lehrer > > > > *From:* Kenny Payton [mailto:k3nnyp@xxxxxxxxx] > *Sent:* Friday, October 24, 2014 11:13 AM > *To:* Scott Canaan > *Cc:* Niall Litchfield; oracle.unknowns@xxxxxxxxx; Chris Taylor; > oracle-l@xxxxxxxxxxxxx > *Subject:* Re: Change database character set > > > > There is an option to csscan that will populate some columns in one of the > tables, I believe it's added to csm$errors. If I recall correctly the > column names are something like CSIDLED1, ...2, ....3. It will provide up > to 3 educated guesses as to which character set the data might have came > from. > > > LCSDDATA=LOSSY LCSD=Y > > You can also look at SYS_OP_CSCONV to convert the data. > > $update=sprintf("update %s.%s set %s=SYS_OP_CSCONV(%s,'%s','%s' ) > where rowid = ?", $err->own, $err->tab, $err->col, $err->col, $tocs, $cs); > > We had millions of rows with embedded characters, such as the fancy quotes > from word documents, in varchar2 fields and had to convert them manually. > > > > > > > On Fri, Oct 24, 2014 at 10:26 AM, Scott Canaan <srcdco@xxxxxxx> wrote: > > What I ended up doing was to create a blackout in OEM, then kill the > dbsnmp connections and truncate the reg$ table. That took care of that > error. The remaining 9 rows in the application data remain. I’ve been > working with Oracle support and they suggested checking against > WE8MSWIN1252. I did that and got the same 9 rows, so I still don’t know > what characterset the data was entered with and I don’t know how to find > out. > > I’ve asked the person that entered the data if she still has the files and > can re-enter it after the conversion and upgrade. I hope so, that will > simplify things a lot. > > > > Scott Canaan ’88 (srcdco@xxxxxxx) > > (585) 475-7886 – work (585) 339-8659 – cell > > “Life is like a sewer, what you get out of it depends on what you put into > it.” – Tom Lehrer > > > > *From:* Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx] > *Sent:* Friday, October 24, 2014 8:49 AM > *To:* Kenny Payton > *Cc:* oracle.unknowns@xxxxxxxxx; Scott Canaan; Chris Taylor; > oracle-l@xxxxxxxxxxxxx > > > *Subject:* Re: Change database character set > > > > Now I'm at my desk > > *Lossy or Convertible Conversion Data In SYS.REG$ Table (Doc ID 1490394.1) > *is the note I meant. > > sys.reg$ table data is related to AQ or the EM agent. This table should > not contain any data if there is no connections to the database. > > Ideally there should not be any sessions in the database while running > csscan as it can cause problems when you do the convert if rows are > deleted. > > > > On Fri, Oct 24, 2014 at 1:32 PM, Kenny Payton <k3nnyp@xxxxxxxxx> wrote: > > I don't believe that worked for us. > > On Oct 24, 2014 8:30 AM, "Niall Litchfield" <niall.litchfield@xxxxxxxxx> > wrote: > > You can verify the Agent issue by stopping it (or dbconsole) and re running > > On 24 Oct 2014 13:10, "Kenny Payton" <k3nnyp@xxxxxxxxx> wrote: > > It sounds like you're getting off easy. As for the sys.reg$ row we had a > similar row and it was related to the emagent. We probably could have got > away with just deleting it but what we did was create a table in a user > schema with the row in it, export the user table, delete the row from both > tables and then reload the row after the csalter. In hindsight this > probably wasn't necessary but worked. > > You could probably do the same with the clob data as well. > > > > On Fri, Oct 24, 2014 at 8:04 AM, Scott Canaan <srcdco@xxxxxxx> wrote: > > Actually, I’ve been considering doing something similar to this. The 9 > rows in that table (out of about 20) that are affected were added in August > and the person that added them still has the original documents, so they > could be reloaded after the conversion. I believe that what is in the > HUGECLOB in that table is just a word document that was cut and pasted into > the field. > > > > The only row that really concerns me is the one in SYS.REG$. > > > > Scott Canaan ’88 (srcdco@xxxxxxx) > > (585) 475-7886 – work (585) 339-8659 – cell > > “Life is like a sewer, what you get out of it depends on what you put into > it.” – Tom Lehrer > > > > *From:* Chris Taylor [mailto:christopherdtaylor1994@xxxxxxxxx] > *Sent:* Friday, October 24, 2014 8:01 AM > *To:* Niall Litchfield > *Cc:* Scott Canaan; oracle.unknowns@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx > *Subject:* Re: Change database character set > > > > Based on Niall's analysis, another option could be to export the > SCRIPT_GROUP table, truncate it, do the conversion and reload it (depending > on the size). > > > > Chris > > > > > > On Fri, Oct 24, 2014 at 5:02 AM, Niall Litchfield < > niall.litchfield@xxxxxxxxx> wrote: > > Scott > > > > I'm pretty sure you'll find the session_key row is transient and due to an > emagent (either grid/cloud control or dbconsole) - there's a note on csscan > dictionary errors that will tell you. That leaves you with 9 documents > (they look like word docs/emails). Can you grab those from the app and > reenter the data post conversion? > > > > > > > > > > -- > Niall Litchfield > Oracle DBA > http://www.orawin.info > > >