Shulou(Shulou.com)06/01 Report--

Http://www.cnblogs.com/ljbguanli/p/6752029.html

I. Overview

Some time ago, after a DBA friend finished rebuilding the table (rename), the business could not execute normally the next morning, and there were restrictions and errors that the data could not be inserted. Later, the analysis found that the reason for the error was that after rebuilding the table using rename, other foreign key constraints referencing the table were not defined in the new table again, thus causing these tables to insert new data. Data integrity constraints are violated, resulting in data cannot be inserted properly.

It has affected the business for more than an hour, which is really a bloody lesson.

Rebuilding tables using rename is a method that is often used in our daily DBA maintenance work, because of the cooperation of CTAS+rename. Very useful and efficient.

A lot of DBA friends should also have used rename to rebuild tables. And after the reconstruction is completed, everything is normal and has not caused any problems. However, what I want to say is that after using rename to rebuild the table. Do you really know what kind of clean-up work needs to be completed in detail?

The main purpose of this article is to summarize when we use rename to rebuild the table. Assuming that you are not very clear about what kind of clean-up work needs to be done. Be sure to read this article carefully. At the same time, it will be corrected in the future work of rebuilding the table. otherwise. The problem will come to you sooner or later!

Second, the way to rebuild the table, let's not talk about anything else here, just talk about the method of rebuilding the table, such as the following

1. In order to ensure that all table fields, field types, and lengths are exactly the same, I generally do not recommend using CTAS to rebuild the table. 2. Generally speaking, I use one of the following two methods to extract the definition of the table select dbms_metadata.get_ddl ('TABLE',upper (' & itable table name'), upper ('& iroomowner') from dual

Use a tool like PL/SQL developer to view table definition statements

3. Create another table of type _ old (according to the table definition extracted above), then use insert / * + append * / xx select xxx to complete the data conversion. 4. Finally, use rename to change the names of the two tables.

Third, rebuild the table Note Index Reconstruction: the most important thing here is whether the name of the index after reconstruction must be the same as before, assuming that it must be the same. The currently used index name must be rename first, otherwise there will be an error that already exists when the index name is created.

For example, the following queries the index of the current table and renames it sql:

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 reconstruction: generally, you can use the following methods to finish 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'' |

Note: 1, only directly dependent objects are rebuilt here, and those indirectly dependent objects (such as view1 dependent on table A, view2 dependent on view1) must be considered, and the search method is almost the same as above. 2, it is assumed that there are some private objects (such as dblink, etc.) in these dependent objects. When we compile with DBA users, there will be compilation errors, for such objects. It must be the owner of the corresponding object to compile successfully. (you can also use the new agent permissions after 10g to complete this kind of task! )

Whether there are methods to find private objects for PL/SQL code (packages, functions, procedures, etc.), for example: 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 whether there is a private object in the view, such as the following (because it is a long type. Must be checked one by one): 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 reconstruction: you can use the following statement, for example, select 'grant' | | PRIVILEGE | |'on'| | owner | |'. | | table_name | |'to'| | grantee | |; 'from dba_tab_privs where table_name = upper (' & iroomtableroomname') and owner = upper ('& iroomowner')

Foreign key reconstruction: for foreign keys, most of today's business data logic is implemented at the application layer. So there may be very few foreign keys on the table, so. As a result, many DBA forget to check and rebuild this part, resulting in business failures. The failure cases mentioned at the beginning of this chapter are caused by the absence of rebuilding foreign keys, so we must be vigilant.

You can use the following statement 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,-- the constraint name b.table_name referenced by the foreign key-- the table name 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.ownerroom.'

Materialized view: another very important dependent object is the materialized view, generally speaking, after the rename table, the materialized view will not have a problem, and it will be compiled actively when it is refreshed again, but this may affect the optimization of its choice of running plan. Therefore, it is recommended to compile these invalid materialized views directly by hand, as follows: alter MATERIALIZED VIEW DH_T_MV compile

Remarks. In fact, this step is already included in the dependent object reconstruction section. Take it out alone because this dependent object is so important that there can be no surprises.

Materialized view log: materialized view log is prepared for high-speed refresh. And can not be found from the dependent table of dba_dependencies. However, for this object. We must be cautious and in awe, because assuming that there is a materialized view log object on the table, then the table cannot finish rename (on a night of changes, everything else OK, suddenly encountered such a problem. We still need to check with the developer. Is very passive, and the entire change is very likely to be cancelled because this cannot be confirmed. Will report the wrong message directly. The materialized view log object method on the lookup table is for example:

Select master,log_table from user_mview_logs a where master in ('DH_T')

Note: we may also need to pay attention to the table field types. Those LOB and long fields are the ones we need to consider when we rebuild the table.

In addition, we may all use parallel+nologging mode to speed up the reconstruction of the table, so be sure to change these properties back after the rebuild is complete. (there was a case in which the parallel attribute was not changed back, which led to the running plan choosing parallelism, which finally caused the resources to be exhausted very quickly, CPU100%)

Other synchronization mechanisms. Suppose synchronization depends on rowid, because rebuilding the table rowid will do so. Real-time synchronization may fail, which is the last thing we need to consider. After the work is done, it is a good idea to check the validity of all the objects. (it is recommended that you save the snapshot before rebuilding. Compared with the previous snapshot after reconstruction) the above are some of the objects that we use most frequently, but some other objects that are very rarely used are not summarized here.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.