How to check your Operating System's code page for a particular character

I have been involved in several migration projects where we had to deal with Western European characters, which have invalid representation in the database, and needs to be converted.

Like I showed in my post about usage of the chr function, you sometimes need to find the decimal and/or hexadecimal value for a particular character.

But how can you verify that your operating system actually agrees; how would your Operating System translate a character passed from a terminal, e.g. putty?

First, make sure your putty terminal has its translation-settings set to the character set of the server you are logging into: Right-click your putty terminal upper frame, and pick "Change Settings" from the menu. Go to the "translation" settings and then select the correct character set from the drop-down menu "Remote character set".

On Linux platforms, a simple way to check how a character would be translated would be to use the hexdump utility. Thanks to Gray Watson for demonstrating this!

man hexdump
The hexdump utility is a filter which displays the specified files, or the standard input, if no files are specified, in a user specified format.

Let's try to hexdump the character ø, and see what the internal hexadecimal representation is:

echo "ø" | hexdump -v -e '"x" 1/1 "%02X" " "'
xC3 xB8 x0A 

The prefix x in front of the values represents hexadecimal values, so the important part here is "C3 and B8" - in a multibyte character set this represent the Scandinavian character ø ( I must admit, I never figured out what the 0A represents. Anyone?)

Another way is to use the "od" utility:

man od

od - dump files in octal and other formats
       -x     same as -t x2, select hexadecimal 2-byte units

Using the -x flag straight off will give the hexadecimal value in 2-byte units:

echo "ø" | od -x
0000000 b8c3 000a
0000003

This time, the values are cast around, and should be read backwards for meaning. I have not found an explanation to why od does this. Anyone?

However, if you use the -t x notation instead,:

echo "ø" | od -t x1
0000000 c3 b8 0a
0000003

The values come out as a DBA expects; c3b8 corresponds to decimal value 50104 which in turn represent the Scandinavian letter ø.

( And again, I never figured out what the 0a represents. Anyone?)

How to work around ORA-29275: partial multibyte character when using chr function

If you need to check what is actually stored in a database table, meaning the internal representation, you can use the DUMP function.

It will return a varcar2 value containing the data type code, length in bytes, and internal representation

SQL> select dump ('ø',1010) from dual;

DUMP('Ø',1010)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL32UTF8: 195,184

So the type above is 96, representing a VARCHAR2 or NVARCHAR datatype, the length is 2 bytes, the characterset of the database is AL32UTF8 and the decimal values of each byte is 195 and 184.

For single-byte character set, it would be easy to check which character the decimal represented under the database's characterset; simply use the chr function and pass the decimal value as an argument.

However with a multibyte character set, which one of the returned decimal functions should you choose?

SQL> select chr(195) from dual;

ERROR:
ORA-29275: partial multibyte character

A workaround is to dump the character to hexadecimal format instead of decimal, by using the argument 1016 as the second argument to the function:

SQL>  select dump ('ø',1016) from dual;

DUMP('Ø',1016)
--------------------------------------------------------------------------------
Typ=96 Len=2 CharacterSet=AL32UTF8: c3,b8

The c3b8 hexadecimal value corresponds to the decimal value of 50104. Pass this value to the chr function and you get the result you're looking for:

SQL> select chr(50104) from dual;

CHR(50
------
ø

A brilliant converter between decimal and hexadecimal numbers can be found here

The documentation for the dump function can be found at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions055.htm#SQLRF00635

The documentation for the chr function can be found at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions026.htm#SQLRF00616

The datatype table is listed here: https://docs.oracle.com/cd/B19306_01/appdev.102/b14250/oci03typ.htm#i422575

Is it possible to change the character set of the database by recreating the control file?

Question: Is it possible to change the character set of the database by recreating the controlfil?
Answer: No, this is not supported.

Doc ID 225912.1 states:

"Note also that it's NOT possible to change / adapt the NLS_CHARACTERSET by re-creating the control file.
The characterset specified in CREATE CONTROLFILE DATABASE ... RESETLOGS ....CHARACTER SET ; ( the result of an ALTER DATABASE BACKUP CONTROLFILE TO TRACE..) need to be the actual current NLS_CHARACTERSET of the database."

Question: So why does CREATE CONTROLFILE generate a CHARACTERSET clause in the first place then?
Answer: In case media recovery is required after the controlfile has been recreated.

From the documentation:
"If you specify a character set, then Oracle Database reconstructs character set information in the control file. If media recovery of the database is subsequently required, then this information will be available before the database is open, so that tablespace names can be correctly interpreted during recovery."

The documentation goes on to say

"This clause is required only if you are using a character set other than the default, which depends on your operating system."

Without having found any sources on the internet to confirm my interpretation, I will claim that this means:

* If you recreate your controlfile for a database with, say, WE8ISO8859P1 as character set, you will indeed need to specify the character in the CREATE CONTROLFILE clause.

* If you recreate the controlfile of a database using UTF8, for example AL32UT8, you can skip this clause altogether if you wish

How to pause an Oracle database

Sometimes it is useful to pause a database, and let it finish the running jobs at a later time.
Particularly in environments with shared resources contention may occur. Important databases need to be prioritized while less important ones should be suspended.

For this purpose, you can use the "suspend" command:

alter system suspend;

Alert log:

Mon Oct 05 10:14:49 2015
Suspending database after alter system suspend command

The database_status field in v$instance will reflect the new state of the database:

SQL> select database_status from v$instance;

 DATABASE_STATUS
-----------------

SUSPENDED

When you are ready to resume database activity, issue the following command:

alter system resume;

More info: Oracle Documentation

How to recursively zip a folder and its subfolders

Use the -r flag to indicate recursive zipping:

zip -r myfile.zip myfolder

How to unzip multiple files on a Unix-based operating systems

When extracting multiple zip files, remember to use single quotes to mask the zip-files:

unzip '*.zip'

For example, when unpacking the files needed to install Oracle 11.2.0.4:

[oracle@lx01 Downloads]$ ls -latr
total 5586768
-rwxr-x---.  1 root   root     1395582860 Sep  2 10:14 p13390677_112040_Linux-x86-64_1of7.zip
-rwxr-x---.  1 root   root     1151304589 Sep  2 10:14 p13390677_112040_Linux-x86-64_2of7.zip
-rwxr-x---.  1 root   root     1205251894 Sep  2 10:15 p13390677_112040_Linux-x86-64_3of7.zip
-rwxr-x---.  1 root   root      656026876 Sep  2 10:16 p13390677_112040_Linux-x86-64_4of7.zip
-rwxr-x---.  1 root   root      599170344 Sep  2 10:16 p13390677_112040_Linux-x86-64_5of7.zip
-rwxr-x---.  1 root   root      488372844 Sep  2 10:16 p13390677_112040_Linux-x86-64_6of7.zip
-rwxr-x---.  1 root   root      119521122 Sep  2 10:16 p13390677_112040_Linux-x86-64_7of7.zip
drwx------. 17 oracle oinstall       4096 Sep  2 10:27 ..
drwxr-xr-x.  2 oracle oinstall       4096 Sep  2 10:34 .
[oracle@lx01 Downloads]$ unzip 'p13390677_112040_Linux-x86-64_*.zip'

Without the quotes, unzip will look for "zip files within zip files".

Thanks to Chris Jean for the tip!

Oracle 11g out-of-place vs. in-place database upgrades

Starting with Oracle 11gR2, Oracle changed their patching strategy.

Previously, a patch set was supposed to be downloaded and installed into an existing ORACLE_HOME.
After 11.2, a "patch set" is really a full release, bundled in a number of zip files.

The default patching method is the "out-out-place", which means that every time customers need to upgrade to a new patch set (say, 11.2.0.4), a new ORACLE_HOME should be created, and the new binaries should be installed here.

So you will potentially end up with a number of ORACLE_HOME on your server, and you can choose to run your database out of either of these, upgrading whenever you please.

See Mike Dietrich's post about the same topic for more details.