metadata for determining unsed columns

Question

We are fetching column names from all_tabs_cols, where we get some unused columns that are not present in the table. How can I determine all the unused columns that are present in all_tabs_cols. Does there exists a data dictionary table for this ?

procedure xxx_mig_db_column_list_proc is
  begin
    insert into xxx_mig_db_column_list
      (id,
       owner_name,
       table_name,
       column_name,
       column_seq,
       data_type,
       is_get_put,
       is_base_primary_key,
       is_base_primary_key_with_seq,
       is_foreign_key,
       is_self_ref_foreign_key,
       is_conditional_foreign_key,
       is_manual,
       category,
       referred_table_name,
       referred_column_name,
       manual_fn_to_call,
       seq_increment_fn_to_call,
       record_manually_altered,
       manual_alteration_details)
      (select rownum,
              s_t_c.owner,
              s_t_c.table_name,
              s_t_c.column_name,
              s_t_c.column_id,
              s_t_c.data_type,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              NULL,
              'N',
              NULL
         FROM (SELECT a_t_c.column_name column_name,
                      a_t_c.column_id   column_id,
                      a_t_c.data_type   data_type,
                      s_t.table_name    table_name,
                      s_t.owner_name    owner
                 FROM all_tab_cols a_t_c, xxx_mig_db_table_list s_t
                WHERE s_t.table_name = a_t_c.table_name
                  AND s_t.owner_name = a_t_c.owner
                  AND s_t.migrate_yn = 'Y') s_t_c

       );
    commit;

  exception
    when others then

      xxx_mig_fn_debug_proc('xxx_mig_db_column_list_proc',
                            'Error : ' || SQLCODE || '*');
  end xxx_mig_db_column_list_proc;

Answer 1

See this question. There is another view ALL_TAB_COLUMNS that shows only "used" columns, so you could use that instead of ALL_TAB_COLS, or subtract one from the other to get just the unused columns.