SELECT ATC.OWNER,
ATC.TABLE_NAME,
UTC.COMMENTS,
ATC.COLUMN_NAME,
ATC.DATA_TYPE,
ATC.DATA_LENGTH,
ATC.NULLABLE,
UCC.COMMENTS
FROM (SELECT ATC.OWNER,
ATC.TABLE_NAME,
ATC.COLUMN_NAME,
ATC.DATA_TYPE,
ATC.DATA_LENGTH,
ATC.NULLABLE
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.OWNER IN ('GTJA21')) ATC
LEFT OUTER JOIN USER_COL_COMMENTS UCC
ON ATC.TABLE_NAME = UCC.TABLE_NAME
AND ATC.COLUMN_NAME = UCC.COLUMN_NAME
LEFT OUTER JOIN USER_TAB_COMMENTS UTC
ON ATC.TABLE_NAME = UTC.TABLE_NAME
ORDER BY ATC.TABLE_NAME,
ATC.COLUMN_NAME
--多个用户下的表明細+表注釋+字段明細+字段注釋
SELECT ATC.OWNER,
ATC.TABLE_NAME,
ATC.COLUMN_NAME,
UTC.COMMENTS,
ATC.DATA_TYPE,
ATC.DATA_LENGTH,
ATC.NULLABLE,
UCC.COMMENTS
FROM (SELECT ATC.OWNER,
ATC.TABLE_NAME,
ATC.COLUMN_NAME,
ATC.DATA_TYPE,
ATC.DATA_LENGTH,
ATC.NULLABLE
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.OWNER IN ('UserName1', 'UserName2')) ATC
LEFT OUTER JOIN ALL_COL_COMMENTS UCC
ON ATC.TABLE_NAME = UCC.TABLE_NAME
AND ATC.COLUMN_NAME = UCC.COLUMN_NAME
LEFT OUTER JOIN ALL_TAB_COMMENTS UTC
ON ATC.TABLE_NAME = UTC.TABLE_NAME
ORDER BY ATC.TABLE_NAME, ATC.COLUMN_NAME