oracle查询数据库用户的表结构

oracle：查询数据库用户的表结构、所有字段，包含表名、字段名、字段含义，字段类型、是否必填、是否主键

--普通用户就能执行
SELECT  distinct(a.TABLE_NAME) as "表名",a.COLUMN_NAME as "字段名",b.COMMENTS as "含义",
       a.DATA_TYPE  || '(' || a.DATA_LENGTH || ')'  as "数据类型",
       ---内置视图中a.NULLABLE为Y表示非必填，这里用decode做一下转换
       decode(a.NULLABLE,'Y','N','N','Y','N') as "必填",
       ---查询主键
        case when a.COLUMN_NAME in (select
 col.column_name
from
 user_constraints con,user_cons_columns col
where
 con.constraint_name=col.constraint_name and con.CONSTRAINT_TYPE='P'
 and col.table_name=a.TABLE_NAME)  then 'Y'
 ELSE 'N' end "主键" 
from 
     ALL_TAB_COLUMNS a,ALL_COL_COMMENTS b --, user_constraints c,user_cons_columns d
WHERE 
       a.OWNER=b.OWNER AND a.TABLE_NAME=b.TABLE_NAME AND a.COLUMN_NAME=b.COLUMN_NAME    
     AND a.OWNER='CLEAR'--用户要大写
     --and a.TABLE_NAME=''
     order by a.TABLE_NAME ;