Oracle修改表名、主键、索引、外键删除重建

转载请注明出处：http://blog.csdn.net/jevonsCSDN/article/details/78034160 【Jevons’Blog】

最近因为要对现有的表结构进行更改，并重建一套与重改之前一模一样的一套表，所以整理了一些SQL，以后备用。（至于为什么要这样做，是因为需要将源表清空，但数据量过大，时间比较紧，所以就不再备份了，索性直接RENAME表名，修改表结构用来当备份表，再重建一套改名前一模一样的系列空表）。以下提及的SQL有可能涉及数据操作，注意先备份。

1.批量生成表名修改语句

SELECT 'ALTER TABLE ' || TABLE_NAME || ' RENAME TO ' || TABLE_NAME ||       '_BAK_0919'   FROM USER_TABLES T  WHERE T.TABLE_NAME LIKE 'PRO%';

2.批量生成主键名修改语句

SELECT 'ALTER TABLE ' || TABLE_NAME ||' '|| 'RENAME  constraint ' ||CONSTRAINT_NAME || ' TO PK_'|| TABLE_NAME||';' FROM USER_CONSTRAINTSWHERE TABLE_NAME LIKE 'PRO%' AND CONSTRAINT_TYPE = 'P';

3.批量生成索引重命名语句

SELECT 'alter index ' || I.INDEX_NAME || ' rename to ' ||       SUBSTR(I.INDEX_NAME,              0,              DECODE(SIGN(25 - LENGTH(I.INDEX_NAME)),                     '-1',                     25,                     LENGTH(I.INDEX_NAME))) || 'temp;',       I.TABLE_NAME,       I.TABLESPACE_NAME  FROM USER_INDEXES I WHERE I.TABLE_NAME LIKE 'PRO%'   AND I.INDEX_TYPE = 'NORMAL';-- 条件根据自己需要调整

4.批量生成外键删除语句

SELECT 'ALTER TABLE ' || TABLE_NAME ||' '||'DROP CONSTRAINT ' || CONSTRAINT_NAME || ' ;' FROM USER_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'R'  AND TABLE_NAME LIKE 'PRO%' ;

5.批量生成外键重建语句

-- 注意：这个sql是有问题的，源表存在联合外键时需手工调整合并sql，有兴趣的可以重新写一个拼装SQLSELECT 'ALTER TABLE ' || child.TABLE_NAME||' ' ||'ADD CONSTRAINT ' || child.CONSTRAINT_NAME ||' '||'FOREIGN KEY ' || '('|| cp.COLUMN_NAME || ')' ||' '|| 'REFERENCES ' || parent.TABLE_NAME ||' '||'('|| pc.COLUMN_NAME || ') '|| child.DELETE_RULE || ' ;' FROM USER_CONSTRAINTS child,USER_CONSTRAINTS parent,USER_CONS_COLUMNS cp,USER_CONS_COLUMNS pc WHERE child.CONSTRAINT_TYPE = 'R' ANDchild.R_OWNER = PARENT.OWNER ANDchild.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME ANDchild.CONSTRAINT_NAME = cp.CONSTRAINT_NAME ANDparent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME ANDcp.POSITION = pc.POSITION ANDchild.TABLE_NAME LIKE 'PRO%' ORDER BY child.OWNER,child.TABLE_NAME,child.CONSTRAINT_NAME,cp.POSITION;

PS: 本例并不适用于任何场景，仅供参考使用。 再次提醒，在进行这些操作之前，一定要先将源表表结构备份！