Changing an huge table column type Long to Clob

Question

Background: I want to change a table column from Long to Clob.

• I am not allowed to create/alter tablespaces
• The table has 10M + rows

What i have tried:

1. Simply alter table from long to clob:

   • returns full tablespace error - ORA-01652: "unable to extend temp segment by %s in tablespace %s"
     

     ---

2. Export data from table and insert in a new table:

   • export just takes too long, at the speed its going now, it will take weeks to export.

     ---

3. Running "insert /*+ APPEND */ into new_table select * from old_table;"

   • returns an error - ORA-00997: "illegal use of LONG datatype"

Answer 1

Just an alternative. (Always test in preproduction before)

• Create a function that casts long to clob.

• Then try your third option using the function:

  insert /*+ APPEND */ into new_table select id, long_to_clob(myLob) as myclob from old_table;

• Drop the long field.

• Add a new clob field.

• Update the new field.

  UPDATE old_table o SET new_field =( SELECT myclob FROM newTable n WHERE n.id = o.id) WHERE 1=1;