I have a legacy database and I was able to use ALL_TAB_MODIFICATIONS to easily find candidates for deletion - tables, that are no longer used by the application using that database, and then continuing with more detailed review of these tables and possibly deleting them
(I know it isn't guaranteed, hence I call them just "candidates for deletion").
My question is, is there anything similar I could use to find columns which aren't used? That means that all newly inserted data has NULL, and it is never UPDATEd to get a not null value.
If there isn't a similar view provided by Oracle, what other approach would you recommend to find them? This isn't needed because of storage reasons, but because the database is open also for reporting purposes and we have had cases where reports have been created based on old columns and thus providing wrong results.
Why not invest a little and get an exact result?
The idea is to store the contant of the tables say at the begin of the month and repeat this at the end of teh month. From the difference you can see with table columns were changed by updates or with inserts. You'd probably tolerate changes caused by deletes.
You'll only need twice the space of your DB and invest a bit in the reporting SQLs.
Please note also that a drop of a column - even if not actively used - may invalidate your application in case wenn the column is referenced or select * from is used.