Longs can be converted to clobs
http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_long_lob.htm#sthref873 (but it requires a copy of the table be made under the covers)
Note:
The ALTER TABLE statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements.Your choices in this case will be:
a) new columnops$tkyte%ORA10GR2> create table t ( x int, y varchar2(4000) );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1, 'hello' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t modify( y clob );
alter table t modify( y clob )
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
ops$tkyte%ORA10GR2> !oerr ora 22858
22858, 00000, "invalid alteration of datatype"
// *Cause: An attempt was made to modify the column type to object, REF,
// nested table, VARRAY or LOB type.
// *Action: Create a new column of the desired type and copy the current
// column data to the new type using the appropriate type
// constructor.
ops$tkyte%ORA10GR2> alter table t add ( temp clob );
Table altered.
ops$tkyte%ORA10GR2> update t set temp=y, y=null;
1 row updated.
ops$tkyte%ORA10GR2> alter table t drop column y;
Table altered.
ops$tkyte%ORA10GR2> alter table t rename column temp to y;
Table altered.
ops$tkyte%ORA10GR2> desc t
Name Null? Type
---------------------------------------- -------- ----------------------------
X NUMBER(38)
Y CLOB
ops$tkyte%ORA10GR2> select * from t;
X
----------
Y
-------------------------------------------------------------------------------
1
hello
b) online redefineops$tkyte%ORA10GR2> create table t ( x int primary key, y varchar2(4000) );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1, 'hello' );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2( x int, y clob );
Table created.
ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T', 'T2', 'x, to_clob(y) y' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> pause
ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
2 dbms_redefinition.copy_table_dependents
3 ( user, 'T', 'T2',
4 copy_indexes => dbms_redefinition.cons_orig_params,
5 num_errors => :nerrors );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print nerrors
NERRORS
----------
0
ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T', 'T2' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> drop table t2;
Table dropped.
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T"
( "X" NUMBER(*,0),
"Y" CLOB,
CONSTRAINT "SYS_C0038059" PRIMARY KEY ("X")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE NOVALIDATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("Y") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
ops$tkyte%ORA10GR2> select * from t;
X
----------
Y
-------------------------------------------------------------------------------
1
hello
c) create table as select
just create new table as select from the old (using to_lob on the column) and then index, constrain, etc the new table, then drop old, rename new to old.