Oracle查询数据表结构/字段/类型/大小

Oracle数据库字典     在Oracle的绝大多数数据字典视图中都有象DBA_TABLES,ALL_TABLES和USER_TABLES这样的视图家族。Oracle中有超过100个视图家族，下表列出了最重要和最常用的视图家族，需要注意的是每个视图家族都有一个DBA_,一个ALL_一个USER_视图。

 

视图家族	描述
COL_PRIVS	包含了表的列权限，包括授予者、被授予者和权限
EXTENTS	数据范围信息，比如数据文件，数据段名(segment_name)和大小
INDEXES	索引信息，比如类型、唯一性和被涉及的表
IND_COLUMNS	索引列信息，比如索引上的列的排序方式
OBJECTS	对象信息，比如状态和DDL time
ROLE_PRIVS	角色权限，比如GRANT和ADMIN选项
SEGMENTS	表和索引的数据段信息，比如tablespace和storage
SEQUECNCES	序列信息，比如序列的cache、cycle和ast_number
SOURCE	除触发器之外的所有内置过程、函数、包的源代码
SYNONYMS	别名信息，比如引用的对象和数据库链接db_link
SYS_PRIVS	系统权限，比如grantee、privilege、admin选项
TAB_COLUMNS	表和视图的列信息，包括列的数据类型
TAB_PRIVS	表权限，比如授予者、被授予者和权限
TABLES	表信息，比如表空间(tablespace),存储参数(storage parms)和数据行的数量
TRIGGERS	触发器信息，比如类型、事件、触发体(trigger body)
USERS	用户信息，比如临时的和缺省的表空间
VIEWS	视图信息，包括视图定义

 

在Oracle中还有一些不常用的数据字典表，但这些表不是真正的字典家族，他们都是一些重要的单一的视图。这些视图见下表:

 

视图名称	描述
USER_COL_PRIVS_MADE	用户授予他人的列权限
USER_COL_PRIVS_RECD	用户获得的列权限
USER_TAB_PRIVS_MADE	用户授予他人的表权限
USER_TAB_PRIVS_RECD	用户获得的表权限

 

Oracle数据库字典的应用借助Oracle数据字典，利用Oracle的DDL语句，我们可以做很多事情，几乎所有的Oracle开发辅助工具都是利用这一点进行设计的。作者将通过如何取得数据库表字段信息来说明。 首先我们定义一个数据库表，数据库表结构如下：

 

数据库表名[TABLE_TEST]
字段名	数据类型	长度	缺省值	允许空	主键	注释
NAME	VARCHAR2	40		N	Y	姓名
***	VARCHAR2	1	''''Y''''	N		性别
BIRTHDAY	DATE	0		Y		生日
HEIGHT	NUMBER	3，1		Y		身高
WEIGHT	NUMBER	3，1		Y		体重
MEMO	BLOB	0		Y		备注

 

创建表的SQL语句如下 -- 创建数据表

CREATE TABLE TABLE_TEST
(
   NAME   VARCHAR2 (40) NOT NULL,
   **     * VARCHAR2(1) DEFAULT 'Y'  NOT NULL,  
      BIRTHDAY date NOT NULL,     HEIGHT number(3,2),    
       WEIGHT number(3,2),     MEMO BLOB );

 

CREATE TABLE TABLE_TEST
(
   NAME   VARCHAR2 (40) NOT NULL,
   **     * VARCHAR2(1) DEFAULT 'Y'  NOT NULL,  
      BIRTHDAY date NOT NULL,     HEIGHT number(3,2),    
       WEIGHT number(3,2),     MEMO BLOB );

 

CREATE TABLE TABLE_TEST
(
   NAME   VARCHAR2 (40) NOT NULL,
   **     * VARCHAR2(1) DEFAULT 'Y'  NOT NULL,  
      BIRTHDAY date NOT NULL,     HEIGHT number(3,2),    
       WEIGHT number(3,2),     MEMO BLOB );

-- 给列添加备注  

 comment on column TABLE_TEST.NAME is ''''姓名''''; 
 comment on column TABLE_TEST.*** is ''''性别'''';  
 comment on column TABLE_TEST.BIRTHDAY is ''''生日'''';  
 comment on column TABLE_TEST.HEIGHT is ''''身高'''';  
 comment on column TABLE_TEST.WEIGHT is ''''体重'''';   
 comment on column TABLE_TEST.MEMO is ''''备注''''; 

 -- 创建约束关系 主键 外键 其他  

 alter table TABLE_TEST add constraint TB_TEST_P_NAME primary key (NAME);

 数据表创建完毕，执行下列SQL语句：

SELECT A.column_name 字段名,
       A.data_type 数据类型,
       A.data_length 长度,
       A.data_precision 整数位,
       A.Data_Scale 小数位,
       A.nullable 允许空值,
       A.Data_default 缺省值,
       B.comments 备注
  FROM user_tab_columns A, user_col_comments B
 WHERE     A.Table_Name = B.Table_Name
       AND A.Column_Name = B.Column_Name
       AND A.Table_Name = 'T_VST_GROUP'

 

我们可以得出一下结果：

 

字段名	数据类型	长度	整数位	小数位	允许空值	缺省值	备注
NAME	VARCHAR2	40			N		姓名
***	VARCHAR2	1			N		性别
BIRTHDAY	DATE	7			N		生日
HEIGHT	NUMBER	22	3	2	Y		身高
WEIGHT	NUMBER	22	3	2	Y		体重
MEMO	BLOB	4000			Y		备注

 

这样，我们在进行程序设计时，通过一条简单的SQL语句，然好通过Ole调用Word，便可为最终用户导出完整的数据库表字典文档。 再执行下列SQL语句:

SELECT INDEX_NAME 索引名, INDEX_TYPE 索引类型, UNIQUENESS 索引类别
  FROM user_indexes
 WHERE TABLE_NAME = 'T_VST_GROUP'

 

得到结果如下(注：SYS_IL0000031226C00006$$索引为系统在创建数据库表时自动创建的，用于数据库表内容的维护):

 

	索引名	索引类型	索引类别
1	SYS_IL0000031226C00006$$	LOB	UNIQUE
2	TB_TEST_P_NAME	NORMAL	UNIQUE

 

 

执行下列SQL语句，我们将得到更多的关于数据库表结构的信息：

SELECT A.column_name 字段名,
       A.data_type 数据类型,
       A.data_length 长度,
       A.data_precision 整数位,
       A.Data_Scale 小数位,
       A.nullable 允许空值,
       A.Data_default 缺省值,
       B.comments 备注,
       C.IndexCount 索引次数
  FROM user_tab_columns A,
       user_col_comments B,
       (  SELECT COUNT (*) IndexCount, Column_Name
            FROM User_Ind_Columns
           WHERE Table_Name = 'T_VST_GROUP'
        GROUP BY Column_Name) C
 WHERE     A.Table_Name = B.Table_Name
       AND A.Column_Name = B.Column_Name
       AND A.Column_Name = C.Column_Name(+)
       AND A.Table_Name = 'T_VST_GROUP'

 

得到结果如下:

 

字段名	数据类型	长度	整数位	小数位	允许空值	缺省值	备注	索引次数
BIRTHDAY	DATE	7			N		生日
HEIGHT	NUMBER	22	3	2	Y		身高
MEMO	BLOB	4000			Y		备注
NAME	VARCHAR2	40			N		姓名	1
***	VARCHAR2	1			N		性别
WEIGHT	NUMBER	22	3	2	Y		体重

    当然Oracle数据字典的应用远不止这些，通过Oracle数据库字典的支持，我们可以得到Oracle数据库结构的所有信息，著名的数据库开发工具PL/SQL Developer完全就是基于Oracle的数据库字典实现的。