Re: Changing Character Set
- From: "Faan DeSwardt" <faan@xxxxxxxxxxxxx>
- To: <Ken.Payton@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Jul 2004 11:47:11 -0700
Ken,
If you have already completed the character set scanner (csscan) exercise (a
MUST before going any further) and know EXACTLY what the impact will be on
your database after the conversion you can use the following line to
circumvent the superset check:
ALTER DATABASE CHARACTER SET INTERNAL_USE <<new character set>>;
Again, I cannot stress enough that this should only be done after complete
and thorough testing and a complete understanding of what will happen to
your "corrupted" character data (the csscan report will list all such data)
once in the new character set. Don't forget to test all your interfaces as
well. You can have surprising results if you feed data from your new multi
byte database to a legacy single byte database.
Be very careful and perform extensive testing if you have Java applications
(this could be your application servers, web clients, etc.) accessing this
database as Java performs very strict character set conversions and will
dump a stack trace if it encounters any of this "corrupted" and unconforming
data. I have seen this numerous times when databases are converted from
WE8ISO8859P? and US7ASCII to UTF8 with "corrupted" data.
Also consider the impact on all your existing code that involves string
operations if your source character set is single byte e.g. US7ASCII and
your target character set is multi byte e.g. UTF8. Also be aware of
scenarios where a character in a single byte character set like WE8ISO8859P1
takes up 2 bytes in a multi byte character set like UTF8. In this example
this is true for all characters in WE8ISO8859P1 that are beyond the 7th bit
(i.e. char(x) where x > 128) in this ISO character set. This can really
throw you off when performing length comparisons.
Also keep sorting in mind as the default is binary sorting but this may not
be applicable anymore once you move to a multi byte character set where you
may need to use character semantic sorting.
If you're on 9i+ and have access to the source code of the applications that
run against this newly converted DB you should seriously consider using
character semantics as the default and define all character variables to use
this newly available option. It would make the life of your developers much
easier and avoid many logical errors that are not that easy to detect in
initial UAT testing but are bound to show up in production.
Obviously RTFM the globalization docs (they are really good) and make sure
you are aware of all the implications as I have just touched on some of the
often overlooked items above.
You may save time and effort now but taking a shortcut will probably cost
you pain later and when you can least afford it (year-end in production!) so
think about the long term implications before doing this.
-f
----- Original Message -----
From: "Ken Payton" <Ken.Payton@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, July 30, 2004 5:01 AM
Subject: Changing Character Set
> Does anyone know how to switch off the superset check when changing the =
> national character set. I would like to change the character set of a =
> new database without rebuilding it and I am receiving ORA-12714: invalid =
> national character set specified. =20
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tim Gorman
> Sent: Friday, July 30, 2004 5:38 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Lost PERFSTAT bridge scripts Found it
>
>
> Larry,
> Thanks for the post of Mr Hurley's material. Especially useful is the =
> use
> of AUTONOMOUS TRANSACTION pragma, although I've not found it to be
> necessary...
>
> I can understand performing a STATSPACK.SNAP before database shutdown, =
> to
> "flush" any values to disk before they are lost, but I am at a loss to
> understand the reason to perform a STATSPACK.SNAP in an AFTER STARTUP
> database-event trigger?
>
> -Tim
>
>
> on 7/29/04 1:49 PM, Wolfson Larry - lwolfs at =
> lawrence.wolfson@xxxxxxxxxx
> wrote:
>
> > Guess I didn't get specific enough on Google the first time.
> >=20
> > Sorry
> >=20
> > http://www.quest-pipelines.com/pipelines/dba/tips03.htm#january
> >=20
> > January's Tip of the Month
> >=20
> > Automatic Statspack Snapshots at Shutdown and Startup
> > Compliments of Darryl Hurley, Pipeline SYSOP (dhurley@xxxxxxxxxx)
> >=20
> > Oracle?s Statspack utility provides a straightforward method of =
> monitoring
> > database performance statistics. The process is simple; take interval
> > snapshots of performance indicators and then run reports to see how =
> much the
> > indicators have changed during the interval(s).
> >=20
> > Problems arise when intervals span an Oracle shutdown because =
> comparing
> > interval values across them is illogical. Here?s an example:
> >=20
> > 10:00 PM Statspack Snapshot #33 shows Physical Reads =3D 100000
> > 10:15 PM Database Shutdown
> > 10:20 PM Database Restarted
> > 11:00 PM Statspack Snapshot #34 shows Physical Reads =3D 100
> >=20
> > At this point a StatsPack Report comparing snapshot #33 to snapshot =
> #34
> > would claim that ?99900 physical reads had occurred. Actually the =
> report
> > would begin with this self-explanatory text:
> >=20
> > ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
> >=20
> > It?s impossible to report across a shutdown, but it is possible to =
> reduce
> > the lost periods of time (10:00 to 10:15 and 10:20 to 11:00 in our =
> example)
> > by automatically performing snapshots before shutdown and after =
> startup.
> > It?s easily done with BEFORE-SHUTDOWN and AFTER-STARTUP triggers.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
