ORA-01446

This was tested on Oracle 11.2. First I created a table and inserted 2 identical values in it:

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

SQL>

Then I looked at the rowids of the values I had inserted:

SQL> select rowid, col1 from tab1

  2  /

 

ROWID                    COL1

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

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Next I created a view and checked that it referred back to the underlying table and picked up the correct rowids again: 

SQL> create view view1

  2  as select col1 from tab1

  3  /

 

View created.

 

SQL> select rowid, col1 from view1

  2  /

 

ROWID                    COL1

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

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Finally I created a view to show the distinct values in the table. This worked as expected too: 

SQL> create view view2

  2  as select distinct col1 from tab1

  3  /

 

View created.

 

SQL> select col1 from view2

  2  /

 

      COL1

----------

         1

SQL> 

But when I tried to look at the rowids in this view, I got an ORA-01446 instead. The rowids in the table itself gave details of each row's physical location in the database. So when I passed these rows through a DISTINCT into a view, there were two occurrences of the value 1 in the table giving one DISTINCT value in the view. Oracle then did not know which row's rowid to associate with the value displayed: 

SQL> select rowid, col1 from view2

  2  /

select rowid, col1 from view2

       *

ERROR at line 1:

ORA-01446: cannot select ROWID from, or sample, a view

with DISTINCT, GROUP BY, etc.

 

SQL>

When Did a Non-Existent Rowid Cause an ORA-00600?

I was looking through some old notes today. I found some which showed a query trying to use a non-existent rowid to access a table. This caused an ORA-00600 with the following arguments:
[2846], [6], [77249], [1], [51200], [], [], []
There was no date on the notes nor any mention of an Oracle version but I decided to try it out
on an Oracle 10 database. First I created a table with 1 row and checked its rowid:

SQL> create table andrews_table
  2  as select 1 col1 from dual
  3  /

Table created.

SQL> select rowid, col1 from andrews_table
  2  /

ROWID                    COL1
------------------ ----------
AAAMl6AABAAAOtqAAA          1

SQL>

Then I checked that I could use the rowid to retrieve the row:

SQL> select col1 from andrews_table
  2  where rowid = 'AAAMl6AABAAAOtqAAA'
  3  /

      COL1
----------
         1

SQL>

Then I made a small change to the rowid and tried again:

SQL> l
  1  select col1 from andrews_table
  2* where rowid = 'BAAMl6AABAAAOtqAAA'
SQL> /
select col1 from andrews_table
                 *
ERROR at line 1:
ORA-01410: invalid ROWID

SQL>

This only produced an ORA-01410 error message. I looked in the alert log but there was no sign of an ORA-00600 message there either. So I assume that the ORA-00600 messages were produced by a bug which has since been fixed.

How to Change a Column from Number to Varchar2

This post, tested on Oracle 9, shows how to change a column definition from number to varchar2. First, create a test table:

SQL> col my_letter format a9
SQL> col my_number format 999999999
SQL> create table andrew1
  2  (my_letter varchar2(1),
  3   my_number number(1))
  4  /

Table created.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        NUMBER(1)

SQL>

Then add three rows of data:

SQL> insert into andrew1 values('A',1)
  2  /

1 row created.

SQL> insert into andrew1 values('B',2)
  2  /

1 row created.

SQL> insert into andrew1 values('C',3)
  2  /

1 row created.

SQL> select * from andrew1
  2  /

MY_LETTER  MY_NUMBER
--------- ----------
A                  1
B                  2
C                  3

SQL>

Next, try to modify the number column. This will fail and display an appropriate error message:

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /
alter table andrew1 modify(my_number varchar2(1))
                           *
ERROR at line 1:
ORA-01439: column to be modified must be empty to
change datatype

SQL>

Create another table and store the values in the column to be modified along with their rowids: 

SQL> create table andrew2 as
  2  select rowid my_rowid, my_number from andrew1
  3  /

Table created.

SQL>

Now remove the values from the column being modified and try the modify command again. This time it works:

SQL> update andrew1 set my_number = null
  2  /

3 rows updated.

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /

Table altered.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        VARCHAR2(1)

SQL>

Finally, using the rowid, reinstate the values in the modified column:

SQL> update andrew1 x
  2  set my_number =
  3  (select my_number from andrew2
  4   where my_rowid = x.rowid)
  5  /

3 rows updated.

SQL> col my_number format a9
SQL> select * from andrew1
  2  /

MY_LETTER MY_NUMBER
--------- ---------
A         1
B         2
C         3

SQL>