Considerations for Oracle Rebuild tables

I. Overview

Some time ago, a DBA friend after completing the Rebuild table (rename) work, the next morning business is not operational, the data can not be inserted restrictions and errors, later analysis found that the reason for the error is the use of rename way to rebuild the table, Other foreign KEY constraints referencing this table point to a new table that has not been redefined to this rebuild, causing the tables to violate data integrity constraints when inserting new data, resulting in the data not being inserted properly. Affected the business about 1 hours, really a bloody lesson ah.

Rebuilding tables using rename is one of the most common methods used in our day-to-day DBA maintenance work, because ctas+rename is very practical and efficient. Many DBA friends should also be using the rename way to rebuild the table, and after the completion of the reconstruction are all normal, did not cause problems. But what I want to say is, after using the Rename rebuild table, what do you really need to do to finish the 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, be sure to read this article carefully, at the same time in the future reconstruction of the work of the correction over, otherwise, the problem will one day come to your side!

Second, the way to reconstruct the table

Let's not talk about the other, just the way to rebuild the table. As follows

1. To ensure that all table fields, field types, and lengths are exactly the same, I generally do not recommend using the CTAs method to recreate the table.

2. Generally I use one of the following two methods to extract the definition of a table

Select Dbms_metadata.get_ddl (' TABLE ', Upper (' &i_table_name '), Upper (' &i_owner ')) from dual;

Use a tool like this Pl/sql developer to view table definition statements

3. Re-create a _old type table (defined according to the extracted table above), and then use the Insert/*+ append/xx Select XXX to complete the conversion of the data

4, the last use rename way to switch the names of these two tables

Iii. Considerations for the Reconstruction table

Index rebuild: The key here is whether the name of the index must be the same as before, and if so, you must first rename the name of the index you are currently using, otherwise the error that the index name already exists will appear when you create the following:

Select ' Alter index ' | | Owner | | '.' || index_name | | ' Rename to ' | |

SUBSTR (index_name, 1, 26) | | ' _old; '

From Dba_indexes A

where A.table_owner = ' Dbmon '

and a.table_name = ' dh_t ';

Dependent object rebuilding: You can generally do this by using the following methods

Select ' 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 ';

Attention:

1, the reconstruction here is only directly dependent on the object, must consider those indirectly dependent objects (such as View1 dependent on a table, view2 rely on View1), lookup method and above almost

2. If there are some private objects (such as Dblink, etc.) in these dependent objects, we will compile errors with the DBA user, and for such objects, it must be the owner of the corresponding object to compile successfully. (You can also use the new agent permissions that appear after 10g to accomplish this kind of task!) ）

For Pl/sql code (packages, functions, procedures, and so on), is there a way to find private objects, 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 '%@% ';

For a lookup method for a private object in the view, as follows (because it is a long type, you 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 rebuild: 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 ');