Oracle data table structure/field/type/size Query

Oracle Database dictionary
In most data dictionary views of Oracle, there are view families like DBA_TABLES, ALL_TABLES, and USER_TABLES. There are more than 100 view families in Oracle. The following table lists the most important and commonly used view families. Note that each view family has a DBA _, one ALL _ and one USER _ view. View family www.2cto.com description COL_PRIVS contains the table's column permissions, including the grants, grants, and permission EXTENTS data range information, such as data files, Data Segment names (segment_name), and size INDEXES index information, for example, the type, uniqueness, and information of the IND_COLUMNS index column of the involved table, such as the OBJECTS object information of the column sorting method on the index, such as the State and DDL time www.2cto.com ROLE_PRIVS role permissions, for example, the data segment information of the SEGMENTS table and index of the GRANT and ADMIN options, such as tablespace and storage SEQUECNCES sequence information, for example, the sequence cache, cycle, and ast_number SOURCE except for all built-in processes, functions, and package SOURCE code SYNONYMS alias information of the trigger, such as the referenced object and database link db_link SYS_PRIVS system permissions, such as gran The column information of tee, privilege, and admin TAB_COLUMNS TABLES and views, including the TAB_PRIVS table permissions of the column data type, such as the authorizer, grantee, and permission TABLES Table information, such as table space ), TRIGGERS trigger information of storage parms and number of data rows, such as type, event, trigger body www.2cto.com USERS user information, for example, temporary and default table space VIEWS, including view definitions, and some infrequently used data dictionary tables in Oracle, are not really dictionary families, they are all important single views. These views are shown in the following table: View Name Description: Lists applications of the Oracle database dictionary granted by users with column permissions granted by users: USER_COL_PRIVS_RECD column permissions granted by users USER_TAB_PRIVS_RECD
With the help of Oracle data dictionary and Oracle DDL statements, we can do a lot of things. Almost all Oracle development auxiliary tools are designed with this. The author will explain how to obtain the field information of the database table.
First, we define a database table. The database table structure is as follows: database Table NAME [TABLE_TEST] www.2cto.com field NAME data type length default value allow null primary key comment NAME VARCHAR2 40 n y name *** VARCHAR2 1 ''' Y ''' N gender BIRTHDAY DATE 0 Y birthday height number 3, 1 y height weight number 3, 1 y weight memo blob 0 Y remarks the SQL statement for creating a table is as follows -- create table TABLE_TEST (www.2cto.com NAME varchar2 (40) not null, *** varchar2 (1) default ''' Y ''' not null, BIRTHDAY date not null, HEIGHT number (3, 2), WEIGHT num Ber (3, 2), MEMO blob); -- add comment on column TABLE_TEST.NAME is ''' name ''' to the column; comment on column TABLE_TEST. * ** is ''' 'gender '''; comment on column TABLE_TEST.BIRTHDAY is ''' 'birthday ''''; comment on column TABLE_TEST.HEIGHT is '''height '''; comment on column TABLE_TEST.WEIGHT is '''''weight ''''; comment on column TABLE_TEST.MEMO is ''' comment '''; -- create the constraint Relation primary key foreign key other alter table TABLE_TEST add constraint TB_TEST_P_NA ME primary key (NAME); after the data table is created, run the following SQL statement: select. column_name field name,. data_type data type,. data_length,. data_precision integer,. data_Scale decimal point,. nullable allows null values.. the default value of Data_default is B. comments remarks from user_tab_columns A, user_col_comments Bwhere www.2cto.com. table_Name = B. table_Name and. column_Name = B. column_Name and. table_Name = '''''table _ test'''. the following result is displayed: the data type length of the field name is an integer. The decimal places allow null values. The default value is NAMEVARCHAR240. N <Long> name *** VARCHAR21 N <Long> gender BIRTHDAYDATE7 www.2cto.com N <Long> birthday HEIGHTNUMBER2232Y <Long> height WEIGHTNUMBER2232Y <Long> weight MEMOBLOB4000 Y <Long> remarks, when designing a program, we can use a simple SQL statement to call Word through Ole to export the complete database table dictionary document for the end user.
Run the following SQL statement: select INDEX_NAME index name, INDEX_TYPE index type, and UNIQUENESS index category from user_indexeswhere TABLE_NAME = ''' TABLE _ test'''. The result is as follows (note: SYS_IL0000031226C00006 $ index is automatically created when the system creates a database table for the maintenance of the database table content): www.2cto.com index name index type index category 1SYS_IL0000031226C00006 $ explain execute the following SQL statement, we will get more information about the database table structure: select. column_name field name,. data_type data type,. data_length,. data_precision integer,. data_Scale decimal point,. Nullable allows null values.. the default value of Data_default is B. comments remarks, C. indexCount index times from user_tab_columns A, user_col_comments B, (select count (*) IndexCount, Column_Name from User_Ind_Columns where Table_Name = '''table _ test''' 'group by Column_Name) cwhere. table_Name = B. table_Name and. column_Name = B. column_Name and. column_Name = C. column_Name (+) and. table_Name = ''' TABLE _ TEST '''. The result is as follows: www.2cto.com. Name Data Type Length Integer decimal places allow null default value remarks index times BIRTHDAYDATE7 N <Long> birthday HEIGHTNUMBER2232Y <Long> height MEMOBLOB4000 Y <Long> remarks NAMEVARCHAR240 N <Long> name 1 *** VARCHAR21 N <Long> gender WEIGHTNUMBER2232Y <Long> weight of course, Oracle Data Dictionary is widely used, with the support of the Oracle database dictionary, We can get all the information about the Oracle database structure. The famous database development tool PL/SQL Developer is fully implemented based on the Oracle database dictionary. Author quyuanbo103