oracle删除无效的列,怎么修改删除虚拟列 - Oracle数据库管理 - ITPUB论坛－中国专业的IT技术社区...

SQL> drop table t_fun;

Table dropped

SQL> create table t_fun(a varchar2(10) not null,b varchar2(10) not null,c char(1000) not null);

Table created

SQL> select dbms_stats.create_extended_stats(ownname=>SYS_CONTEXT('USERENV','CURRENT_USER'),tabname=>upper('t_fun'),extension =>'(SUBSTR("A",2,1))') from dual;

DBMS_STATS.CREATE_EXTENDED_STA

--------------------------------------------------------------------------------

SYS_STUGBGHS5HBZX15XHKCLFJHSBZ

SQL>

SQL> desc t_fun

Name Type         Nullable Default Comments

---- ------------ -------- ------- --------

A    VARCHAR2(10)

B    VARCHAR2(10)

C    CHAR(1000)

SQL>  alter table t_fun add d as (substr(a,2,1));

alter table t_fun add d as (substr(a,2,1))

ORA-54015: 指定了重复的列表达式

SQL> execute dbms_stats.drop_extended_stats(ownname=>SYS_CONTEXT('USERENV','CURRENT_USER'),tabname=>upper('&table_name'),extension => '(SUBSTR("A",2,1))')

PL/SQL procedure successfully completed

SQL> alter table t_fun add d as (substr(a,2,1));

Table altered

SQL> desc t_fun

Name Type         Nullable Default         Comments

---- ------------ -------- --------------- --------

A    VARCHAR2(10)

B    VARCHAR2(10)

C    CHAR(1000)

D    VARCHAR2(4)  Y        SUBSTR("A",2,1)

SQL> execute dbms_stats.drop_extended_stats(ownname=>SYS_CONTEXT('USERENV','CURRENT_USER'),tabname=>upper('&table_name'),extension => '(SUBSTR("A",2,1))')

PL/SQL procedure successfully completed

SQL> desc t_fun

Name Type         Nullable Default Comments

---- ------------ -------- ------- --------

A    VARCHAR2(10)

B    VARCHAR2(10)

C    CHAR(1000)

SQL> alter table t_fun drop column d;

alter table t_fun drop column d

ORA-00904: "D": 标识符无效

to  〇〇

您能解释一下 我的这个过程吗？

dbms_stats.drop_extended_stats 他会删除虚拟列

create_extended_stats 会创建虚拟列，但是这个虚拟列 不存在表结构中

所有我刚才一直drop不掉，原来被drop_extended_stats drop掉了