Monday, September 08, 2014

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…

No comments: