ORA-01460 on PL/SQL Developer

An Oracle 9 database had the following character set:

 

SQL> l

  1  select sys_context('userenv','LANGUAGE')

  2* from dual

SQL> /

 

SYS_CONTEXT('USERENV','LANGUAGE')

----------------------------------------

ENGLISH_UNITED KINGDOM.WE8ISO8859P15

 

SQL>

 

I used PL/SQL Developer to look at the code in the database (as usual, click on the image to enlarge it and bring it into focus):

 

Somebody typed the following command in the database by mistake:

 

SQL> l

  1* create database sonar character set utf8

SQL> /

create database sonar character set utf8

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

I logged in to PL/SQL Developer again and found that I got an ORA-01460 when I tried to look at the same code in the database:

I also noticed that the database had a new character set:

 

SQL> l

  1  select sys_context('userenv','LANGUAGE')

  2* from dual

SQL> /

 

SYS_CONTEXT('USERENV','LANGUAGE')

----------------------------------------

ENGLISH_UNITED KINGDOM.UTF8

 

SQL>

I guess this is a bug as the user account which typed the create database command had no special privileges and Oracle replied with an ORA-01031. Therefore no damage should have been caused.

Three days later:

I logged in as a privileged user and tried to change the character set back but this failed, in line with the Oracle documentation:

SQL> conn / as sysdba

Connected.

SQL> alter database character set we8iso8859p15

  2  /

alter database character set we8iso8859p15

*

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

 

SQL>

 

So, as a last resort, I logged back in with an ordinary user and tried to reverse the damage like this:

 

SQL> conn andrew/reid

Connected.

SQL> create database sonar character set we8iso8859p15

  2  /

create database sonar character set we8iso8859p15

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

This seemed to have the desired effect, at least in the short term as the character set went back to its original value:

 

SQL> select sys_context('userenv','LANGUAGE')

  2  from dual

  3  /

 

SYS_CONTEXT('USERENV','LANGUAGE')

----------------------------------------

AMERICAN_AMERICA.WE8ISO8859P15

 

SQL>

 

I will report back as the problem develops…