Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

I resolved it myself:

If you browse the table listings of an Oracle database you have a table called "PROPS$". I found my falsely overwritten column that looked like this:

Parameter     Value
WE8ISO8859P1, AL32UTF8

I update the column with the SQLDeveloper back to "NLS_CHARACTERSET" and my database is running fine again.

The table "NLS_DATABASE_PARAMETERS" is just a View that filters several parameter Tables. I think I just could not run updates on that View and kicked the column out. I will do some research on how I change the character-set properly now.

Thanks for your help and suggestions!

Marco

share|improve this 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$.

share|improve this answer
    
Thanks! I guess I have to admit that I did a mistake :) Thanks anyway for your advice... I am a freshmen with Oracle and think I'll bite the bullet and reinstall it... But I am still curious that I can destroy that much with the "sys" user. Even if I have this power with the "sys" user I should have it to reverse my actions, haven't I? –  Marco Nätlitz May 28 '10 at 10:00
    
@Marco Nätlitz: think of SYS in Oracle as root in Linux/UNIX. Would you recover from a rm -rf / :) ? –  Vincent May 31 '10 at 12:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.