ITPub博客

首页 > Linux操作系统 > Linux操作系统 > set unused column和视图,约束,同义词和索引的关系

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

原创 Linux操作系统 作者:viadeazhu 时间:2010-09-11 23:01:26 0 删除 编辑

当使用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
                       
                        
                      

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15415488/viewspace-673249/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-22

  • 博文量
    79
  • 访问量
    368901