NO.65 Oracle主外键、索引Sql抽取工具

  AR Tools总体说明：NO.66 AR Tools-开发人员常用小工具，总有一款适合你

 

 --update at 20101011:现将此工具集成至ARTools并调整为抽取所有主外健、索引Sql

【CSDN下载】：

• ARTools.Oracle抽取指定库的主键、外键、索引 

 

【背景】

        为了跟未来生产库的表空间名称一致，通过expdp、impdp依基线库在新的表空间上新建了一套库，过了一段时间后发现由于表空间参数部分不对(当时impdp时remap_tablespace后少了约束、索引所在的表空间)，导致大部分主外键约束索引全部丢失（之所以是大部，是因为后期新增的主外键约束全部建立在了默认表空间上）。

        怎么办？由于这段时间内，库变更较多，不可能拿老的备份重新基线库，只能将Oracle主外键约束索引的创建Sql拿到去新基线库上执行了。

        查了半天，Oracle及附属工具居然没有类似的功能（有大拿了解的，望赐教哈）……

        Sign，自己写工具吧……

 

        事急，先用Java编了，大家谁试试改成存储过程的呗，呵呵。

 

        代码涉及太多，只说一下主要思路吧

 

1【主键部分主要思路】

1.1 获取差异主键的“主键名称 - pk_constraint_name”、“主键表名称 - pk_table_name”

--使用SQl

select constraint_name,table_name from dba_constraints where owner='CODE_BASE_BAK' and constraint_type = 'P' minus select constraint_name,table_name from dba_constraints where owner='CODE_BASE' and constraint_type = 'P'

 --update at 20101011

工具中现修改为：select constraint_name,table_name from user_constraints where constraint_type = 'P' and table_name not like 'BIN$%'

 

1.2 根据“主键名称”查“主键涉及字段的串 - strPKColumns”(多个的话，逗号分隔拼出****,****串)

--使用SQl

select column_name from dba_cons_columns where owner='CODE_BASE_BAK' and constraint_name=? 

 --update at 20101011

工具中现修改为：select column_name from user_cons_columns where constraint_name=?

1.3 根据“主键名称 - pk_constraint_name”、“主键表名称 - pk_table_name”、“主键涉及字段的串 - strPKColumns”拼出主键创建SQL

sb.append("alter table ").append(pk_table_name);

sb.append("  add constraint ").append(pk_constraint_name);

sb.append(" primary key (");

sb.append(strPKColumns);

sb.append(");\n");

 

2【外键部分主要思路】

2.1 获取差异主键的“外键名称 - fK_constraint_name”、“外键表名称 - fk_table_name”，“依赖的主键名称 - r_constraint_name”

select constraint_name,table_name,r_constraint_name from dba_constraints where owner='CODE_BASE_BAK' and constraint_type = 'R' minus select constraint_name,table_name,r_constraint_name from dba_constraints where owner='CODE_BASE' and constraint_type = 'R' 

--update at 20101011

工具中现修改为：select constraint_name,table_name,r_constraint_name from user_constraints where  constraint_type = 'R'

 

2.2 根据“依赖的主键名称 - r_constraint_name”查出“主键所在表 - pk_table_name”

select table_name from dba_constraints where owner='CODE_BASE_BAK' and constraint_name = ? 

--update at 20101011

工具中现修改为：select constraint_name,table_name,r_constraint_name from user_constraints where  constraint_type = 'R'

 

2.3 根据“外键名称”查“外键涉及字段的串 - strFKColumns”(多个的话，逗号分隔拼出****,****串)

Sql同1.2

 

2.4 根据“依赖的主键名称”查“主键涉及字段的串 - strPKColumns”(多个的话，逗号分隔拼出****,****串)

Sql同1.2

 

2.5 根据“外键名称 - fk_constraint_name”、“外键表名称 - fk_table_name”、“主键表名称 - pk_table_name”、“外键涉及字段的串 - strFKColumns”、“主键涉及字段的串 - strPKColumns”拼出外键创建SQL

sb.append("alter table ").append(  fk_table_name);

sb.append("  add constraint ").append(fk_constraint_name);

sb.append(" foreign key (");

sb.append(strFKColumns);

sb.append(")  references ");

sb.append(pk_table_name);

sb.append(" (");

sb.append( strPKColumns);

sb.append(");\n");

 

3【索引部分主要思路】

3.1 获取差异主键的“索引名称 - idx_name”、“索引表名称 - idx_table_name”

select index_name,table_name from dba_indexes where owner='CODE_BASE_BAK' minus select index_name,table_name from dba_indexes where owner='CODE_BASE'

--update at 20101011

工具中现修改为：select index_name,table_name from user_indexes

 

3.2 根据“索引名称”查“索引涉及字段的串 - strIDXColumns”(多个的话，逗号分隔拼出****,****串)

select column_name from dba_ind_columns where index_owner='CODE_BASE_BAK' and index_name=?

 --update at 20101011

工具中现修改为：select column_name from user_ind_columns where index_name=?

 

3.3 根据“索引名称 - idx_name”、“索引表名称 - idx_table_name”、“主键涉及字段的串 - strIDXColumns”拼出索引创建SQL

sb.append("create index ").append( idx_name);

sb.append("  on ").append( idx_table_name);

sb.append(" (");

sb.append(strIDXColumns);

sb.append(");\n");