Oracle 表的元数据相关

元数据, metadata, 即描述数据的数据。

desc

在 Oracle 中，可以使用 desc tablename 的命令查看一个表的各字段的信息，如：

注释

手工标识表注释的方式：

comment on table DBD_ROAD is '路线表';

手工标识字段注释的方式：

comment on column EMP.empno is 'employee no.';

comment on column EMP.sal is 'salary';

查询元信息

示例代码：

查看 EMP 表的元信息

select * from dba_tab_columns t where t.TABLE_NAME = 'EMP'

dba_tab_columns 表的字段有：

SQL> desc dba_tab_columns

Name Type Nullable Default Comments

-------------------- ------------- -------- ------- --------------------------------------------------------------------

OWNER VARCHAR2(30)

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

DATA_PRECISION NUMBER Y Length: decimal digits (NUMBER) or binary digits (FLOAT)

DATA_SCALE NUMBER Y Digits to right of decimal point in a number

NULLABLE VARCHAR2(1) Y Does column allow NULL values?

COLUMN_ID NUMBER Y Sequence number of the column as created

DEFAULT_LENGTH NUMBER Y Length of default value for the column

DATA_DEFAULT LONG Y Default value for the column

NUM_DISTINCT NUMBER Y The number of distinct values in the column

LOW_VALUE RAW(32) Y The low value in the column

HIGH_VALUE RAW(32) Y The high value in the column

DENSITY NUMBER Y The density of the column

NUM_NULLS NUMBER Y The number of nulls in the column

NUM_BUCKETS NUMBER Y The number of buckets in histogram for the column

LAST_ANALYZED DATE Y The date of the most recent time this column was analyzed

SAMPLE_SIZE NUMBER Y The sample size used in analyzing this column

CHARACTER_SET_NAME VARCHAR2(44) Y Character set name

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 column in bytes

CHAR_LENGTH NUMBER Y The maximum length of the column in characters

CHAR_USED VARCHAR2(1) Y C if the width was specified in characters, B if in bytes

V80_FMT_IMAGE VARCHAR2(3) Y Is column data in 8.0 image format?

DATA_UPGRADED VARCHAR2(3) Y Has column data been upgraded to the latest type version format?

HISTOGRAM VARCHAR2(15) Y

注：对于普通权限的用户，可以通过查询 user_tab_columns:

select table_name, column_name from user_tab_columns where table_name=upper('table_name');

参：