这两个属性都属于user_tab_columns视图,他们的含义:
data_length:当前列数据类型的字节长度
如:EMPNO NUMBER(4) 22
ENAME VARCHAR2(10) 10
data_precision:列相关数据类型(数字类型)的具体长度(有效位数),一个十进制数(NUMBER类型),或一个二进制树(FLOAT类型)
如:SAL NUMBER(7,2) 7
//
来看看user_tab_columns视图的相关信息,我们主要关注Name,Comments两列和带注释的三行:
desc user_tab_columns;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- --------------------------------------------------------------------
TABLE_NAME VARCHAR2(30) Table, view or cluster name
COLUMN_NAME VARCHAR2(30) Column name
DATA_TYPE VARCHAR2(106) Y Datatype of the column
DATA_TYPE_MOD VARCHAR2(3) Y Datatype modifier of the column
DATA_TYPE_OWNER VARCHAR2(30) Y Owner of the datatype of the column
DATA_LENGTH NUMBER Length of the columninbytes/*列的数据类型的字节长度*/
DATA_PRECISION NUMBER Y Length:decimaldigits (NUMBER) or binary digits (FLOAT)/*数字类型的实际长度*/
DATA_SCALE NUMBER Y Digits to right ofdecimalpointina number/*小数点位数*/
NULLABLE VARCHAR2(1) Y Does column allow NULL values?
COLUMN_ID NUMBER Y Sequence number of the columnascreated
DEFAULT_LENGTH NUMBER Y Length ofdefaultvalueforthe column
DATA_DEFAULT LONG Y Default valueforthe column
NUM_DISTINCT NUMBER Y The number of distinct valuesinthe column
LOW_VALUE RAW(32) Y The low valueinthe column
HIGH_VALUE RAW(32) Y The high valueinthe column
DENSITY NUMBER Y The density of the column
NUM_NULLS NUMBER Y The number of nullsinthe column
NUM_BUCKETS NUMBER Y The number of bucketsinhistogramforthe column
LAST_ANALYZED DATE Y The date of the most recent timethiscolumn was analyzed
SAMPLE_SIZE NUMBER Y The sample size usedinanalyzingthiscolumn
CHARACTER_SET_NAME VARCHAR2(44) Y Charactersetname
CHAR_COL_DECL_LENGTH NUMBER Y Declaration length of character type column
GLOBAL_STATS VARCHAR2(3) Y Are the statistics calculated without merging underlying partitions?
USER_STATS VARCHAR2(3) Y Were the statistics entered directly by the user?
AVG_COL_LEN NUMBER Y The average length of the columninbytes
CHAR_LENGTH NUMBER Y The maximum length of the columnincharacters
CHAR_USED VARCHAR2(1) Y Cismaximum length givenincharacters, Bifinbytes
V80_FMT_IMAGE VARCHAR2(3) Y Is column datain8.0 image format?
DATA_UPGRADED VARCHAR2(3) Y Has column data been upgraded to the latest type version format?
HISTOGRAM VARCHAR2(15) Y
//
实例1:查看emp表的列对应的data_length,data_precision,data_scale实际值
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
//
select column_name,data_type,data_length,data_precision,data_scale
from user_tab_columns where table_name='EMP';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------------ ------------------------------- ----------- -------------- ----------
EMPNO NUMBER 22 4 0
ENAME VARCHAR2 10
JOB VARCHAR2 9
MGR NUMBER 22 4 0
HIREDATE DATE 7
SAL NUMBER 22 7 2
COMM NUMBER 22 7 2
DEPTNO NUMBER 22 2 0