[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.

[20210423]建立檢視以及欄位長度.txt

相關文章