Oracle表重命名后索引、约束、权限、同义词的影响

65 阅读 0 评论 43 点赞

我是靠谱客的博主儒雅斑马，最近开发中收集的这篇文章主要介绍Oracle表重命名后索引、约束、权限、同义词的影响，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

create public synonym test01 for scott.test01; --同义词
create index scott.idx_col_date on scott.test01(col_date); --索引
alter table TEST01 add constraint UK_TEST01 unique (COL_DATE); --唯一约束
alter table test01 modify col_date not null; --not null 约束
grant select,insert,update,delete on scott.test01 to xiaozhang; --授权

--变更表名
alter table scott.test01 rename to ttt01;
--检查索引和约束情况
select owner,index_name,index_type,table_owner,table_name,uniqueness from DBA_INDEXES where table_name='TTT01';

OWNER INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME UNIQUENESS
---------- -------------------- ---------- ---------- -------------------- ----------
SCOTT IDX_COL_DATE NORMAL SCOTT TTT01 NONUNIQUE

select t.owner,t.constraint_name,t.constraint_type,t.table_name,t.index_owner,t.index_name,STATUS from dba_constraints t where t.table_name='TTT01';

OWNER CONSTRAINT_NAME CONSTRAINT TABLE_NAME INDEX_OWNE INDEX_NAME STATUS
---------- -------------------- ---------- -------------------- ---------- -------------------- ----------
SCOTT SYS_C0011193 C TTT01 ENABLED
SCOTT UK_TEST01 U TTT01 SCOTT IDX_COL_DATE ENABLED
--检查同义词情况
select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where table_name='TEST01';

OWNER SYNONYM_NAME TABLE_OWNE TABLE_NAME
---------- ------------------------------ ---------- --------------------
PUBLIC TEST01 SCOTT TEST01
select owner,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='TEST01';

OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- ----------
PUBLIC TEST01 SYNONYM INVALID
--检查权限情况
SELECT 'GRANT ' || T3.PRIVILEGE || ' ON ' || T3.OWNER || '.' ||
T3.TABLE_NAME || ' TO ' || T3.GRANTEE || ';' AS TEXT
FROM DBA_TAB_PRIVS T3 where table_name='TTT01';

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------
GRANT DELETE ON SCOTT.TTT01 TO XIAOZHANG;
GRANT INSERT ON SCOTT.TTT01 TO XIAOZHANG;
GRANT SELECT ON SCOTT.TTT01 TO XIAOZHANG;
GRANT UPDATE ON SCOTT.TTT01 TO XIAOZHANG;

总结：索引、约束(包括外键)、权限在表重命名后，会跟随着变更，无需单独处理。同义词、视图会失效，需要单独处理。