SQL> desc dept; Name Type Nullable Default Comments ------ ------------ -------- ------- -------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) Y LOC VARCHAR2(13) Y
SQL> select data_length,data_precision,data_scale 2 from user_tab_columns 3 where table_name='DEPT';
select sum(DATA_LENGTH) from USER_TAB_COLUMNS WHERE TABLE_NAME = 't'
select object_name,sum(decode(data_type,'NUMBER',floor((col_length+1)/2) + 1,col_length)) table_size from (select object_name,column_name,data_type,decode(data_precision,NULL,data_length,data_precision) col_length from (select o.name object_name,c.column_name column_name,c.data_type,c.data_length,c.data_precision,c.data_scale from sys.tab$ t,dba_tab_cols c,sys.obj$ o,sys.user$ u where o.obj#=t.obj# and o.name=c.table_name and c.owner=u.name and o.owner#=u.user# and u.name='SCOTT' /*and o.name='EMP'*/ ) tab_cols ) col_len group by object_name;
select sum(size) from ( select length(a)+length(b)+length(c) as size from t )
谢谢大家的回答 尤其是BOBO12082119,oracle里面2个数字位只占一个字节是吧?
SQL> 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 column in bytes --byte --一个字符2个字节,一个字节8个位
SQL> desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL> select data_length,data_precision,data_scale
2 from user_tab_columns
3 where table_name='DEPT';
DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
22 2 0
14
13
oracle data_length和data_precision的区别
select object_name,sum(decode(data_type,'NUMBER',floor((col_length+1)/2) + 1,col_length)) table_size
from
(select object_name,column_name,data_type,decode(data_precision,NULL,data_length,data_precision) col_length
from
(select o.name object_name,c.column_name column_name,c.data_type,c.data_length,c.data_precision,c.data_scale
from sys.tab$ t,dba_tab_cols c,sys.obj$ o,sys.user$ u
where o.obj#=t.obj#
and o.name=c.table_name
and c.owner=u.name
and o.owner#=u.user#
and u.name='SCOTT'
/*and o.name='EMP'*/
) tab_cols
) col_len
group by object_name;
from
(
select length(a)+length(b)+length(c) as size
from t
)
SQL> 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 column in bytes --byte
--一个字符2个字节,一个字节8个位
你对数字的做法是用(data_precision + 1)/2 + 1 这是什么情况呢?
--数值number(p,s)占用得空间为:
--length = floor [(p+1)/2] + 1
ANALYZE TABLE 表名 COMPUTE STATISTICS;
SELECT t.TABLE_NAME, t.AVG_ROW_LEN
FROM user_tables t
WHERE t.TABLE_NAME = UPPER('表名');