Oracle 11gR2: NLS_CHARACTERSET accidentally removed with an UPDATE-Query

configurationoracleoracle-11g

I have a fresh installation of Oracle 11gr2_x64 on CentOS. After the installation I wanted to get productive and started to import my dumps. One of the dumps caused characterset error so I tried to change the systems character-set to the one specified in the dump.

I ran a statement like this:

UPDATE nls_database_parameters SET parameter='WS....' WHERE parameter=’NLS_CHARACTERSET’;

As you can see: I have written the value of the character-set in the parameter column instead of the value column. I guess I was just too much thinking about the problem instead of checking what I am typing there. After the query the parameter "NLS_CHARACTERSET" is gone and the server reports that the characterset is "(null)".

I want to put the "NLS_CHARACTERSET" paramater back in the table but don't know how. If I try to do something like this

INSERT INTO nls_database_parameters (PARAMETERS, VALUE) VALUES ("NLS_CHARACTERSET", "AL32UTF8");

I get the error:

Fehler bei Befehlszeile:1 Spalte:84
Fehlerbericht:
*Cause: SQL-Fehler: ORA-00984: Spalte hier nicht zulässig
*Action: 00984. 00000 -  "column not allowed here"

Sorry that the error message is in German but it contains the Oracle error code.

Do you have any idea how I can fix that?

Thanks and best regards

Marco

Best Answer

Your very first error is to modify an object owned by SYS. You should not UPDATE/DELETE/TRUNCATE any object owned by SYS unless expressly told by support. Oracle provides APIs (packages) and DDL commands (ALTER DATABASE, etc.) to interact with the system objects and you should not use anything else.

Now, this is not how you change the database character set. The very first hit on google "oracle change character set" provides an example of how to change your character set.

Since it is a fresh install I suggest you bite the bullet and reinstalls a clean database with the desired character set. If you really don't want to reinstall, I suggest you contact support.

Edit

Funnily, I browsed the MOS support site to find out how to change the character set and I found out this note Changing the Database Character Set [225912.1] with this interesting note:

There are still "dba's" out there who try to change the NLS_CHARACTERSET or NLS_NCHAR_CHARACTERSET by updating props$ . This is NOT supported and WILL corrupt your database. This is one of the best way's to destroy a complete dataset. Oracle Support will TRY to help you out of this but Oracle will NOT warrant that the data can be recoverd or recovered data is correct. You WILL be asked to do a FULL export and a complete rebuild of the database.

Please, do NOT update props$.

Related Topic