checking which columns are never used in a oracle database

I am currently converting an Oracle database to Postgis. Instead of blindingly copying the data model, I am also checking which columns are actually really used, and drop those that are never used.

In most tables it is pretty easy, I can do a quick visual check and then count that one column that seems to be zero all the time. But we have a few tables with 30-50 columns.

For such a table there is also an easy way:

SELECT t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y'
    AND t.table_name = 'YOUR_TABLE_NAME_HERE'
    AND t.num_distinct = 0

Mind you, for this to work, your database must have gathered the statistics (if you haven't done this before, this will also help your performance).

BEGIN
  DBMS_STATS.gather_database_stats();
END;

Comments
Add comment

Recent comments

Tags

ruby on rails 34 ruby 26 rails3 17 rails 15 oracle 11 rspec 9 rspec2 7 jquery 7 ubuntu 5 javascript 5 windows 5 activerecord 3 refactoring 3 geoserver 3 gis 3 arrrrcamp 3 actionmailer 2 oracle spatial 2 tdd 2 postgis 2 routing 2 rvm 2 mongoid 2 csharp 2 thin 2 win32 2 gem 2 rails4 2 git 2 service 2 haml 2 cucumber 2 view testing 2 i18n 1 displaysleep 1 spatial 1 gemsets 1 wubi 1 oracle_enhanced_adapter 1 migrations 1 watchr 1 ci 1 plugins 1 coderetreat 1 ie8 1 ssl 1 oci 1 nested model form 1 wcf 1 11.04 1 jsonp 1 ruby-oci8 1 teamcity 1 engines 1 pgadmin 1 soap 1 content_for 1 word automation 1 plugin 1 capybara 1 xml 1 bootstrap 1 migrate to rails3 1 mvc 1 unity 1 rendering 1 word2007 1 x64 1 limited stock 1 fast tests 1 pl/sql 1 delayed_job 1 pdf 1 test coverage 1 optimization 1 processing 1 borland 1 method_missing 1 cross-browser 1 devise 1 schema_plus 1 mongo 1 mongrel 1 dual boot 1 usability 1 mongrel_service 1 dba 1 mission statement 1 model 1 metadata 1 rcov 1 exceptions 1 image_tag 1 attachments 1 bde 1 css 1 yield 1 ajax 1 generative art 1 rails-assets 1 coordinate systems 1 submodules 1 netzke 1 ora-01031 1 authlogic 1 postgresql 1 shopping cart 1 agile 1 fast_tagger 1 subjective 1 wice_grid 1 generators 1 nvidia 1 mongodb 1 etsyhacks 1 staleobjecterror 1 session 1 jeweler 1 wordpress hacked 1 jasmine 1 heroku 1 rjs 1 life 1 unobtrusive-javascript 1 render_anywhere 1 html5 1 rails31 1 json 1 cocoon 1 mingw32 1 observe_field 1 osx 1 actionwebservice 1 testing 1 debugging 1 strings 1