oracle 查询表结构并导出EXCEL

SELECT B.TABLE_NAME     AS "表名",

       C.COMMENTS       AS "表说明",
       B.COLUMN_ID      AS "字段序号",
       B.COLUMN_NAME    AS "字段名",
       B.DATA_TYPE      AS "字段数据类型",
       B.DATA_LENGTH    AS "数据长度",
       B.DATA_PRECISION AS "整数位",
       B.DATA_SCALE     AS "小数位",
       A.COMMENTS       AS "字段说明"
  FROM ALL_COL_COMMENTS A, ALL_TAB_COLUMNS B, ALL_TAB_COMMENTS C

WHERE A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)
   AND A.OWNER = B.OWNER
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
   AND C.TABLE_NAME = A.TABLE_NAME
   AND C.OWNER = A.OWNER
   AND A.OWNER = 'PYE'
ORDER BY A.TABLE_NAME, B.COLUMN_ID;

另外一些辅助查询：

SELECT * FROM ALL_USERS WHERE USERNAME='PYE'; --查询用户
SELECT * from user_tables　where table_name like '%TJ%'; --模糊查询表明


select owner from dba_tables where table_name='TJ_T_TJTCDJB'; --查询表拥有者


select (select a.table_name
          from user_constraints a
         where a.constraint_name = c.r_constraint_name) 主表表名,
       c.r_constraint_name 主表主键键约束名,
       c.table_name 从表表名,
       c.constraint_name 从表外键约束名
  from user_constraints c
 where c.constraint_type = 'R'; --查询表的主外键

select * from user_cons_columns  --查询表的约束

select * from user_tab_columns --查询表的字段信息