Oracle教程 2023-02-24

Oracle 视图 DBA_TAB_COLS 官方解释，作用，如何使用详细说明

本站中文解释

DBA_TAB_COLS 是 Oracle 中的一个基础查询视图。它的目的是提供关于数据库中所有表、各列的元数据信息。

DBA_TAB_COLS 视图提供了以下信息：

＊表名
＊表所在的用户
＊列（字段）名
＊表列拥有的数据类型（包括字符串长度和列宽）
＊列的注释
＊默认值
＊表空间
＊是否可为 null
＊列 ID

使用者可以使用DBA_TAB_COLS视图读取一张表中所有列的元数据信息。它可帮助数据库管理员分析服务中究竟存在什么表，以及这些表的结构。

使用方法：

例如，查询一张表的所有列的元数据信息，可使用下面的 SQL 语句：

SELECT * FROM DBA_TAB_COLS WHERE table_name=’table_name’;

其中，table_name应替换成实际的表名。

官方英文解释

DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database.

Its columns (except for SENSITIVE_COLUMN) are the same as those in ALL_TAB_COLS.

To gather statistics for this view, use the DBMS_STATS package.

This view differs from DBA_TAB_COLUMNS in that system-generated hidden columns are not filtered out.

Columns marked with an asterisk (*) in the table below remain for backward compatibility with Oracle7. This information is now in the [TAB|PART]_COL_STATISTICS views.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(128)`	`NOT NULL`	Owner of the table, view, or cluster
`TABLE_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the table, view, or cluster
`COLUMN_NAME`	`VARCHAR2(128)`	`NOT NULL`	Column name
`DATA_TYPE`	`VARCHAR2(128)`		Data type of the column
`DATA_TYPE_MOD`	`VARCHAR2(3)`		Data type modifier of the column
`DATA_TYPE_OWNER`	`VARCHAR2(128)`		Owner of the data type of the column
`DATA_LENGTH`	`NUMBER`	`NOT NULL`	Length of the column (in bytes)
`DATA_PRECISION`	`NUMBER`		Decimal precision for `NUMBER` data type; binary precision for `FLOAT` data type; NULL for all other data types
`DATA_SCALE`	`NUMBER`		Digits to the right of the decimal point in a number
`NULLABLE`	`VARCHAR2(1)`		Indicates whether a column allows NULLs. The value is `N` if there is a `NOT NULL` constraint on the column or if the column is part of a `PRIMARY KEY`.
`COLUMN_ID`	`NUMBER`		Sequence number of the column as created
`DEFAULT_LENGTH`	`NUMBER`		Length of the default value for the column
`DATA_DEFAULT`	`LONG`		Default value for the column
`NUM_DISTINCT*`	`NUMBER`		Number of distinct values in the column
`LOW_VALUE*`	`RAW(1000)`		Low value in the column
`HIGH_VALUE*`	`RAW(1000)`		High value in the column
`DENSITY*`	`NUMBER`		If a histogram is available on `COLUMN_NAME`, then this column displays the selectivity of a value that spans fewer than 2 endpoints in the histogram. It does not represent the selectivity of values that span 2 or more endpoints. If a histogram is not available on `COLUMN_NAME`, then the value of this column is `1`/`NUM_DISTINCT`.
`NUM_NULLS`	`NUMBER`		Number of NULLs in the column
`NUM_BUCKETS`	`NUMBER`		Number of buckets in the histogram for the column Note: The number of buckets in a histogram is specified in the `SIZE` parameter of the `ANALYZE` SQL statement. However, Oracle Database does not create a histogram with more buckets than the number of rows in the sample. Also, if the sample contains any values that are very repetitious, Oracle Database creates the specified number of buckets, but the value indicated by this column may be smaller because of an internal compression algorithm.
`LAST_ANALYZED`	`DATE`		Date on which this column was most recently analyzed
`SAMPLE_SIZE`	`NUMBER`		Sample size used in analyzing this column
`CHARACTER_SET_NAME`	`VARCHAR2(44)`		Name of the character set: `CHAR_CS` `NCHAR_CS`
`CHAR_COL_DECL_LENGTH`	`NUMBER`		Declaration length of the character type column
`GLOBAL_STATS`	`VARCHAR2(3)`		`GLOBAL_STATS` will be `YES` if statistics are gathered or incrementally maintained, otherwise it will be `NO`
`USER_STATS`	`VARCHAR2(3)`		Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)
`AVG_COL_LEN`	`NUMBER`		Average length of the column (in bytes)
`CHAR_LENGTH`	`NUMBER`		Displays the length of the column in characters. This value only applies to the following data types: `CHAR` `VARCHAR2` `NCHAR` `NVARCHAR2`
`CHAR_USED`	`VARCHAR2(1)`		Indicates that the column uses `BYTE` length semantics (`B`) or `CHAR` length semantics (`C`), or whether the data type is not any of the following (NULL): `CHAR` `VARCHAR2` `NCHAR` `NVARCHAR2`
`V80_FMT_IMAGE`	`VARCHAR2(3)`		Indicates whether the column data is in release 8.0 image format (`YES`) or not (`NO`)
`DATA_UPGRADED`	`VARCHAR2(3)`		Indicates whether the column data has been upgraded to the latest type version format (`YES`) or not (`NO`)
`HIDDEN_COLUMN`	`VARCHAR2(3)`		Indicates whether the column is a hidden column (`YES`) or not (`NO`)
`VIRTUAL_COLUMN`	`VARCHAR2(3)`		Indicates whether the column is a virtual column (`YES`) or not (`NO`)
`SEGMENT_COLUMN_ID`	`NUMBER`		Sequence number of the column in the segment
`INTERNAL_COLUMN_ID`	`NUMBER`	`NOT NULL`	Internal sequence number of the column
`HISTOGRAM`	`VARCHAR2(15)`		Indicates existence/type of histogram: `NONE` `FREQUENCY` `TOP-FREQUENCY` `HEIGHT BALANCED` `HYBRID`
`QUALIFIED_COL_NAME`	`VARCHAR2(4000)`		Qualified column name
`USER_GENERATED`	`VARCHAR2(3)`		Indicates whether the column is a user-generated column (`YES`) or a system-generated column (`NO`)
`DEFAULT_ON_NULL`	`VARCHAR2(3)`		Indicates whether the column has DEFAULT ON NULL semantics (`YES`) or not (`NO`)
`IDENTITY_COLUMN`	`VARCHAR2(3)`		Indicates whether this is an identity column (`YES`) or not (`NO`)
`SENSITIVE_COLUMN`	`VARCHAR2(3)`		Indicates whether this is a sensitive column (`YES`) or not (`NO`)
`EVALUATION_EDITION`	`VARCHAR2(128)`		Name of the edition in which editioned objects referenced in an expression column are resolved
`UNUSABLE_BEFORE`	`VARCHAR2(128)`		Name of the oldest edition in which the index may be used as part of a query plan
`UNUSABLE_BEGINNING`	`VARCHAR2(128)`		Name of the edition for which the index may not be used as part of a query plan in this edition or any of its descendants
`COLLATION`	`VARCHAR2(100)`		Collation for the column. Only applies to columns with character data types.
`COLLATED_COLUMN_ID`	`NUMBER`		Internal sequence number of a column, for which this virtual column generates a collation key

本站中文解释

官方英文解释

相关推荐