[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2921601/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
