Oracle12.2 SQL言語リファレンス COMMENT 使用されるOracleのバージョンのマニュアルを確認してからお使いください。
comment_on_table.sql
--
--  COMMENT ON TABLE 文, COMMENT ON MATERIALIZED VIEW 文取得
--
-- history
--    2018/04/20 PKG_USERS_SCHEMA 対応  かなり遅くなったような？
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 500
SET PAGESIZE 10000
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL comment_on_table_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL comment_on_table_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
COLUMN COMMENT_ON_TABLE FORMAT A1000
--
-- COMMENT ON MATERIALIZED VIEW, TABLE
--
SELECT
    CASE
        WHEN A.COMMENTS IS NULL                -- まだコメントがない
        OR   A.TABLE_NAME LIKE 'MLOG\$_%'      -- MATERIALIZED VIEW LOG
        OR   A.TABLE_NAME LIKE 'RUPD\$_%' THEN -- temporary updatable snapshot log
            '-- '  -- コメント行に
    END
 || 'COMMENT ON '
 || RPAD(A.TABLE_TYPE, 18)            -- ↓桁数は現場対応
 || RPAD(A.OWNER || '.' || A.TABLE_NAME, 45) || ' IS '''
 || A.COMMENTS || ''' ;' AS COMMENT_ON_TABLE
FROM(
    SELECT
        NVL(OB.OBJECT_TYPE, TM.TABLE_TYPE) AS TABLE_TYPE
      , TA.OWNER
      , TA.TABLE_NAME
      , TA.TEMPORARY
      , TO_SINGLE_BYTE(TRIM(NVL(TM.COMMENTS, MM.COMMENTS))) AS COMMENTS
    FROM(
        SELECT COLUMN_VALUE AS OWNER FROM  TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA)  -- ユーザーが作成したスキーマのみ
         ) MS
    INNER JOIN DBA_TABLES TA
    ON  TA.OWNER = MS.OWNER
    LEFT OUTER JOIN DBA_OBJECTS OB
    ON  OB.OWNER       = MS.OWNER
    AND OB.OWNER       = TA.OWNER
    AND OB.OBJECT_NAME = TA.TABLE_NAME
    LEFT OUTER JOIN DBA_TAB_COMMENTS TM
    ON  TM.OWNER      = MS.OWNER
    AND TM.OWNER      = TA.OWNER
    AND TM.TABLE_NAME = TA.TABLE_NAME
    LEFT OUTER JOIN DBA_MVIEW_COMMENTS MM
    ON  MM.OWNER      = MS.OWNER
    AND MM.OWNER      = TA.OWNER
    AND MM.MVIEW_NAME = TA.TABLE_NAME
    WHERE
        TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
    AND TA.TABLE_NAME NOT IN
        (
        'CHAINED_ROWS'  -- except Oracle side table
      , 'EXCEPTIONS'
      , 'PLAN_TABLE'
      , 'PLAN_TABLE$'
        )
    AND OB.OBJECT_TYPE IN('MATERIALIZED VIEW', 'TABLE')
    -- AND TM.COMMENTS IS NOT NULL  -- コメントが設定されていないテーブルのみ
    ) A
ORDER BY 
    A.OWNER
  , A.TABLE_NAME
  , A.TABLE_TYPE ;
SPOOL OFF
COLUMN COMMENT_ON_TABLE CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT ON
参考
Oracle 純粋にユーザー側のスキーマのみを取得