set unused column和视图，约束，同义词和索引的关系

最新推荐文章于 2025-10-19 09:25:36 发布

转载最新推荐文章于 2025-10-19 09:25:36 发布 · 1.1k 阅读

oracle 专栏收录该内容

61 篇文章

订阅专栏

本文通过实验演示了在Oracle数据库中使用ALTER TABLE SET UNUSED COLUMN语法后，对视图、同义词、约束及索引的影响。结果显示，视图和同义词会变为无效状态，其中同义词可直接重新编译，而视图需修改后才能重新编译；约束和索引则被自动删除。

当使用alter table set unused column语法之后，会对其相关联的视图，约束，同义词和索引有什么作用么？

答案是：

视图和同义词会失效，其中同义词再次引用即可重新编译，但是视图重新编译会报错；

约束和索引会被自动删除。

实验：

SQL> create table test as select * from dba_objects;

Table created.

SQL> create view testview as select * from test;

View created.

SQL> alter table test add constraint testc check(object_id<999999);

Table altered.

SQL> create index testidx on test(object_id);

Index created.

SQL> create synonym testsyn for test;

Synonym created.

SQL> select CONSTRAINT_NAME,status from user_constraints
2 where CONSTRAINT_NAME in ('TESTC');

CONSTRAINT_NAME STATUS
------------------------------ --------
TESTC ENABLED

SQL> select OBJECT_NAME,status from user_objects
2 where OBJECT_NAME in ('TESTVIEW','TESTIDX','TESTSYN');

OBJECT_NAME                    STATUS
------------------------------ -------
TESTIDX                        VALID
TESTSYN                        VALID
TESTVIEW                       VALID

SQL> alter table test set unused (object_id);

Table altered.

SQL> select CONSTRAINT_NAME,status from user_constraints
2 where CONSTRAINT_NAME in ('TESTC');

no rows selected

SQL> select OBJECT_NAME,status from user_objects
2 where OBJECT_NAME in ('TESTVIEW','TESTIDX','TESTSYN');

OBJECT_NAME                    STATUS
------------------------------ -------
TESTSYN                        INVALID
TESTVIEW                       INVALID

SQL> select count(*) from TESTSYN;

COUNT(*)
----------
10069

SQL> select count(*) from TESTVIEW;
select count(*) from TESTVIEW
*
ERROR at line 1:
ORA-04063: view "HAO.TESTVIEW" has errors