Set up a friendly environment to share my understanding and ideas about Oracle / Oracle Spatial database administration, ESRI ArcSDE Geodatabase administration and UNIX (Solaris) operating system.
Thursday, September 4, 2008
DBA_TAB_COLS
Oracle 11gR1
DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database. This view differs from "DBA_TAB_COLUMNS" in that hidden columns are not filtered out. Its columns are the same as those in "ALL_TAB_COLS". To gather statistics for this view, use the SQL ANALYZE statement.
Related Views
·ALL_TAB_COLS describes the columns of the tables, views, and clusters accessible to the current user. To gather statistics for this view, use the ANALYZE SQL statement or the DBMS_STATS package.
·USER_TAB_COLS describes the columns of the tables, views, and clusters owned by the current user. This view does not display the OWNER column.
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
NOT NULL
Owner of the table, view, or cluster
TABLE_NAME
VARCHAR2(30)
NOT NULL
Name of the table, view, or cluster
COLUMN_NAME
VARCHAR2(30)
NOT NULL
Column name
DATA_TYPE
VARCHAR2(106)
Datatype of the column
DATA_TYPE_MOD
VARCHAR2(3)
Datatype modifier of the column
DATA_TYPE_OWNER
VARCHAR2(30)
Owner of the datatype of the column
DATA_LENGTH
NUMBER
NOT NULL
Length of the column (in bytes)
DATA_PRECISION
NUMBER
Decimal precision for NUMBER datatype; binary precision for FLOAT datatype, null for all other datatypes
DATA_SCALE
NUMBER
Digits to right of decimal point in a number
NULLABLE
VARCHAR2(1)
Specifies whether a column allows NULLs. 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 default value for the column
DATA_DEFAULT
LONG
Default value for the column
NUM_DISTINCT
NUMBER
Number of distinct values in the column
These columns remain for backward compatibility with Oracle7. This information is now in the {TAB|PART}_COL_STATISTICS views.
LOW_VALUE
RAW(32)
Low value in the column
These columns remain for backward compatibility with Oracle7. This information is now in the {TAB|PART}_COL_STATISTICS views.
HIGH_VALUE
RAW(32)
High value in the column
These columns remain for backward compatibility with Oracle7. This information is now in the {TAB|PART}_COL_STATISTICS views.
DENSITY
NUMBER
Density of the column
These columns remain for backward compatibility with Oracle7. This information is now in the {TAB|PART}_COL_STATISTICS views.
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 SQL statement ANALYZE. However, the 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, the 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 or NCHAR_CS
CHAR_COL_DECL_LENGTH
NUMBER
Length
GLOBAL_STATS
VARCHAR2(3)
For partitioned tables, indicates whether column statistics were collected for the table as a whole (YES) or were estimated from statistics on underlying partitions and subpartitions (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 datatypes:
·CHAR
·VARCHAR2
·NCHAR
·NVARCHAR
CHAR_USED
VARCHAR2(1)
B | C. B indicates that the column uses BYTE length semantics. C indicates that the column uses CHAR length semantics. NULL indicates the datatype is not any of the following:
·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)
No comments:
Post a Comment