how to change column default value to no default in 11gr2

Have the question as follows:
create table scott.t1(col1 varchar2(1) default null, col2 varchar2(10));
create table scott.t2(col1 varchar2(1), col2 varchar2(10));

create table (table_name varchar2(35), column_name varchar2(35), data_default varchar2(20));

    for a in (select table_name, column_name, DATA_DEFAULT from DBA_TAB_COLS where owner = 'SCOTT' and table_name in ('T1', 'T2') ) loop
       insert into values(a.table_name, a.column_name, a.data_default);
    end loop;

select * from order by 1,2,3;

T1      COL1      null
T1      COL2      
T2      COL1      
T2      COL2      

The question is
without dropping T1, how to remove the default value "null" here to make T1 the same as T2???
Can any guru shed some light on it? Thanks a lot.
Who is Participating?
sdstuberConnect With a Mentor Commented:
you can't rearrange the order of columns without rebuilding the entire table.

but, if you want to recreate the column it's pretty easy to do

alter table t1 add(col3 varchar2(1));
update t1 set col3 = col1;
alter table t1 drop column col1;
alter table t1 rename column col3 to col1;

No - there isn't a documented, supported, safe way to alter the data dictionary to remove the default.
sdstuberConnect With a Mentor Commented:
it's a quirk of oracle.

once you define a default, you can't remove it.  You can only change it.

Of course, setting the default to NULL is the same as not having a default though.  So, it's safe.

The only other options would be  drop and recreate the column, which would then move it to the end of the column list.

Or, create the entire table.

Generally easier to just leave the NULL default
insert into values
(a.table_name, a.column_name, decode(upper(a.data_default),'NULL',null));
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

awking00Connect With a Mentor Commented:
Should be modified to accommodate actual default values -
(a.table_name, a.column_name, decode(upper(a.data_default),'NULL',null,a.data_default));
Inserting, updating or otherwise modifying the content of the "TT" table doesn't change the default value of a column.

It could change a report that looks at column definitions, but that's not what the question asked.
jl66Author Commented:
Thanks for the tips. I also checked some doc and links. However I still would like to try the alternatives. Maybe the question is equavilent to that
1) is there any safe way to update the table dba(user,all)_tab_cols?
2) As sdstuber mentioned, drop/re-create the column and re-arrange the order to the original. Could you please show me an example for it or a link?
jl66Author Commented:
Thanks for the answers
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.