Re: Change database character set
Date: Fri, 24 Oct 2014 11:12:41 -0400
Message-ID: <CAEidWqNkiUoX6W7VNvSgi0nPYPEX2Y+PFbMZV7uwuogEVqcKYQ_at_mail.gmail.com>
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_at_rit.edu> 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_at_rit.edu)
>
> (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_at_gmail.com]
> *Sent:* Friday, October 24, 2014 8:49 AM
> *To:* Kenny Payton
> *Cc:* oracle.unknowns_at_gmail.com; Scott Canaan; Chris Taylor;
> oracle-l_at_freelists.org
>
> *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_at_gmail.com> wrote:
>
> I don't believe that worked for us.
>
> On Oct 24, 2014 8:30 AM, "Niall Litchfield" <niall.litchfield_at_gmail.com>
> wrote:
>
> You can verify the Agent issue by stopping it (or dbconsole) and re running
>
> On 24 Oct 2014 13:10, "Kenny Payton" <k3nnyp_at_gmail.com> 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_at_rit.edu> 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_at_rit.edu)
>
> (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_at_gmail.com]
> *Sent:* Friday, October 24, 2014 8:01 AM
> *To:* Niall Litchfield
> *Cc:* Scott Canaan; oracle.unknowns_at_gmail.com; oracle-l_at_freelists.org
> *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_at_gmail.com> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 24 2014 - 17:12:41 CEST