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

本站中文解释

Oracle视图DBA_TAB_COLUMNS用于显示所有表(包括表的结构和数据类型)的字段信息,也就是说,可以使用这个视图来显示某一张表的所有列信息,也可以显示某一个列的信息。

可以使用以下语句来查询所有表信息:

SELECT * FROM DBA_TAB_COLUMNS;

可以使用以下语句来查询某一张表中的所有列信息, 其中table_name参数表示要查询的表名:

SELECT * FROM DBA_TAB_COLUMNS WHERE table_name= table_name ;

可以使用以下语句来查询某一张表中某一个指定列的信息,其中table_name参数表示要查询的表名,column_name参数表示要查询的列名:

SELECT * FROM DBA_TAB_COLUMNS WHERE table_name= table_name AND column_name = column_name ;

官方英文解释

DBA_TAB_COLUMNS 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_COLUMNS.

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

This view filters out system-generated hidden columns. The DBA_TAB_COLS view does not filter out system-generated hidden columns.

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. The constraint should be in an ENABLE VALIDATE state.

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)

HISTOGRAM

VARCHAR2(15)

Indicates existence/type of histogram:

  • NONE

  • FREQUENCY

  • TOP-FREQUENCY

  • HEIGHT BALANCED

  • HYBRID

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.

See Also:

  • “ALL_TAB_COLUMNS”

  • “DBA_TAB_COLS”

  • Oracle Database PL/SQL
    Packages and Types Reference
    for more information about the DBMS_STATS package


数据运维技术 » Oracle 视图 DBA_TAB_COLUMNS 官方解释,作用,如何使用详细说明