how to know the previous length of a column after altering that

Question

I have altered the size of a column in Oracle , and now i want to get the prev size of that column, so is there anyway we can get the prev size of that column.

Thanks.

Answer 1

You may use flashback on user_tab_columns :

SQL> conn <your_schema>
SQL> create table tab( str varchar2(20) );
SQL> alter table tab modify str VARCHAR2(30);
SQL> conn / as sysdba
SQL> grant flashback on user_tab_columns to <your_schema>;
SQL> conn <your_schema>
SQL> select *
  from user_tab_columns
 as of timestamp systimestamp - interval '1' minute c 
 where c.column_name = 'STR'
   and c.table_name = 'TAB'; 
-- the period depends on your latency of issuing the commands
-- "'1' minute" may be replaced with "'10' second" as an example.