Summary of Oracle rebuild table (rename) Considerations

Some time ago, a DBA friend failed to run the business on the morning of the next day after completing the rename operation, and the data cannot be inserted due to restrictions and errors.

Some time ago, a DBA friend failed to run the business on the morning of the next day after completing the rename operation, and the data cannot be inserted due to restrictions and errors.

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 Reconstruction

Here we will not talk about anything else, just talk about the method of recreating the table. As follows:

1. To ensure that all table fields, field types, and lengths are identical, 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), and then use insert/* + append */xx select xxx to convert the data.

4. rename the names of the two tables.

Iii. Notes for table Reconstruction

Re-indexing: the most important thing here is whether the re-indexing name 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 below:

Index_name

| '_ Old ;'

From dba_indexes

Where a. table_owner = 'dbmon'

And a. table_name = 'dh _ T ';

Dependency object reconstruction: Generally, you can use the following method to complete

Select 'alter '| decode (type, 'package body', 'package', type) | ''| owner | '. '| name | 'compile ;'

From dba_dependencies

Where a. referenced_name = 'dh _ t'

And a. referenced_owner = 'dbmon ';

Note:

1. The reconstruction here is just A direct dependent object. 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

(Select owner, name

From dba_dependencies B

Where B. referenced_name = 'dh _ t'

And B. referenced_owner = 'dbmon ')

And a. TEXT like '% @ % ';

The following method is used to query whether a private object exists in a view ):

Select *

From dba_views

(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

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, many business data logics are 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 rebuilt tables.

Select a. table_name,

A. owner,

A. constraint_name,

A. constraint_type,

A. r_owner,

A. 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 a. constraint_type = 'R'

And a. r_constraint_name = B. constraint_name

And a. r_owner = B. owner

And B. table_name = 'fspareceivebilltime'

And B. owner = '';