ORA-01754

The first example below was tested on Oracle 11.2. If you try to have more than one long column in a table, you get an ORA-01754:

SQL> create table tab1 (col1 long, col2 long)

  2  /

create table tab1 (col1 long, col2 long)

                              *

ERROR at line 1:

ORA-01754: a table may contain only one column of type LONG

 

SQL> 

That was fairly obvious. Now here is another example, which I wrote at the start of 2012. I do not know which Oracle version I did it on. First I created a table with one column:

SQL> create table andrews_table

  2  (col1 varchar2(10))

  3  /

Table created.

SQL>

Then I added a long column to the table and described it to show the column I had just added:

SQL> alter table andrews_table

  2  add (col2 long)

  3  /

Table altered.

SQL> desc andrews_table

Name                      Null?    Type

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

COL1                                VARCHAR2(10)

COL2                                LONG

SQL>

Next I set the long column to unused. This does not restore the disk space used by the column:

SQL> alter table andrews_table
  2  set unused column col2
  3  /

Table altered.

SQL>

I described the table again to show that the long column had gone:

SQL> desc andrews_table
Name                      Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(10)

SQL>

Then I tried to add another long column. This failed as you can only have 1 long column per table. How would you diagnose this problem if it happened to you in real life?

SQL> alter table andrews_table
  2  add (col3 long)
  3  /
add (col3 long)
    *
ERROR at line 2:
ORA-01754: a table may contain only one column of type
LONG

SQL>

You can get a clue by looking in user_unused_col_tabs, which confirms that the table has 1 unused column:

SQL> select count(*) from user_unused_col_tabs

  2  where table_name = 'ANDREWS_TABLE'

  3  /

  COUNT(*)

----------

         1

SQL>

Drop the unused column from the table. This frees up the disk space which the column occupied:

SQL> alter table andrews_table
  2  drop unused columns
  3  /

Table altered.

SQL>

The table no longer has an entry in user_unused_col_tabs:

SQL> select count(*) from user_unused_col_tabs
  2  where table_name = 'ANDREWS_TABLE'
  3  /

no rows selected

SQL>

And you can add another long column if you wish:

SQL> alter table andrews_table
  2  add (col3 long)
  3  /

Table altered.

SQL>