How to convert a varchar column to "clob"

We can do it in three methods:

A. Online Redefinition
B. Create table as select * from
C. Alternate way, explained below.

A) Online Redefinition 

   
sql> create table t ( x int primary key, y varchar2(4000) );

Table created.

sql> insert into t values ( 1, 'hello' );

1 row created.

sql> commit;

Commit complete.

sql>
sql> create table t2( x int, y clob );

Table created.

sql> exec dbms_redefinition.start_redef_table( user, 'T', 'T2', 'x, to_clob(y) y' );

PL/SQL procedure successfully completed.

sql> pause

sql> variable nerrors number
sql> 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.

sql> print nerrors

  NERRORS
 ----------
        0

sql> exec dbms_redefinition.finish_redef_table( user, 'T', 'T2' );

PL/SQL procedure successfully completed.

sql> drop table t2;

Table dropped.

sql> 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))

B. Create new_table as select * from the old (using to_lob on the column) and then add index, constrains etc the new table, then drop old, rename new to old.

C. There is an alternative solution which always preserves the order of columns.
The idea is to first convert the varchar2 column to a long and then to convert that long to a clob.

alter table t add y_copy varchar2(4000);
update t set y_copy = y;
commit;
update t set y = null;
commit;
alter table t modify y long;
alter table t modify y clob;
update t set y = y_copy;
commit;
alter table t drop column y_copy;

Archive Log Information

 Size of the archives available on disk

===================================

Select round(sum(BLOCK_SIZE * BLOCKS)/1024/1024/1024,2 ) "Size_of_arc_on_disk_GB"
From v$archived_log
Where status  like 'A';

How many days archives are there on disk and their time info

==========================================================
Select Min(completion_time) "Lowest_arc_found_on_disk",
Max(completion_Time) "Highest_arc_found_on_disk",
Round(max(completion_Time)  - min(completion_time),2) "No_of_Days_Aric_on_disk"
From v$archived_log
Where status  like 'A';

No of archives generated/day, size & freqency

===========================================
Select trunc(completion_time) "Date",
To_char(trunc(completion_time),'Day') "Day",
Count(*) "No_of_arc" ,
Round(1440/count(*),2) "Frquency_arc_created_Min",
Round(sum(block_size * blocks)/1024/1024/1024,2) "Size_disk_GB"
From v$archived_Log
Group by trunc(completion_time)
Order by trunc(completion_time) desc;