[20210423]建立视图以及字段长度.txt

[20210423]建立视图以及字段长度.txt --//这个测试源于我在19c上使用toad遇到的问题,使用toad的schema brower看表的script，出现如下错误: ORA-00904 REF invalid identifier. --//后来发现是由于19c视图dba_obj_audit_opts的定义取消了ref字段.我想在11g下测试改名源视图,重新定义新的视图遇到的问题,通过 --//例子说明. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立视图: SCOTT@book> create view v_dept as select deptno,SUBSTR (dname, 1, 1) || '/' || SUBSTR (dname, 2, 1) dnamex from dept; View created. SCOTT@book> @ desc v_dept            Name     Null?    Type            -------- -------- ----------------------------     1      DEPTNO   NOT NULL NUMBER(2)     2      DNAMEX            VARCHAR2(5) --//你可以发现dnamex的类型是varchar2,长度是5.为什么? SCOTT@book> create view v_deptx as select deptno,SUBSTRB (dname, 1, 1) || '/' || SUBSTRB (dname, 2, 1) dnamex from dept; View created. SCOTT@book> @ desc v_deptx            Name     Null?    Type            -------- -------- ----------------------------     1      DEPTNO   NOT NULL NUMBER(2)     2      DNAMEX            VARCHAR2(3) --//采用substrb函数正常，很明显这是字符集导致的问题。 --//看看dba_obj_audit_opts的定义： SCOTT@book>  @ desc dba_obj_audit_opts            Name         Null?    Type            ------------ -------- -------------     1      OWNER                 VARCHAR2(30)     2      OBJECT_NAME           VARCHAR2(30)     3      OBJECT_TYPE           VARCHAR2(23)     4      ALT                   VARCHAR2(3)     5      AUD                   VARCHAR2(3)     6      COM                   VARCHAR2(3)     7      DEL                   VARCHAR2(3)     8      GRA                   VARCHAR2(3)     9      IND                   VARCHAR2(3)    10      INS                   VARCHAR2(3)    11      LOC                   VARCHAR2(3)    12      REN                   VARCHAR2(3)    13      SEL                   VARCHAR2(3)    14      UPD                   VARCHAR2(3)    15      REF                   CHAR(3) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    16      EXE                   VARCHAR2(3)    17      CRE                   VARCHAR2(3)    18      REA                   VARCHAR2(3)    19      WRI                   VARCHAR2(3)    20      FBK                   VARCHAR2(3) --//里面相似的方式建立视图的字符串长度是3,为什么呢? --//很容易想到的问题,oracle开始建立的数据库字符集是ASCII的.然后在通过alter database修改为别的字符集. --//我们生产系统数据库字符集都是ZHS16GBK. 3.分析: --//正好我们一个老的的数据库使用的字符集是US7ASCII的,在上面测试看看. XXXX> select * from v$version where rownum=1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi XXXX> create view scott.v_dept as select deptno,SUBSTR (dname, 1, 1) || '/' || SUBSTR (dname, 2, 1) dnamex from scott.dept; View created. XXXX> set linesize 80 XXXX> desc scott.v_dept;  Name    Null?    Type  ------- -------- ----------------------------  DEPTNO  NOT NULL NUMBER(2)  DNAMEX           VARCHAR2(3) --//验证正确。 XXXX> drop view scott.v_dept; View dropped. 4.能否在字符集ZHS16GBK建立varchar2(3)的视图呢? --//我尝试修改用户的环境变量NLS_LANG=AMERICAN_AMERICA.US7ASCII,登陆后测试不行. $ export NLS_LANG=AMERICAN_AMERICA.US7ASCII SCOTT@book> create or replace view v_dept as select deptno,SUBSTR (dname, 1, 1) || '/' || SUBSTR (dname, 2, 1) dnamex from dept; View created. SCOTT@book> @ desc v_dept;            Name    Null?    Type            ------- -------- ----------------------------     1      DEPTNO  NOT NULL NUMBER(2)     2      DNAMEX           VARCHAR2(5) --//依旧不行。视乎登录会设置正确的数据库字符集。 $ echo $NLS_LANG AMERICAN_AMERICA.US7ASCII SYS@book> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@book> startup upgrade ORACLE instance started. Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes Database mounted. Database opened. SYS@book> create or replace view scott.v_dept as select deptno,SUBSTR (dname, 1, 1) || '/' || SUBSTR (dname, 2, 1) dnamex from scott.dept; View created. SYS@book> @ desc scott.v_dept;            Name   Null?    Type            ------ -------- ------------     1      DEPTNO NOT NULL NUMBER(2)     2      DNAMEX          VARCHAR2(5) 5.继续： --//知道问题的原因，不过我觉得问题应该不大。即使这样建立修改dba_obj_audit_opts视图使用不应该存在问题。 $ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK --//重启数据库略。 SYS@book> rename dba_obj_audit_opts to dba_obj_audit_opts_org; Table renamed. --//我在11g上测试scott不会报错，可能它使用user_obj_audit_opts。而sys用报 --//the select privilege is required on DBA_OBJ_AUDIT_OPTS SYS@book> @ /tmp/11g.txt View created. SYS@book> @ desc dba_obj_audit_opts            Name        Null?    Type            ----------- -------- ------------     1      OWNER                VARCHAR2(30)     2      OBJECT_NAME          VARCHAR2(30)     3      OBJECT_TYPE          VARCHAR2(23)     4      ALT                  VARCHAR2(5)     5      AUD                  VARCHAR2(5)     6      COM                  VARCHAR2(5)     7      DEL                  VARCHAR2(5)     8      GRA                  VARCHAR2(5)     9      IND                  VARCHAR2(5)    10      INS                  VARCHAR2(5)    11      LOC                  VARCHAR2(5)    12      REN                  VARCHAR2(5)    13      SEL                  VARCHAR2(5)    14      UPD                  VARCHAR2(5)    15      REF                  CHAR(3)    16      EXE                  VARCHAR2(5)    17      CRE                  VARCHAR2(5)    18      REA                  VARCHAR2(5)    19      WRI                  VARCHAR2(5)    20      FBK                  VARCHAR2(5) --//再次使用toad并不影响使用。另外我的测试11g即使没有它，报错选择ok可以继续。查看没有问题，19c不行。 SYS@book> drop view dba_obj_audit_opts; View dropped. SYS@book> rename dba_obj_audit_opts_org to dba_obj_audit_opts; Table renamed.