12c新特性不可用列小测试 -m6米乐安卓版下载

一、创建测试表

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