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. The method of recreating a table is not mentioned here. I just want to talk about the method of recreating a 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 usually 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 ), then, USE insert/* + append */xx select xxx to convert the data. 4. Use rename to replace the names of the two tables. 3. Notes about rebuilding the index: 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
