0

A well-known task: convert BASICFILE LOBs to SECUREFILE LOBs.

I have a table:

    CREATE TABLE "DKR"."DKR_SEARCH_INFO"
   (    "ID" NUMBER NOT NULL ENABLE,
    "REPORT" BLOB,
    "CREATE_TS" TIMESTAMP (6),
    "OWNER" VARCHAR2(45),
     CONSTRAINT "DKR_SEARCH_INFO$PK$"
PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 LOB ("REPORT") STORE AS SECUREFILE (COMPRESS MEDIUM DEDUPLICATE)  --
  --
 TABLESPACE WSDATA;

After that I generate interim table DDL with SECUREFILE (from DBMS_METADATA.GET_DDL procedure):

    CREATE TABLE "DKR"."DKR_SEARCH_INFO_1"
   (    "ID" NUMBER NOT NULL ENABLE,
    "REPORT" BLOB,
    "CREATE_TS" TIMESTAMP (6),
    "OWNER" VARCHAR2(45),
     CONSTRAINT "DKR_SEARCH_INFO$PK$_1"
PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 LOB ("REPORT") STORE AS SECUREFILE (COMPRESS MEDIUM DEDUPLICATE)  --
  --
 TABLESPACE WSDATA;

After that I execute script with START_REDEF_TABLE procedure:

    SET serveroutput ON;
BEGIN
    FOR redef_table IN
    (SELECT DISTINCT dtc.owner, dtc.table_name, (LISTAGG(dtc.column_name || ' ' || dtc.column_name, ',') WITHIN GROUP (ORDER BY dtc.column_id)) AS COL_MAP
    FROM dba_tab_columns dtc, dba_lobs dl
    WHERE dtc.owner = dl.owner AND dtc.table_name = dl.table_name
    AND dl.owner NOT IN ('APEX_030200', 'CTXSYS', 'EXFSYS', 'FLOWS_FILES', 'MDSYS', 'OLAPSYS', 'ORDDATA', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'WMSYS', 'XDB')
    AND dl.partitioned = 'NO' AND dl.securefile = 'NO' AND dl.segment_created = 'YES' AND dtc.table_name = 'DKR_SEARCH_INFO'
    GROUP BY dtc.owner, dtc.table_name
    ORDER BY dtc.owner, dtc.table_name)
    LOOP
    BEGIN    
      DECLARE
        l_col_map VARCHAR2(4000);
          BEGIN
            l_col_map := redef_table.col_map;
            DBMS_OUTPUT.PUT_LINE(redef_table.col_map);
            DBMS_OUTPUT.PUT_LINE('Owner: ' || redef_table.owner);
            DBMS_OUTPUT.PUT_LINE('Table orig: ' || redef_table.table_name);
            DBMS_OUTPUT.PUT_LINE('Table interim: ' || redef_table.table_name || '_1');
            DBMS_OUTPUT.PUT_LINE(l_col_map);
            DBMS_REDEFINITION.START_REDEF_TABLE(redef_table.owner, redef_table.table_name, redef_table.table_name || '_1', '''' || l_col_map || '''');
          END;
    END;
                --DBMS_OUTPUT.PUT_LINE(redef_table.col_map);
    END LOOP;
  END;
/

And I receive error:

    Error starting at line : 4 in command -

Error report -
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: на  "SYS.DBMS_REDEFINITION", line 56
ORA-06512: на  "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: на  line 21
42016. 0000 -  "shape of interim table does not match specified column mapping"
*Cause:    The number of columns, or the type or the length semantics of a
           column, in the interim table did not match the specified
           column mapping.
*Action:   Ensure that the interim table matches the column mapping by
           either modifying the column mapping string or altering the
           interim table's column definition(s).
ID ID,REPORT REPORT,CREATE_TS CREATE_TS,OWNER OWNER
Owner: DKR
Table orig: DKR_SEARCH_INFO
Table interim: DKR_SEARCH_INFO_1
ID ID,REPORT REPORT,CREATE_TS CREATE_TS,OWNER OWNER

When I execute this command not from the script, everything is ok:

        DECLARE 
       l_col_map VARCHAR2(4000); 
    BEGIN 
    -- map all the columns in the interim table to the original table 
       l_col_map := 'ID ID,REPORT REPORT,CREATE_TS CREATE_TS,OWNER OWNER'; 
       DBMS_REDEFINITION.START_REDEF_TABLE('DKR', 'DKR_SEARCH_INFO_1', 'DKR_SEARCH_INFO_1', l_col_map); 
    END; 
    /

anonymous block completed

I do not understand where I made a mistake in script.. All commands executed under SYS user.

Sergey
  • 157
  • 1
  • 6
  • 19
  • Something's not making sense. Your first 2 blocks of code are the same. By your error, your mappings are wrong. Why not create the interim table with exactly the same structure as the original table? That way, you don't even have to set the column mappings value to DBMS_REDEFINITION.START_REDEF_TABLE. – Renato Afonso Jan 12 '18 at 09:18
  • Oh, sorry. It's my mistake..Of cource, the origin table and interim table have different name. I've edited DDL. I use Doc ID 728758.1 (HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES) – Sergey Jan 12 '18 at 09:58

0 Answers0