This week I encountered an interesting issue. We have processes that explore tables for potentially sensitive information, in order to ensure our data protection and privacy policies are followed and enforced. Part of that process involves retrieving a list of all the table columns in an Oracle server.
Lucky us, Oracle has a robust data dictionary, and the all_tab_columns view comes to the rescue. The only surprise for us was that this view also contains all view columns. Well, now! That proved inconvenient.
I worked around this by joining to the all_views view, and excluding columns that matched. Here's the code I used. Note that I exclude certain tables. It's probably the case that some of these tables don't exist in all environments, so feel free to adjust the filtration to your own circumstances.
SELECT ATC.*
FROM ALL_TAB_COLUMNS ATC
LEFT OUTER JOIN ALL_VIEWS AV ON ATC.OWNER = AV.OWNER
AND ATC.TABLE_NAME = AV.VIEW_NAME
WHERE ATC.OWNER NOT IN ('SYSTEM', 'SYS', 'XDB', 'OLAPSYS',
'RMAN', 'IMADVISOR', 'APPQOSSYS',
'PERFSTAT')
AND AV.OWNER IS NULL
Cheers!
