//我们常常使用desc命令查看表结构:
SQL> desc scott.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 o.obj#,o.name object_name,
c.column_id,
c.column_name,
c.data_type,
c.data_length,
c.data_precision,
c.data_scale,
c.nullable
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'
order by obj#,column_id;
//
//有时候,我们闲着没事做,想知道我们建立的表中,所有字段所占用的空间,
//除了通过查询视图user_tab_columns,知道表中每个字段的长度精度,然后再计算外,我们有下面的方法:
//这个方法可以准确的计算出表中字段所占用的空间大小:
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;
/