Oracle Migrating Table Columns from Longs to CLOB

The purpose of LOB (Large Object) Data Types is to store large data. Ex. document (txt, word, excel, xml), pictures, video, audio and so on. This type of data was previously held in data types such as long, raw, long raw, and nowadays large objects are held in data types such as BLOB, CLOB, NCLOP. Data types such as long, raw and long raw are mostly used for old support.
BLOB: Stores data in binary format. Stores up to 128 TB of data. Files such as pictures and videos can be stored in this data type.

CLOB: Stores data as characters. Stores up to 128 TB of data. We can store the data in TXT files in the CLOB data type.
With Oracle 11g, Oracle Securefile structure has been announced to store the data types mentioned above. With Securefile, features such as compression in lobe areas, deduplicate encryption, cachinhg, and logging mechanisms have been introduced.

We’re starting. How to Convert Oracle Longs to CLOB;

• Temporary table is created. Long field is made of clob.

create table LOGTABLE2
(
  logid NUMBER not null,
  register_no NUMBER not null,
  datetime VARCHAR2 (20) not null,
  method VARCHAR2 (100),
  outgoing CLOB,
  fromlog VARCHAR2 (4000)
COMPRESS FOR ALL OPERATIONS;

• Since the outlog field is clob, it is moved to another tablespace and compressed. The following command is run to find the field.

SELECT ‘alter table’
       || dba_lobs.own is
       || ‘.’
       || table_Name
       || ‘move lob (‘
       || column_name
       || ‘) store as SECUREFILE’
       || dba_lobs.segment_n Game
       || ‘(tablespace INDX COMPRESS HIGH);’
  FROM dba_segments, dba_lobs
 WHERE dba_lobs.segment_name = dba_segments.segment_name
       AND dba_lobs.table_name = ‘LOGTABLE2’

• The output is run sql again and moved to another tablespace.

alter table BUGRA.LOGTABLE2 move lob (GIDENLOG) store as SECUREFILE LOGTABLE_LOB0000122799C00005 $$ (tablespace INDX COMPRESS HIGH);

• The area moves back to its original location.

alter table BUGRA.LOGTABLE2 move lob (GIDENLOG) store as SECUREFILE LOGTABLE_LOB0000122799C00005 $$ (tablespace USERS);

• Table redefinition command is started, table fields are defined in col_mapping.

declare
 col_mapping varchar2 (1000);
BEGIN

• map all the columns in the original table

 col_mapping: =
               ‘LOGID LOGID,’ ||
               ‘REGISTER_NO REGISTER_NO,’ ||
               ‘DATETIME DATETIME,’ ||
‘METOD METOD,’ ||
               ‘to_lob (GIDENLOG) GIDENLOG,’ ||
‘GELENLOG GELENLOG’;

dbms_redefinition.start_redef_table (‘BUGRA’, ‘LOGTABLE’, ‘LOGTABLE2’, col_mapping);
END;
/

Copying table dependencies

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (‘BUGRA’, ‘LOGTABLE’, ‘LOGTABLE2’,
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

• check for errors

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

• table is synchronized for the last time

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE (‘BUGRA’, ‘LOGTABLE’, ‘LOGTABLE2’);
END;
/

• process is terminated and new information is imported into the old table

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE (‘BUGRA’, ‘LOGTABLE’, ‘LOGTABLE2’);
END;
/

• temporary table is dropped

drop table BUGRA.LOGTABLE2 cascade constraints purge;

• changing area in old table is checked

desc BUGRA.LOGTABLE;

• If the operation is interrupted due to any error, it is broken by abortion.

BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE (‘BUGRA’, ‘LOGTABLE’, ‘LOGTABLE2’);
END;
/