oracle dba管理脚本(约束)

OracleDBA管理脚本--约束（constraints）1.禁止约束---------------------------------------------------------------------------------------FileName::DRTimothySHall--Description:Disablesallcheckconstraintsforaspecifiedtable,oralltables.--CallSyntax:@disable_chk(table-nameorall)(schema-name)--LastModified:28/01/2001-------------------------------------------------------------------------------------SETPAGESIZE0SETFEEDBACKOFFSETVERIFYOFFSPOOLtemp.sqlSELECT'ALTERTABLE'||a.table_name||'DISABLECONSTRAINT'||a.constraint_name||';'FROMall_constraintsaWHEREa.constraint_type='C'ANDa.owner=UPPER('&2');ANDa.table_name=DECODE(UPPER('&1'),'ALL',a.table_name,UPPER('&1'));[e-mail]14SETFEEDBACKONSETVERIFYON2.禁止外键---------------------------------------------------------------------------------------FileName::DRTimothySHall--Description:DisablesallForeignKeysbelongingtothespecifiedtable,oralltables.--CallSyntax:@disable_fk(table-nameorall)(schema-name)--LastModified:28/01/2001-------------------------------------------------------------------------------------SETPAGESIZE0SETFEEDBACKOFFSETVERIFYOFFSPOOLtemp.sqlSELECT'ALTERTABLE'||a.table_name||'DISABLECONSTRAINT'||a.constraint_name||';'FROMall_constraintsaWHEREa.constraint_type='R'ANDa.table_name=DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))ANDa.owner=Upper('&2');[e-mail]14SETFEEDBACKONSETVERIFYON3.禁止主键---------------------------------------------------------------------------------------FileName::DRTimothySHall--Description:DisablesthePrimaryKeyforthespecifiedtable,oralltables.--CallSyntax:@disable_pk(table-nameorall)(schema-name)--LastModified:28/01/2001-------------------------------------------------------------------------------------SETPAGESIZE0SETFEEDBACKOFFSETVERIFYOFFSPOOLtemp.sqlSELECT'ALTERTABLE'||a.table_name||'DISABLEPRIMARYKEY;'FROMall_constraintsaWHEREa.constraint_type='P'ANDa.owner=Upper('&2')ANDa.table_name=DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'));[e-mail]14SETFEEDBACKONSETVERIFYON4.禁止参考性外键---------------------------------------------------------------------------------------FileName::DRTimothySHall--Description:DisablesallForeignKeysreferencingaspecifiedtable,oralltables.--CallSyntax:@disable_ref_fk(table-name)(schema-name)--LastModified:28/01/2001-------------------------------------------------------------------------------------SETPAGESIZE0SETFEEDBACKOFFSETVERIFYOFFSPOOLtemp.sqlSELECT'ALTERTABLE'||a.table_name||'DISABLECONSTRAINT'||a.constraint_name||';'enable_constraintsFROMall_constraintsaWHEREa.owner=Upper('&2')ANDa.constraint_type='R'ANDa.r_constraint_nameIN(SELECTa1.constraint_nameFROMall_constraintsa1WHEREa1.table_name=DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))ANDa1.owner=Upper('&2'));[e-mail]14SETFEEDBACKONSETVERIFYON5.启用约束---------------------------------------------------------------------------------------FileName::DRTimothySHall--Description:Enablesallcheckconstraintsforaspecifiedtable,oralltables.--CallSyntax:@enable_chk(table-nameorall)(schema-name)--LastModified:28/01/2001-------------------------------------------------------------------------------------SETPAGESIZE0SETFEEDBACKOFFSETVERIFYOFFSPOOLtemp.sqlSELECT'ALTERTABLE'||a.table_name||'ENABLECONSTRAINT'||a.constraint_name||';'FROMall_constraintsaWHEREa.constraint_type='C'ANDa.owner=Upper('&2');ANDa.table_name=DECODE(Upper('&1'),'ALL',a.table_name,UPPER('&1'));[e-mail]14SETFEEDBACKONSETVERIFYON6.启用外键---------------------------------------------------------------------------------------FileName::DRTimothySHall--Description:EnablesallForeignKeysbelongingtothespecifiedtable,oralltables.--CallSyntax:@enable_fk(table-nameorall)(schema-name)--LastModified:28/01/2001-------------------------------------------------------------------------------------SETPAGESIZE0SETFEEDBACKOFFSETVERIFYOFFSPOOLtemp.sqlSELECT'ALTERTABLE'||a.table_name||'ENABLECONSTRAINT'||a.constraint_name||';'FROMall_constraintsaWHEREa.constraint_type='R'ANDa.table_name=DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))ANDa.owner=Upper('&2');[e-mail]14SETFEEDBACKONSETVERIFYON7.启用主键---------------------------------------------------------------------------------------FileName::DRTimothySHa