I. Overview
Some time ago, there was a DBA friend after the completion of the reconstruction table (rename) work, the next morning the business is not working properly, the data can not be inserted restrictions and errors, later analysis found that the cause of the error is to use the rename way to rebuild the table, Other foreign KEY constraints referencing this table point to new tables that are not redefined to this rebuild, causing the tables to violate data integrity constraints when inserting new data, causing data to not be inserted properly. The impact of the business about 1 hours, is really a bloody lesson ah. Rebuilding tables using the Rename method is often used in our daily DBA maintenance work, because ctas+rename is a very practical and efficient way to do this. Many DBA friends should also have used the Rename method to rebuild the table, and after the completion of the reconstruction is all normal, not causing problems. But what I want to say is, after using rename to rebuild the table, what do you really know about the finishing 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, you must read this article, and in the future reconstruction table work to rectify, otherwise, sooner or later, the problem will come to your side!
ii. ways of rebuilding tables Let's just talk about the method of rebuilding the table. as follows 1, in order to ensure that all table fields, field type, length is exactly the same, I generally do not recommend the use of CTAs method to rebuild the table. 2, generally I use one of the following two methods to extract the definition of the table
- Select Dbms_metadata.get_ddl (' TABLE ', Upper (' &i_table_name '), Upper (' &i_owner ')) from dual;
- Use the PL/SQL developer tool like this to view table definition statements
3. Re-create a new table of type _old (as defined by the table above), and then use the Insert/*+ append */xx Select XXX method to complete the conversion of the data4, the last use of the rename way to the name of the two tables
Iii. Considerations for Reconstruction Tables
Index rebuild: The key here is whether the name of the index must be the same as the previous one, and if so, the index name that is currently used must be rename first, otherwise the index name already exists when the error is created, as follows:select ' alter index ' | | owner | | | | index_name | | ' rename to ' | | substr(index_name, 1, ) | | ' _old; ' From dba_indexes a where a.table_owner = ' DBMON ' and a.table_name = ' dh_t ';
Dependent Object reconstruction: You can generally do this by using the following methodSelect ' alter '| | 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 reconstruction is only directly dependent on the object, must consider those indirectly dependent on the object (for example, view1 dependent on a table, View2 dependent view1), the search method and the above similar2, if there are some private objects in these dependent objects (such as Dblink, etc.), we use the DBA user recompile is a compile error, for such objects, must be the corresponding object's owner to compile successfully. (You can also use the new proxy permissions that appear after 10g to complete this kind of task!) )for PL/SQL code (packages, functions, procedures, and so on), is there a way to find a private object, 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 '%@% ';
The lookup method for the existence of a private object in the view, as follows (because it is a long type, one must be viewed):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 rebuilds: You can use the following statementselect ' Grant ' | | PRIVILEGE | | ' On ' | | Owner | | '. ' | | table_name | |' to ' | | | grantee | | '; 'From Dba_tab_privsWHERE table_name = UPPER (' &i_table_name ')and owner = Upper (' &i_owner ');
foreign Key reconstruction: for foreign keys, many of the business data logic is now implemented at the application level, so there may be very few foreign keys on the table, so many DBAs forget to check and rebuild this part, causing business problems, The first failure case in this chapter is caused by the absence of a foreign key reconstruction, so we must be vigilant. You can use the following statements to see which tables reference the Rebuild table Select A.table_name,A.owner,A.constraint_name,A.constraint_type,A.r_owner,a.r_constraint_name,--constraint name referenced by foreign keyB.table_name--Name of the table referenced by the foreign keyFrom dba_constraints A, dba_constraints bwhere a.constraint_type = ' R 'and a.r_constraint_name = B.constraint_nameand A.r_owner = B.ownerand b.table_name = ' Fspareceivebilltime 'and b.owner= ';
materialized view: Another very important dependent object is materialized view, in general, after the Rename table, materialized view is not problematic, the refresh will be automatically compiled, but this may affect the optimization of its selection execution plan, therefore, It is recommended to compile these failed materialized views manually, as follows Alter materialized VIEW DH_T_MV compile; Note that this step is already included in the dependency reconstruction section, which is taken out separately because the dependent object is very important and cannot be accidentally
materialized View logs: materialized view logs are prepared for quick refreshes and cannot be found from the dba_dependencies dependency table, but for this object we must remain cautious and awe, Because if there is materialized view Log object on the table, then this table can not complete rename (in a change of night, everything else OK, suddenly encountered a problem, but also to find development confirmation, is very passive, the whole change is likely because this can not be confirmed and canceled), will directly error, The materialized view log objects on the lookup table are as follows:
Select master,log_table From user_mview_logs awhere master in (' dh_t ');
remark:
- We might also need to focus on table field types, those lobs, The long field is the one we need to consider when rebuilding a table
-
- There are some synchronization mechanisms, if the synchronization depends on the ROWID, because the Rebuild table rowid the table, may cause the real-time synchronization failure, these are we need to consider the
- Finally, after the work is done, it's a good idea to check the validity of all objects. (It is recommended to save the snapshot before rebuilding, compared to the previous snapshot after rebuilding)
-
