Summary of Oracle rebuild table (rename) Considerations

I. Overview
Some time ago, a DBA friend failed to run the business on the morning of the next day after he finished the rename operation and encountered the restriction and error that the data could not be inserted, the error is that after the table is rebuilt using the rename method, the foreign key constraints referenced in the table point to the new table that has not been redefined. As a result, when new data is inserted to these tables, data cannot be inserted due to violation of data integrity constraints. It may take more than one hour to influence the business. It is a bloody lesson. Using the rename method to recreate a table is a frequently used method in daily DBA maintenance work, because the CTAS + rename combination method is very practical and efficient. Many DBA friends should also use the rename method to recreate the table, and after the reconstruction is completed, everything will be normal, without causing any problems. However, what I want to say is, after using rename to recreate a table, do you really know which scanning tasks need to be completed ?? This article mainly aims to summarize the work that needs to be done after we use the rename method to reconstruct the table. If you are not very clear, you must read this article carefully, at the same time, rectify the problem in the future table reconstruction work. Otherwise, the problem will come to you one day sooner or later!
Ii. Table ReconstructionHere we will not talk about anything else, just talk about the method of recreating the table. 1. To ensure that all table fields, field types, and lengths are the same, we do not recommend using CTAS to recreate the table. 2. I generally use one of the following two methods to extract TABLE definitions: select dbms_metadata.get_ddl ('table', upper ('& I _table_name '), upper ('& I _owner') from dual;
Use tools like PL/SQL developer to view table definition statements.
3. Create a New _ old table (defined according to the extracted table above ), use insert/* + append */xx select xxx to convert the data. 4. Use rename to replace the names of the two tables.

Iii. Notes for table ReconstructionIndex reconstruction:The most important thing here is whether the index name after reconstruction must be the same as the previous one. If you need the same name, you must rename the name of the currently used index first, otherwise, an index name error occurs during creation, as shown in the following figure: select 'alter Index' | owner | '. '| index_name | 'rename to' | substr (index_name, 1, 26) | '_ old;' from dba_indexes a where. table_owner = 'dbmon' and. table_name = 'dh _ T ';
Dependency object reconstruction:Select 'alter '| decode (type, 'package body', 'package', type) | ''| owner | '. '| name | 'compile;' from dba_dependencies a where. referenced_name = 'dh _ t' and. referenced_owner = 'dbmon ';
Note: 1. The reconstruction here only involves direct dependent objects. You must consider those indirectly dependent objects (for example, view1 depends on table A and view2 depends on view1 ), the search method is similar to the above 2. If these dependent objects have some private objects (such as dblink), we will encounter a compilation error when re-compiling with the DBA user. For such objects, the object must belong to the corresponding object before compilation is successful. (You can also use a new proxy permission after 10 Gb to complete such tasks !) For PL/SQL code (package, function, process, etc.), whether there is a private object search method, as follows: select * from dba_source a where (. owner,. name) in (select owner, name from dba_dependencies B where B. referenced_name = 'dh _ t' and B. referenced_owner = 'dbmon') and. TEXT like '% @ % ';
The following method is used to query whether a private object exists in a view. (because it is of the long type, you must check it one by one): select * from dba_views a where (. owner,. 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 reconstruction: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 reconstruction:For foreign keys, most of the current business data logic is implemented at the application layer, so there may be very few foreign keys on the table. Therefore, as a result, many DBAs forget to check and rebuild this part, which leads to business problems. The fault cases mentioned in this chapter are due to the absence of Rebuild of Foreign keys, therefore, we must be vigilant. You can use the following statement to check which tables reference the rebuild table select. table_name,. owner,. constraint_name,. constraint_type,. r_owner,. r_constraint_name, -- Name of the constraint referenced by the foreign key B. table_name -- Name of the table referenced by the foreign key from dba_constraints a, dba_constraints B where. constraint_type = 'r' and. r_constraint_name = B. constraint_name and. r_owner = B. owner and B. table_name = 'fspareceivebilltime' and B. owner = '';
Materialized View:Another very important dependent object is the materialized view. In general, after the rename TABLE, the materialized view will not be faulty and will be automatically compiled upon refresh, however, this may affect the optimization of the selected execution plan. Therefore, we recommend that you manually compile these invalid MATERIALIZED views, as shown in alter materialized view DH_T_MV compile; note, in fact, this step has already been included in the rebuilding part of the dependent object. It is taken out separately because this dependent object is very important and should not be surprising.
Materialized view log:Materialized View logs are prepared for quick refresh and cannot be found in the dependency table dba_dependencies. However, we must be cautious and in awe of this object, if the table contains a materialized view log object, this table cannot be rename (on a change night, everything else is okay, and suddenly this problem occurs, you have to find the development confirmation, which is very passive. The entire change is likely to be canceled because it cannot be confirmed). An error is reported directly. The method for searching the log object of the materialized view on the table is as follows:
Select master, log_table from user_mview_logs awhere master in ('dh _ t ');

Note: We may also need to pay attention to the table field types. We need to consider the LOB and long fields for recreating the table.
We may also use the parallel + nologging mode to speed up table reconstruction. Remember to modify these attributes after reconstruction. (I used to encounter a case where the parallel attribute was not changed back, resulting in parallel Execution Plan Selection, resulting in rapid resource depletion, CPU100 %)
There are also some synchronization mechanisms. If the synchronization depends on rowid, because the reconstruction of the table rowid will cause this table, real-time synchronization may fail. These are the final considerations. after the work is completed, checking the validity of all objects is a good solution. (We recommend that you save the snapshot before the reconstruction and compare it with the previous snapshot.) All of the above are some of the most common objects. Other rarely used objects are not described here.