记一次Oracle Rename Table的实战应用
起因
在生产环境下我们要在某个表 PRODUCT 上删除一些重复的数据。开始之前，我们首先对该表做了备份，如下：
CREATE TABLE APP.PRODUCT_201910 TABLESPACE "TABLESPACE_1" nologging AS SELECT * FROM APP.PRODUCT WHERE 1=2;
INSERT /*+ APPEND PARALLEL(8) */ INTO APP.PRODUCT_201910 nologging SELECT * FROM APP.PRODUCT;
COMMIT;
需要注意的是，使用 CATS(CREATE TABLE AS) 方法创建出来的表，不从其父表继承约束、身份列、默认列值或主键。
完了之后，我们就开始对 PRODUCT 进行操作，但是由于 DB Performance 的原因，在原先预估的时间内没有完成。为了不影响后续其它脚本，决定先放弃这个操作，将 PRODUCT 还原回去。
发展
首先，我们让 DBA 定位到正在运行的 query ，手动将其 kill 掉。
其次，我们用 backup 表替换回原表。
RENAME APP.PRODUCT TO APP.PRODUCT_BROKEN;
RENAME TABLE APP.PRODUCT_201910 TO APP.PRODUCT;
但是，到这里还没有结束，原表还有很多属性需要重新再修改回去。这个过程需要特别注意，因为很容易漏掉一些步骤。
设置 Sequence, Default Value
这几步是比较常见也是执行得很快的。
Add sequence
DROP SEQUENCE APP.PRODUCT_ID_SEQ
ALTER TABLE APP.PRODUCT MODIFY PRODUCT_ID NUMBER(10,0) DEFAULT APP.PRODUCT_ID_SEQ.NEXTVAL
Set default value
ALTER TABLE APP.PRODUCT MODIFY PRODUCT_KEY VARCHAR2(300 CHAR) DEFAULT '-1';
ALTER TABLE APP.PRODUCT MODIFY PRODUCT_TYPE_ID NUMBER(5,0) DEFAULT -1;
ALTER TABLE APP.PRODUCT MODIFY DATE TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP;
设置 Constrain
- 加主键。
- 检查一下是否有外键链接到该表。
-- 1
DROP CONSTRAINT APP.PRODUCT_PK
ALTER TABLE APP.PRODUCT ADD CONSTRAINT APP.PRODUCT_PK PRIMARY KEY ("PRODUCT_PK");
-- 2
SELECT * 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 = 'PRODUCT'
AND B.OWNER = 'APP';
设置 Index
索引加起来可能会慢一点。
DROP INDEX APP.IDX_PRODUCT_KEY_DATE
CREATE INDEX APP.IDX_PRODUCT_KEY_DATE ON APP.PRODUCT ("PUBLIC_KEY", "END_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TABLESPACE_1";
需要注意的是，主键的index不需要再额外创建。
Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled.
设置 Dependency
依赖对象重建：这里重建的只是直接依赖对象。
-- check sql
SELECT * FROM DBA_DEPENDENCIES A WHERE A.REFERENCED_NAME = 'PRODUCT' AND A.REFERENCED_OWNER = 'APP';
-- modify sql
SELECT 'alter '||DECODE(TYPE,'PACKAGE BODY','PACKAGE',TYPE)||' '||OWNER||'.'||NAME||' compile;'
FROM DBA_DEPENDENCIES A
WHERE A.REFERENCED_NAME = 'PRODUCT'
AND A.REFERENCED_OWNER = 'APP';
设置 Privilege
权限重建
-- check sql
SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME = UPPER('PRODUCT') AND OWNER = UPPER('APP');
-- modify sql
SELECT 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || GRANTEE || ';'
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = UPPER('PRODUCT')
AND OWNER = UPPER('APP');
总结
除了以上提到的
- Sequence, Default Value
- Constrain
- Index
- Dependency
- Privilege
之外，还有物化视图和物化视图日志，具体可以看参考链接。
所以，用 rename 方法还是相当麻烦的。如果数据量不是很大的情况下，还是建议直接把数据 insert 回原表，方便简洁不易出错。
