[20221030]繫結變數的分配長度10.txt

lfree發表於2022-11-02

[20221030]繫結變數的分配長度10.txt

--//如果繫結變數中字串分配佔用空間的長度變化,oracle會建立子游標。
--//測定nvarchar2型別的情況.
--//varchar2型別的情況. 連結 http://blog.itpub.net/267265/viewspace-2056695/

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t (c1 nvarchar2(32767));
create table t (c1 nvarchar2(32767))
                                   *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SCOTT@test01p> @ calcx 32767/2
                                DEC                  HEX
----------------------------------- --------------------
                       16383.500000 0000000000004000

SCOTT@test01p> create table t (c1 nvarchar2(16384));
create table t (c1 nvarchar2(16384))
                                   *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SCOTT@test01p> create table t (c1 nvarchar2(16383));
Table created.

declare
v_c1 nvarchar2(16383);
  begin
   for i in 1..16383 loop
     v_c1 := rpad('X',i);
     execute immediate 'select count(*) from t where c1=:instring' using v_c1 ;
    end loop;
end;
/
commit;
--//確定sql_id='9mrd273576n14'

2.看看結果:
SCOTT@test01p> @ bind_cap 9mrd273576n14 ''
SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------
9mrd273576n14            0 YES :INSTRING                     1         32 2022-10-30 21:33:47 NVARCHAR2(32)   X
                         1 YES :INSTRING                     1        128 2022-10-30 21:33:47 NVARCHAR2(128)  X
                         2 YES :INSTRING                     1       2000 2022-10-30 21:33:47 NVARCHAR2(2000) X

SYS@test>  @ hide _cursor_bind_capture_area_size
NAME                           DESCRIPTION                                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ -------------------------------------------- ------------- ------------- ----------------
_cursor_bind_capture_area_size maximum size of the cursor bind capture area TRUE          400           400

--//受隱含引數_cursor_bind_capture_area_size大小控制的影響,僅僅顯示3個子游標.

SCOTT@test01p> select sql_id, child_number, executions  from v$sql where sql_id = '9mrd273576n14';
SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
9mrd273576n14            0         16
9mrd273576n14            1         48
9mrd273576n14            2        936
9mrd273576n14            3       1000
9mrd273576n14            4       2096
9mrd273576n14            5       4097
9mrd273576n14            6       8190
7 rows selected.
--//因為nvarchar2型別一個英文字元佔用2個位元組.

SCOTT@test01p> select sql_id, child_number,executions, sum(executions) over (partition by sql_id order by child_number)  N20 from v$sql where sql_id = '9mrd273576n14' order by child_number;
SQL_ID        CHILD_NUMBER EXECUTIONS        N20
------------- ------------ ---------- ----------
9mrd273576n14            0         16         16
9mrd273576n14            1         48         64
9mrd273576n14            2        936       1000
9mrd273576n14            3       1000       2000
9mrd273576n14            4       2096       4096
9mrd273576n14            5       4097       8193
9mrd273576n14            6       8190      16383
7 rows selected.
--//如果varchar2型別按照 32,128,2000,4000,8192,16386來分段.這樣對於我當前字符集環境nvarchar2就是在原來基礎除以2.
--//Nvarchar2型別按照 16,64,1000,2000,4096,8193.
--//有一個矛盾的地方@ bind_cap 9mrd273576n14 '' 顯示的是NVARCHAR2(32).而實際上這裡的32標識DATA_LENGTH.

SCOTT@test01p> select * from dba_tab_cols where owner='SCOTT' and table_name='T'
  2  @ prxx
==============================
OWNER                         : SCOTT
TABLE_NAME                    : T
COLUMN_NAME                   : C1
DATA_TYPE                     : NVARCHAR2
DATA_TYPE_MOD                 :
DATA_TYPE_OWNER               :
DATA_LENGTH                   : 32766
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DATA_PRECISION                :
DATA_SCALE                    :
NULLABLE                      : Y
COLUMN_ID                     : 1
DEFAULT_LENGTH                :
DATA_DEFAULT                  :
NUM_DISTINCT                  :
LOW_VALUE                     :
HIGH_VALUE                    :
DENSITY                       :
NUM_NULLS                     :
NUM_BUCKETS                   :
LAST_ANALYZED                 :
SAMPLE_SIZE                   :
CHARACTER_SET_NAME            : NCHAR_CS
CHAR_COL_DECL_LENGTH          : 16383
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
GLOBAL_STATS                  : NO
USER_STATS                    : NO
AVG_COL_LEN                   :
CHAR_LENGTH                   : 16383
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CHAR_USED                     : C
V80_FMT_IMAGE                 : NO
DATA_UPGRADED                 : YES
HIDDEN_COLUMN                 : NO
VIRTUAL_COLUMN                : NO
SEGMENT_COLUMN_ID             : 1
INTERNAL_COLUMN_ID            : 1
HISTOGRAM                     : NONE
QUALIFIED_COL_NAME            : C1
USER_GENERATED                : YES
DEFAULT_ON_NULL               : NO
IDENTITY_COLUMN               : NO
SENSITIVE_COLUMN              : NO
EVALUATION_EDITION            :
UNUSABLE_BEFORE               :
UNUSABLE_BEGINNING            :
COLLATION                     : USING_NLS_COMP
COLLATED_COLUMN_ID            :
PL/SQL procedure successfully completed.

--//注意看下劃線內容.

SCOTT@test01p> @ desc t
 Name   Null?    Type
 ------ -------- ----------------------------
 C1              NVARCHAR2(16383)

SCOTT@test01p> @ desczz t 1
eXtended describe of t

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .

Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ------------------
SCOTT      T                                                       1 C1                              NVARCHAR2(32766)
--//desczz指令碼顯示的就是dba_tab_cols.DATA_LENGTH資訊.
--//如果你使用toad的scahme broswer的script看錶定義:
CREATE TABLE SCOTT.DEPT
(
  DEPTNO  NUMBER(2),
  DNAME   VARCHAR2(14 BYTE),
  LOC     VARCHAR2(13 BYTE)
)
--//dname數字後跟一個byte.

CREATE TABLE t1
AS
   SELECT object_id
         ,object_name
         ,CAST (object_name AS NVARCHAR2 (128)) vobject_name
     FROM all_objects where rownum<=3;

--//toad的scahme broswer的script看錶定義如下:
CREATE TABLE SCOTT.T1
(
  OBJECT_ID     NUMBER                          NOT NULL,
  OBJECT_NAME   VARCHAR2(30 BYTE)               NOT NULL,
  VOBJECT_NAME  NVARCHAR2(128)                  NOT NULL
)

--//nvarchar2(沒有帶byte),實際上這個定義的是CHAR_LENGTH.
SYS@book> select * from dba_tab_cols where owner='SCOTT' and table_name='T1' and column_name='VOBJECT_NAME'
  2  @ pr
==============================
OWNER                         : SCOTT
TABLE_NAME                    : T1
COLUMN_NAME                   : VOBJECT_NAME
DATA_TYPE                     : NVARCHAR2
DATA_TYPE_MOD                 :
DATA_TYPE_OWNER               :
DATA_LENGTH                   : 256
DATA_PRECISION                :
DATA_SCALE                    :
NULLABLE                      : N
COLUMN_ID                     : 3
DEFAULT_LENGTH                :
DATA_DEFAULT                  :
NUM_DISTINCT                  : 3
LOW_VALUE                     : 0043004F004E0024
HIGH_VALUE                    : 6D4B8BD54E2D6587
DENSITY                       : .333333333333333
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 2022-10-12 22:00:07
SAMPLE_SIZE                   : 3
CHARACTER_SET_NAME            : NCHAR_CS
CHAR_COL_DECL_LENGTH          : 128
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 11
CHAR_LENGTH                   : 128
CHAR_USED                     : C
V80_FMT_IMAGE                 : NO
DATA_UPGRADED                 : YES
HIDDEN_COLUMN                 : NO
VIRTUAL_COLUMN                : NO
SEGMENT_COLUMN_ID             : 3
INTERNAL_COLUMN_ID            : 3
HISTOGRAM                     : NONE
QUALIFIED_COL_NAME            : VOBJECT_NAME
PL/SQL procedure successfully completed.

SYS@book> insert into scott.t1  values (1,'x',lpad('x',128,'x'));
1 row created.

SYS@book> insert into scott.t1  values (1,'x',lpad('x',129,'x'));
insert into scott.t1  values (1,'x',lpad('x',129,'x'))
                                    *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T1"."VOBJECT_NAME" (actual: 129, maximum: 128)


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2921601/,如需轉載,請註明出處,否則將追究法律責任。

相關文章