SELECT 
DT.OWNER 用户名,
DT.TABLE_NAME 表名,
DT.TABLE_COMMENTS 表注释,
DT.TABLESPACE_NAME 所属表空间,
DT.TABLE_PK 表主键,
DTC.COLUMN_NAME 字段名,
CASE 
WHEN DTC.DATA_PRECISION IS NOT NULL AND DTC.DATA_SCALE IS NOT NULL THEN DTC.DATA_TYPE||'('||DTC.DATA_PRECISION||','||DTC.DATA_SCALE||')'
WHEN DTC.DATA_TYPE LIKE '%TIME%' THEN DTC.DATA_TYPE||'('||DTC.DATA_SCALE||')'
WHEN DATA_TYPE LIKE '%INT%' OR DATA_TYPE IN ('BIT','BLOB','CLOB','TEXT','DATE','IMAGE','LONGVARCHAR') THEN DATA_TYPE
ELSE DTC.DATA_TYPE||'('||DTC.DATA_LENGTH||')'
END 字段类型,
CC.COMMENTS 字段注释,
CASE WHEN DIC.COLUMN_NAME IS NULL THEN '否' ELSE '是' END 是否有索引,
CASE WHEN DTC.NULLABLE='N' THEN '否' ELSE '是' END 是否可为空,
DTC.DATA_DEFAULT 默认值
FROM(

SELECT 
DT.OWNER OWNER,
DT.TABLE_NAME TABLE_NAME,
TC.COMMENTS TABLE_COMMENTS,
DT.TABLESPACE_NAME TABLESPACE_NAME,
LISTAGG(DCC.COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) TABLE_PK
FROM DBA_TABLES DT
LEFT JOIN DBA_TAB_COMMENTS TC ON DT.OWNER=TC.OWNER AND DT.TABLE_NAME=TC.TABLE_NAME
LEFT JOIN DBA_CONSTRAINTS DC ON DT.OWNER=DC.OWNER AND DT.TABLE_NAME=DC.TABLE_NAME AND DC.CONSTRAINT_TYPE='P'
LEFT JOIN DBA_CONS_COLUMNS DCC ON DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME
WHERE DT.OWNER='SYSDBA' 
GROUP BY DT.OWNER,DT.TABLE_NAME,TC.COMMENTS,DT.TABLESPACE_NAME
)DT
LEFT JOIN DBA_TAB_COLUMNS DTC ON DT.OWNER=DTC.OWNER AND DT.TABLE_NAME=DTC.TABLE_NAME
LEFT JOIN DBA_COL_COMMENTS CC ON DTC.OWNER=CC.OWNER AND DTC.TABLE_NAME=CC.TABLE_NAME AND DTC.COLUMN_NAME=CC.COLUMN_NAME
LEFT JOIN (SELECT DISTINCT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS)DIC ON DTC.OWNER=DIC.TABLE_OWNER AND DTC.TABLE_NAME=DIC.TABLE_NAME AND DTC.COLUMN_NAME=DIC.COLUMN_NAME
ORDER BY DT.OWNER,DT.TABLE_NAME

SELECT TAB.NAME TABLE_NAME ,
          COLS.NAME COLUMN_NAME,
          COLS.TYPE$ DATA_TYPE,
          --SF_COL_IS_IDX_KEY(INDS.KEYNUM, INDS.KEYINFO, COLS.COLID) COLUMN_KEY ,
          CASE COLS.INFO2 WHEN '1' THEN 'auto_increment' ELSE '' END EXTRA,
          NULL COLLATION_NAME,
          COLS.COLID,
          concat(cols.TYPE$,'(',COLS.LENGTH$,')') COLUMN_TYPE 
     FROM(SELECT ID,
                  SCHID,
                  NAME
             FROM SYS.SYSOBJECTS 
            WHERE TYPE$ = 'SCHOBJ' AND SUBTYPE$ = 'UTAB' and name not in ('##HISTOGRAMS_TABLE','##PLAN_TABLE') and name not like 'BAK_DMINI%') TAB 
LEFT JOIN SYS.SYSCOLUMNS COLS
       on TAB.ID=COLS.ID
LEFT JOIN (SELECT ID, PID, NAME FROM SYS.SYSOBJECTS WHERE SUBTYPE$='INDEX') OBJ_INDS 
       on TAB.ID = OBJ_INDS.PID 
LEFT JOIN SYS.SYSINDEXES INDS 
       on INDS.ID= OBJ_INDS.ID
    WHERE TAB.SCHID = (SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE$ = 'SCH' AND NAME = 'SYSDBA' ) --and (INDS.XTYPE& 0x01) = 1 
 ORDER BY TAB.NAME,COLS.COLID