oracle导出表结构语句（包含注释加主键）

SELECT   a.table_name
        , c.comments
        , a.column_name
        , b.comments
        , a.data_type
        , a.data_length, a.column_id
        , listagg(to_char(d.position)) within group(order by d.position)  as position
     FROM user_tab_cols                 a
LEFT JOIN user_col_comments             b
       ON a.table_name  = b.table_name
      AND a.column_name = b.column_name
LEFT JOIN user_tab_comments             c
       ON c.table_name = b.table_name
LEFT JOIN
          (SELECT ucc.table_name
                  , ucc.column_name
                  , ucc.position
               FROM user_cons_columns   ucc
          LEFT JOIN user_constraints    uc
                 ON ucc.constraint_name = uc.constraint_name
                AND uc.constraint_type  = UPPER('p')
          )                             d
       ON d.table_name  = b.table_name
      AND d.column_name = b.column_name
         where a.TABLE_NAME like 'TB%'
        group by  a.table_name
        , c.comments
        , a.column_name
        , b.comments
        , a.data_type
        , a.data_length, a.column_id
 ORDER BY a.table_name,
         a.column_id asc ;

上述语句主要运用了listagg函数进行了重复的行合并处理，因为主键的行会重复，把position进行了合并。