12c新特性不可用列小测试

一、创建测试表

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