Modify Oracle database field types to handle ORA-01439 errors

When you modify a field type for a table that already has data, Oracle prompts: ORA-01439: To change the data type, the column to be modified must be empty.   You can create a new table, fill in the original table data, and then rename it, or create a temporary field, replace the data, and then delete it. Test environment:
drop table foo;
CREATE TABLE foo (col_name varchar2 (5));
INSERT into Foo values (' 1 ');
INSERT into Foo values (' 12 ');
INSERT into Foo values (' 33445 ');
Commit
CREATE INDEX idx1 on foo (col_name);
SELECT * from foo;
desc Foo Workaround 1. Create a new table
Creates a new table based on the current table structure, modifies the new field, fills in the original data, deletes the old table, and rename the new table to the old table name
CREATE table new as SELECT * from foo where 1=2;
ALTER TABLE new Modify (col_name number (5));
INSERT INTO new SELECT * from Foo;
drop table foo;
Rename New to Foo;   If the original table has indexes, triggers, foreign keys, and so on need to be new. 2. Use CTAs to convert
Oracle's ability to create tables using queries creates a new table containing the original data, as follows:
CREATE TABLE New
As
Select [Other columns],
To_number (Char_col) col_name
from Foo; Then drop the original table, and the new table is renamed to the original table:
drop table foo;
Rename New to Foo;   As with Method 1, be aware of creating a new related object. 3. Create temporary field substitution
Create a new temporary field, back up the contents of the field you want to modify to a temporary field, empty the original field, then modify the type, then copy the contents of the temporary field to the modified field, and then delete the temporary field ALTER TABLE foo Add (tmp_col number (5));
Update foo Set tmp_col = To_number (col_name);
Update foo SET col_name = null; --Be careful here, the original data is all gone, we must ensure that the last step of the correct implementation

ALTER TABLE Foo Modify (col_name number (5));
Update foo Set col_name = Tmp_col;
ALTER TABLE foo drop column tmp_col;
Oracle Rebuild Table (rename) Summary of considerations I. Overview
Some time ago, a DBA friend after completing the Rebuild table (rename) work, the next morning business is not operational, the data can not be inserted restrictions and errors, later analysis found that the reason for the error is the use of rename way to rebuild the table, Other foreign KEY constraints referencing this table point to a new table that has not been redefined to this rebuild, causing the tables to violate data integrity constraints when inserting new data, resulting in the data not being inserted properly. Affected the business about 1 hours, really a bloody lesson ah. Rebuilding tables using rename is one of the most common methods used in our day-to-day DBA maintenance work, because ctas+rename is very practical and efficient. Many DBA friends should also be using the rename way to rebuild the table, and after the completion of the reconstruction are all normal, did not cause problems. But what I'm trying to say is, after using the Rename rebuild table, what do you really need to do to finish the work? This article is mainly summed up when we use the rename way to rebuild the table, what needs to be done, if you are not very clear, be sure to read this article, but also in the future reconstruction of the work of the correction, otherwise, sooner or later, the problem will come to your side.
second, the way to reconstruct the tableLet's not talk about the other, just the way to rebuild the table. As follows 1, to ensure that all table fields, field types, and lengths are exactly the same, I generally do not recommend using the CTAs method to recreate the table. 2. Generally I use one of the following two methods to extract the definition of a table Select DBMS_METADATA.GET_DDL (' table ', Upper (' &i_table_name '), Upper (' &i_owner ') ) from dual;
Use a tool like this Pl/sql developer to view table definition statements
3, a new _old type of table (based on the above extracted table definition), and then use the Insert/*+ append/xx Select XXX Way to complete the conversion of the data 4, the last use rename way to change the names of the two tables

iii. Reconstruction Table Considerations Index Rebuild:The key here is whether the name of the index must be the same as before, and if so, you must first rename the index name that is currently in use, otherwise the error that the index name already exists will appear when you create it, as follows: SELECT ' Alter index ' | | Owner | | '.' || index_name | | ' Rename to ' | | SUBSTR (index_name, 1, 26) | | ' _old ' from dba_indexes a where A.table_owner = ' Dbmon ' and a.table_name = ' dh_t ';
Dependent object rebuild:You can generally complete select ' Alter ' by using the following method: | Decode (type, ' PACKAGE body ', ' PACKAGE ', type) | | '|| owner| | '. ' | | name| | ' Compile ' from dba_dependencies a where a.referenced_name = ' dh_t ' and A.referenced_owner = ' Dbmon ';
Note: 1. The only objects that are directly dependent here are those that are indirectly dependent (for example, View1 relies on a table, VIEW2 relies on view1), the lookup method is almost 2 above, if there are some private objects (such as Dblink, etc.) in these dependencies, Recompiling with a DBA user is a compilation error, and for such an object, it must be compiled to be successful by the owner of the corresponding object. (You can also use the new proxy permissions that appear later in the 10g to accomplish this type of task.) For Pl/sql code (packages, functions, procedures, and so on), is there a way to find private objects, as follows: SELECT * from Dba_source a WHERE (A.owner, A.name) in (select owner, name from DBA _dependencies b where b.referenced_name = ' dh_t ' and B.referenced_owner = ' Dbmon ') and a.text like '%@% ';
For a lookup method for a private object in the view, as follows (because it is a long type, you must have one view): SELECT * from Dba_views a WHERE (A.owner, A.view_name) in (select owner, name From dba_dependencies b where b.referenced_name = ' dh_t ' and B.referenced_owner = ' Dbmon ' and b.type = ' VIEW '
Permission rebuild:You can use the following statement to select ' Grant ' | | Privilege | | ' On ' | | Owner | | '.' || table_name | | ' To ' | | Grantee | | '; ' from DBA_TAB_PRIVS WHERE table_name = UPPER (' &i_table_name ') and owner = Upper (' &i_owner ');
foreign key rebuild:For foreign keys, many of the current business data logic is implemented at the application level, as a result, the foreign keys on the table may be very small, so that many DBAs forget that they need to check and rebuild this section, causing business problems, and the first failure case of this chapter is caused by not rebuilding the foreign key. So we must be vigilant. You can use the following statement to see which tables refer to the Rebuild table select A.table_name, A.owner, A.constraint_name, A.constraint_type, A.r_owner, A.r_constraint_ Name,--the constraint name referenced by the foreign key b.table_name--the table name referenced by the foreign key from Dba_constraints A, dba_constraints b where a.constraint_type = ' R ' and a.r_c Onstraint_name = b.constraint_name and A.r_owner = B.owner and b.table_name = ' fspareceivebilltime ' and b.owner= ';
materialized Views:Another very important dependent object is the materialized view, in general, materialized views are not problematic after rename, and are automatically compiled when refreshed again, but this may affect the optimization of their choice execution plan, so it is recommended to manually compile these failed materialized views, as follows Alter materialized VIEW DH_T_MV compile; Note that this step is already included in the dependency reconstruction section, and it is only because the dependent object is so important that there is no accident.
materialized view log:Materialized view logs are prepared for quick refreshes and cannot be found from the dba_dependencies dependency table, but for this object, we must be cautious and awed, because if a materialized view log object exists on the table, Then this table cannot be completed rename (on a change of night, everything else OK, suddenly encountered a problem like this, but also to find development confirmation, is very passive, the entire change is likely because this cannot be confirmed and canceled, will directly error, lookup table materialized view Log object method is as follows:
Select Master,log_table from User_mview_logs a Where master (' dh_t ');

Note: We may also need to focus on the table field type, and those lob, long fields are our rebuilding table to consider
There is also the rebuilding of the table is that we may all use the parallel+nologging mode to speed up, be sure to remember that after the rebuild is done to modify these attributes back. (previously encountered a case, did not change the parallel property back, resulting in the execution plan selection parallelism, resulting in the resource quickly depleted, cpu100%)
There are also some synchronization mechanisms, if the synchronization depends on ROWID, because the table ROWID rebuild table, may cause real-time synchronization failure, these are the last thing we need to consider, after the completion of the work, check the effectiveness of all objects is a good solution. (It is recommended that you save the snapshot before rebuilding and compare it with the previous snapshot) it's all about some of our most common objects, some other objects that are rarely used.