Oracle数据库管理员职责（四）

核心提示： 5. nonuPK.sqlnonuPK.sql To find tables with nonunique PK indexes. Requires that PK names follow a naming convention. An alternative query follows

5. nonuPK.sql

nonuPK.sql

To find tables with nonunique PK indexes. Requires that PK names

follow a naming convention. An alternative query follows that

does not have this requirement, but runs more slowly.　　　　　　

11/2/98　　

　　　SELECTindex_name,table_name,uniqueness
　　FROMall_indexes
　　WHEREindex_namelike'&PKNAME%'
　　ANDwner='&OWNER'ANDuniqueness='NONUNIQUE'
　　SELECTc.constraint_name,i.tablespace_name,i.uniqueness
　　FROMall_constraintsc,all_indexesi
　　WHEREc.owner=UPPER('&OWNER')ANDi.uniqueness='NONUNIQUE'
　　ANDc.constraint_type='P'ANDi.index_name=c.constraint_name

6. mkrebuild_idx.sql

mkrebuild_idx.sql

Rebuild indexes to have correct storage parameters

11/2/98　

　　　SELECT'alterindex'||index_name||'rebuild'
　　,'tablespaceINDEXESstorage'
　　||'(initial256Knext256Kpctincrease0);'
　　FROMall_indexes
　　WHERE(tablespace_name!='INDEXES'
　　ORnext_extent!=(256*1024)
　　)
　　ANDwner='&OWNER'
　　/

7. datatype.sql

datatype.sql

To check datatype consistency between two environments

11/30/98　

　　　SELECTtable_name,column_name,data_type,data_length,data_precision,data_scale,nullable
　　FROMall_tab_columns--firstenvironment
　　WHEREwner='&OWNER'
　　MINUS
　　SELECTtable_name,column_name,data_type,data_length,data_precision,data_scale,nullable
　　FROMall_tab_columns@&my_db_link--secondenvironment
　　WHEREwner='&OWNER2'
　　orderbytable_name,column_name

8. obj_coord.sql

obj_coord.sql

To find out any difference in objects between two instances

12/08/98　　　　　

　　　SELECTobject_name,object_type
　　FROMuser_objects
　　MINUS
　　SELECTobject_name,object_type
　　FROMuser_objects@&my_db_link