changing precision
March 26, 2008 - 5am Central time zone
Reviewer: Duke Ganote from Tourist-trappy Kissimmee, Florida USA
Timestamps are cumbersome to modify:
drop table t;
Table dropped.
create table t as select systimestamp ts from dual;
Table created.
alter table t modify ts timestamp (0);
alter table t modify ts timestamp (0)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
alter table t modify ts timestamp (9);
alter table t modify ts timestamp (9)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
Followup March 26, 2008 - 9am Central time zone:
just like numbers and strings - only numbers and strings (varchar2 strings) can be made larger easily (not smaller)
timestamps are a fixed width datatype, changing the size would require rewriting the ENTIRE table (unless they are all null)
March 26, 2008 - 7pm Central time zone
Reviewer: David Aldridge from Fairfax, VA
>> ... changing the size would require rewriting the ENTIRE table (unless they are all null)
I still wish we had the option to override the "no non-null values" check though, and just take any
pain associated with the required changes. I suppose that the major issues would be migrated rows,
a locked table, a potentially high i/o load, data segment compression issues, and having to deal
with indexes.
To not be able to for the sake of one non-null value out of fifty gazillion null values is a
frustration. I'm not saying there isn't a way round it, or that it's particularly onerous, but
there are cases when it would be a useful option.
Followup March 27, 2008 - 10am Central time zone:
dbms_redefinition would do that and avoid the ... migrated rows, a locked table, a potentially
high i/o load, data segment compression issues, and having to deal with
indexes. ....
issues.
|