一、創建測試表

scott@orcl> create table t2(id int,name varchar2(20),pho number);

Table created.
scott@orcl> insert into t2 values(1,'scott',13888888888);

1 row created.

scott@orcl> commit;

Commit complete.
scott@orcl> desc t2;
 Name                                                                                  Null?    Type
 ------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
 ID                                                                                             NUMBER(38)
 NAME                                                                                           VARCHAR2(20)
 PHO   

scott@orcl> select * from t2;

        ID NAME                                                      PHO
---------- -------------------------------------------------- ----------
         1 scott                                              1.3889E+10

二、創建視圖和同義詞

scott@orcl> create view t_v  as select * from t2;

View created.

scott@orcl> alter table t2 set unused(pho);

Table altered.

scott@orcl> select * from t_v;
select * from t_v
              *
ERROR at line 1:
ORA-04063: view "SCOTT.T_V" has errors


scott@orcl> alter table t2 add pho number;

Table altered.

scott@orcl> update t2 set pho=13888888888 where id=1;

1 row updated.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from t_v;

        ID NAME                                                      PHO
---------- -------------------------------------------------- ----------
         1 scott                                              1.3889E+10
scott@orcl> create synonym t_s for t2;

Synonym created.

scott@orcl> alter table t2 set unused(pho);

Table altered.

三、標記為不可見

scott@orcl> alter table t2 set unused(pho);
alter table t2 set unused(pho)
                          *
ERROR at line 1:
ORA-00904: "PHO": invalid identifier

scott@orcl> select * from t_s;

        ID NAME
---------- --------------------------------------------------
         1 scott
scott@orcl> select * from t_v;
select * from t_v
              *
ERROR at line 1:
ORA-04063: view "SCOTT.T_V" has errors

同義詞可見視圖不可見，同義詞是別名，視圖因為依賴關系，已經失效

四、添加列

scott@orcl> alter table t2 add pho number;

Table altered.

scott@orcl> update t2 set pho=13888888888 where id=1;

1 row updated.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from t2;

        ID NAME                                                      PHO
---------- -------------------------------------------------- ----------
         1 scott                                              1.3889E+10
scott@orcl> select * from t_v;

        ID NAME                                                      PHO
---------- -------------------------------------------------- ----------
         1 scott                                              1.3889E+10

五、試試索引

scott@orcl>  alter table t2 add pho number;

Table altered.

scott@orcl> update t2 set pho=13888888888 where id=1;

1 row updated.

scott@orcl> commit;

Commit complete.

scott@orcl> create index idx_t2 on t2(pho);

scott@orcl> col INDEX_NAME for a20
scott@orcl> select index_name,status from user_indexes;

INDEX_NAME           STATUS
-------------------- ----------------
IDX_T2               VALID

scott@orcl> alter table t2 set unused(pho);

Table altered.

scott@orcl> select index_name,status from user_indexes;

no rows selected

一起消失了

scott@orcl> alter table t2 add pho number;

Table altered.

scott@orcl> update t2 set pho=13888888888 where id=1;

1 row updated.

scott@orcl> commit;

Commit complete.

scott@orcl> select index_name,status from user_indexes;

no rows selected

scott@orcl> create index idx_t2 on t2(pho);

Index created.

scott@orcl> select index_name,status from user_indexes;

INDEX_NAME           STATUS
-------------------- ----------------
IDX_T2               VALID