I have table with nearly five million rows in it. One of the columns is a LONG which I need to convert to a CLOB a) in the long term and b) for a search prototype using Oracle Text and a multi-column datastore.
When eventually done on production, this will have to be done with minimal impact so DBMS_REDEFINITION seems to be the best options.
I did initially try ALTER TABLE <tablename> MODIFY <columnname> CLOB” but it just took far too long. I made the mistake of not having enough free space in the tablespace and it would take nine or ten hours to come back and say it was unable to create the necessary temp extents. Presumably, it’s creating new temp extents for the clob column which on completion it will convert to permanent. Unfortunately, I was just letting this run overnight so I wasn’t proactively monitoring the tablespace.
Having said that, on the second time I ran the statement I had let set one of the datafiles in the tablespace to autoextend on. However, it only extended out to just over 30 Gig – operating system limit presumably – and then the same error occurred.
So, it made sense to do it with DBMS_REDEFINITION as that is what we’d need to do in production and I’d not used it before, so good opportunity.
The steps are well documented out there, but broadly:
1. Manually create redefinition table with datatypes and column names required. In this case, the definition of the new table was exactly in the same as the existing table with the exception of the CLOB datatype of the previously LONG column.
2. Grant the necessary grants to the table owner, namely: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE, CREATE ANY TRIGGER, CREATE ANY INDEX and EXECUTE ON DBMS_REDEFINITION.
3. Check that the target table can be redefined using DBMS_REDEFINITION.CAN_REDEF_TABLE (probably more efficient right at the start before step 1).
4. Use DBMS_REDEFINITION.START_REDEF_TABLE to define how the old table maps to the new table. A couple of points to note – firstly (and probably obviously but it took a couple of failed attempts for the penny to drop), you need to specify all the columns. So, the parameter is a comma separated string like this:
col_mapping => 'col1 col1, col2 col2, col3 col3 ...... TO_LOB(colN) colN'
If you don’t get this right then you get:
ORA-42016: shape of interim table does not match specified column mapping
Secondly, there is an options_flag parameter which indicates how the mapping mechanism is done. There seem to be two methods – by primary key or by rowid with the former seemingly preferred over the latter where possible.
5. Use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS to copy all the TRIGGERS, INDEXES, CONSTRAINTS and PRIVILEGES.
6. Call DBMS_REDEFINITION.SYNC_INTERIM_TABLE to sync up any DML activity that has happened since the start.
7. Call DBMS_REDEFINITION.FINISH_REDEF_TABLE to copy the new table to the old.
The only minor issues that I had were at the SYNC_INTERIM_TABLE step and, following on from my erroneous or incomplete actions there, at the FINISH_REDEF_TABLE step.
When, I got to the SYNC_INTERIM_TABLE step, an error was raised:
ERROR at line 1:
ORA-42009: error occurred while synchronizing the redefinition
ORA-12008: error in materialized view refresh path
ORA-04098: trigger is invalid and failed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 117
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1448
ORA-06512: at line 55
Now, I got this because I was working with a cut down version of the database with some objects missing. As a result, some of the triggers on the existing table were invalid and therefore some of the newly created triggers on the redef table were invalid. So, I thought I would just drop these triggers from the redef table.
This was fine and the SYNC_INTERIM_TABLE step passed ok.
However, at the FINISH_REDEF_TABLE step, another error:
ERROR at line 1:
ORA-04080: trigger 'TMP$$_NEWSDELETE0' does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 76
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1376
ORA-06512: at line 2
It didn’t exist because I had dropped it but obviously something somewhere needed it.
I tried using DBMS_REDEFINITION.UNREGISTER_DEPENDENT_OBJECT but although this call worked, the error above persisted.
I could see in the DBA_REDEFINITION_OBJECTS that these non-existent objects were still expected.
But I couldn’t find any info on Metalink or Google about this.
So, I recreated the triggers with the expected temporary names and validated them by having a NULL; trigger body.
Bish, bash, bosh, FINISH_REDEF_TABLE done and dusted, process finished and all that was left was to manually drop the redef table created at step 1.
In summary, it’s a pretty neat bit of functionality, particularly for a high availability system and I know we will use this a lot once and when we migrate from 8i to 10g.