I. Overview
Some time ago, there was a DBA friend after the Rebuild 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 have not yet been defined in this rebuild, causing the tables to violate data integrity constraints when inserting new data, resulting in data not being 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, due to the way ctas+rename works together. Very useful and efficient.
Very many DBA friends should also have used rename to rebuild the table. And after the completion of the reconstruction is all normal, not caused problems. However , what I want to say is that after using rename to rebuild the table. What do you really know about the finished work?
This article is mainly inductive when we use the Rename method to reconstruct the table. What needs to be done, assuming you are not very clear. Be sure to read this article carefully. At the same time in the future reconstruction of the table work corrected. 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.
For example, the following
1, in order to ensure that all table fields, field types, the length of 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 PL/SQL developer similar to this tool to view table definition statements
3. Build another table of type _old (as defined by the table above) and then use Insert/*+ Append */xx Select XXX 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 one that was once rebuilt, assuming the same needs. You must first rename the name of the index you are currently using, or you will create an error that the index name already exists, such as the following: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: generally can be used such as the following way to completeSelect ' 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 (such as View1 dependent on a table, View2 dependent view1), the search method and the above is almost the same2. Suppose that there are some private objects in these dependent objects (such as Dblink, etc.). When we compile with the DBA user again, there will be a compile error for such an object. Must be compiled successfully with the owner of the corresponding object. (You can also use the new proxy permissions after 10g to complete such tasks!) )for PL/SQL code (packages, functions, procedures, and so on), is there a way to find a private object, such as the following: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 '%@% ';
A lookup method for the presence of a private object in the view, such as the following (because it is a long type). 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 rebuilds: ability to use the following statements, for exampleselect ' 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, much of today's business data logic is implemented at the application level. Therefore, the foreign keys on the table may be very small, so. Causes a lot of DBAs to forget to check and rebuild this part, causing the business to fail, the most important fault case in this chapter is because the foreign key is not rebuilt, so we must be vigilant.
The ability to use the following statements, for example, 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 views are not problematic, the refresh will be self-compiling, but this may affect the optimization of its selection run plan. Therefore, it is recommended to compile these failed materialized views manually, such as the following Alter materialized VIEW DH_T_MV compile; note. In fact, this step is already included in the Dependent Object Reconstruction section. It is because this dependent object is very important, no matter what the accident
materialized View logs: materialized view logs are prepared for high-speed refreshes. And it cannot be found from the dba_dependencies dependency table. However, for this object. We must remain cautious and awed, because the materialized view log object exists on the table, the table cannot be completed rename (in a change of night, everything else OK, suddenly encountered a problem like this.) Also need to find development confirmation. is very passive and the entire change is likely to be canceled due to this inability to confirm). Will directly error. The materialized view Log object methods on the lookup table are as follows:
Select master,log_table From user_mview_logs awhere master in (' dh_t ');
remark:
- We may also need to focus on table field types, which are the LOB, long fields we need to consider to rebuild the table.
- There is the rebuilding of the table is that we may use the parallel+nologging mode to heighten the speed, it is important to remember that after the completion of the reconstruction of these properties back. (once encountered a case, did not change the parallel property back, resulting in a parallel run plan, finally resulting in a very fast depletion of resources, cpu100%)
- Some other synchronization mechanisms. Assume that the synchronization relies on ROWID because the table is ROWID by the Rebuild table. can cause real-time synchronization failures, which we need to consider
- At last. After the work is done, it is a good idea to check the validity of all objects.
(It is recommended that you save the snapshot before rebuilding.) Compare with previous snapshot after rebuilding)
- These are some of the objects we use most often, and other objects that are very rarely used are not outlined here.
Oracle rebuild Table (rename) Considerations Summary
