Efficient Method to Alter Column.
Hi I am using 11.2.0.3.0 version of oracle.
Now we are supposed to apply column level TDE(Transparent data encryption) to some of our table in database. it involves 4 big tables out of which 3 tables having size ~30GB(one is partitioned table) and another one ~800GB(Not partitioned) Now the concern is, what will be the efficient/safest way to apply TDE on columns, below are the two options with us. (NOTE - We do have 8 hrs of downtime window during DB maintenance but looking at the size of the table, i suspect it might cross.)
i think , below are option available with me, i have some questions as mentioned below, please suggest?
Copy Column:(Tested on local for 30GB table)
-----------------------------------------------------------------
1.Create new column as TDE enabled.
2.Copy data from existing column to new one through ROWID reference, may be using 'FOR ALL' .
3. Mark the existing column Unused, and rename the new column.
complexity:
1.Less complex compare to Online Redefinition as because only columnlevel changes will be done, other dependent objects like 'Index/Constraints/Triggers/grants' will not be touched.
2.It will take extra space used for one EXTRA column. + Some UNDO.
3.it took 2 hr 20 min for 30GBtable having 80000000 record.
Direct 'Alter':(Tested on local for 30GB table)
---------------------------------------------------------------------
1.Simply execute 'ALTER' script for altering the column.
E.g. ALTER table tab add (c1 varchar2(3932 CHAR) ENCRYPT);
Complexity:
1.Least complex as one line script required for ALTER.
2.it took 3 hr for 30GBtable having 80000000 record.
3.As I tested it generates ~10 GB of undo for a 30GB table.
Question;
1.Why its taking more time than 'Copy Column' method, how its different from 'Alter' logically?
2.Whether it will take any extra space?
Table redefinition:(not tested)
-----------------------------------------
1.Will Create Interim table with column as 'TDE' Enabled.
2. Apply online redefinition using 'START_REDEF_TABLE'.
3. We need to copy all dependent objects like 'Index/Constraints/Triggers/Grants' etc.
4. We need to Sync both the 'Base table' and Interim table.
5. Finish redefinition
Complexity:
1. It will involve in many steps and full table transition in terms of 'all Indexes/all Constraints/all grants/all Triggers' and Data.
2. All procedure are predefined one. And in case of any error in these procedures during transaction hour, it might cause issue.
3.Need additional storage equal to that of the base table.
Question:
1.Whether this method will be slowest of all three, if yes, then why?
2. Our database is highly concurrent during transaction hours, so whether online redefinition will slow down the DMLS on the base table?
