Hello Experts,
I am trying to list out BLOB/CLOB datatypes in a database based on the resources from here, here and here. However, I am getting duplicates with this for has_blob and has_clob columns. Could someone please help me with it?
select col.owner as schema_name,
col.table_name,
t.num_rows,
CASE WHEN col.data_type = 'BLOB' THEN 'Y' ELSE 'N' END AS has_blob,
CASE WHEN col.data_type = 'CLOB' THEN 'Y' ELSE 'N' END AS has_clob
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner
and col.table_name = t.table_name
where
col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by col.owner,
col.table_name,
t.num_rows,
CASE WHEN col.data_type = 'BLOB' THEN 'Y' ELSE 'N' END,
CASE WHEN col.data_type = 'CLOB' THEN 'Y' ELSE 'N' END
order by col.table_name
;
Thanks!