要更新一个用户信息表的mail字段时,提示ORA-30657错误
SQL> update class_users set email='XXXXXXXXX@qq.com' where id = 73257;
update class_users set email='XXXXXXXXX@qq.com' where id = 73257
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
此错误是因为class_users表是一个外部表,不能直接update更新,可以直接更新引用的文本文件里的内容
SQL> select object_name,object_type from user_objects where object_name='CLASS_USERS';
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
CLASS_USERS TABLE
SQL> select * from tab where tname='CLASS_USERS';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CLASS_USERS TABLE
SQL> set long 9999999
SQL> select dbms_metadata.get_ddl('TABLE','CLASS_USERS') from dual;
DBMS_METADATA.GET_DDL('TABLE','CLASS_USERS')
--------------------------------------------------------------------------------
CREATE TABLE "NEWCOURSE"."CLASS_USERS"
( "ID" NUMBER(10,0),
"USER_NAME" VARCHAR2(150),
"PASSWD" VARCHAR2(32),
"CHANNEL" VARCHAR2(50),
"PROPERTIES" NUMBER(19,0),
"EMAIL" VARCHAR2(250),
"MOBILE" VARCHAR2(20),
"EMAIL_VERIFIED" NUMBER(1,0),
"MOBILE_VERIFIED" NUMBER(1,0),
"ROLE" NUMBER(1,0)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DPDIR"
ACCESS PARAMETERS
( records delimited by newline
fields terminated by ','
)
LOCATION
( 'class_users.info'
)
)
REJECT LIMIT UNLIMITED
SQL> select directory_path from dba_directories where directory_name='DPDIR';
DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------
/tol/backup/dmp
QL> col email for a35
SQL> select id,email from class_users where id = 73257;
ID EMAIL
---------- -----------------------------------
73257
SQL> desc class_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
USER_NAME VARCHAR2(150)
PASSWD VARCHAR2(32)
CHANNEL VARCHAR2(50)
PROPERTIES NUMBER(19)
EMAIL VARCHAR2(250)
MOBILE VARCHAR2(20)
EMAIL_VERIFIED NUMBER(1)
MOBILE_VERIFIED NUMBER(1)
ROLE NUMBER(1)
+++编辑/tol/backup/dmp 目录下的class_users.info文件,查找到id=73257的记录,改为:
73257,amosquito6,63c4d05e73b829727c1ff47bd4388d00,class,0,345305538@qq.com,,0,0,0
+++再次查询:
SQL> select id,email from class_users where id = 73257;
ID EMAIL
---------- -----------------------------------
73257 345305538@qq.com