一、創建測試表
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
最后修改時間：2022-05-10 08:05:04
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
