列照合 - mieyanman @ ウィキ

WITH
T1 AS
(
SELECT OWNER

     ,TABLE_NAME
     ,COLUMN_NAME
     ,DATA_TYPE
     ,DATA_LENGTH
     ,DATA_PRECISION
     ,DATA_SCALE
     ,NULLABLE
     ,COLUMN_ID
 FROM DBA_TAB_COLUMNS
 WHERE TABLE_NAME NOT LIKE '%$%'

)
,
T2 AS
(
SELECT USERNAME

 FROM DBA_USERS
 WHERE USERNAME NOT IN
         ('SYS'
         ,'OUTLN'
         ,'DIP'
         ,'ORACLE_OCM'
         ,'DBSNMP'
         ,'APPQOSSYS'
         ,'CTXSYS'
         ,'XDB'
         ,'ANONYMOUS'
         ,'MDSYS'
         ,'HR'
         ,'FLOWS_FILES'
         ,'APEX_PUBLIC_USER'
         ,'APEX_040000'
         ,'XS$NULL'
         )

)
SELECT T1.OWNER

     ,T1.TABLE_NAME
     ,T1.COLUMN_NAME
     ,T1.DATA_TYPE
     ,T1.DATA_LENGTH
     ,T1.DATA_PRECISION
     ,T1.DATA_SCALE
     ,T1.NULLABLE
     ,T1.COLUMN_ID
 FROM T1
   INNER JOIN T2
     ON  T1.OWNER = T2.USERNAME
 ORDER BY T1.OWNER
         ,T1.TABLE_NAME
         ,T1.COLUMN_ID