So, we just found out that 254 tables in our Oracle DBMS have one column named "Foo" with the wrong length- Number(10) instead of Number(3).
That foo column is a part from the PK of the tables.
Those tables have other tables with forigen keys to it.
What I did is:
- backed-up the table with a temp table.
- Disabled the forigen keys to the table.
- Disabled the PK with the
foo column.
- Nulled the
foo column for all the rows.
- Restored all the above
But now we found out it's not just couple of tables but 254 tables.
Is there an easy way, (or at least easier than this) to alter the columns length?
P.S. I have DBA permissions.
There's an easier way to generate the scripts that you want, use the system tables user_tables and user_constraints to dynamically generate the DDL. The downside is that this requires downtime. Also note that I use the truncate command rather than delete, which should be faster.
Assuming a simple table that looks like:
create table a (
foo number(10)
, bar number(10)
, constraint pk_a primary key (foo)
, constraint fk_a foreign key ( bar ) references a(foo )
);
This unlovely looking query
select cmd
from (
select table_name
, 1 as stage -- Just used to order by at the end.
, 'create table ' || table_name || '_backup as select * from '
|| table_name || ';' || chr(10) as cmd
-- chr(10) is LF
from user_tab_columns -- View of all columns
where column_name = 'FOO'
and data_precision = 10 -- Length of the number
union all
select table_name
, 3 as stage
, 'truncate table ' || table_name || ';' || chr(10) -- Remove all data
|| 'alter table ' || table_name
|| ' modify ( foo number(3));' || chr(10)
|| 'insert into ' || table_name || ' select * from '
|| table_name || '_backup;' || chr(10)
|| 'drop table ' || table_name || '_backup;' as cmd
from user_tab_columns
where column_name = 'FOO'
and data_precision = 10
union all
select ut.table_name
, 2 as stage
-- Disable the constraint
, 'alter table ' || uc.table_name || ' disable constraint '
|| uc.constraint_name || ';' || chr(10) as cmd
from user_constraints uc -- All named constraints
join user_tab_columns ut
on uc.table_name = ut.table_name
where ut.column_name = 'FOO'
and ut.data_precision = 10
and constraint_type = 'R' -- Foreign Key constraints (see link)
union all
select ut.table_name
, 4 as stage
, 'alter table ' || uc.table_name || ' enable constraint '
|| uc.constraint_name || ';' || chr(10) as cmd
from user_constraints uc
join user_tab_columns ut
on uc.table_name = ut.table_name
where ut.column_name = 'FOO'
and ut.data_precision = 10
and constraint_type = 'R'
)
order by stage
Will produce the following:
create table A_backup as select * from A; -- Create your backup
alter table A disable constraint FK_A; -- Disable FKs
truncate table A; -- Remove all data in the table
alter table A modify ( foo number(3)); -- Reduce the size of the column
insert into A select * from A_backup; -- Replace all the data
drop table A_backup; -- Drop the backup
alter table A enable constraint FK_A; -- Re-enable FKs
Due to the column stage, this won't be done table by table but stage by stage so that all the constraints will be disabled at the same time, which will avoid problems. If you're scared (I would be) then remove the drop of the _backup tables from the query; this means that whatever goes wrong you're safe.
If you're running this in SQL*Plus you also want to include whenever sqlerror exit so that if there's a problem, for instance no more tablespace, you don't truncate things that you haven't backed-up. It might almost be worth running it stage by stage so that you know that everything has completed correctly.
I would suggest testing this on a different user with a few tables to ensure that it does everything you need.
What we have done is:
CREATE TABLE <table_name_backup> as SELECT * <table_name>;
DELETE <table_name>;
ALTER TABLE <table_name> MODIFY (Foo NUMBER(3));
INSERT INTO <table_name> SELECT * FROM <table_name_backup>;
DROP <table_name_backup>;
For all the tables.
Your solution works but is a lot of work and implies downtime.
Since physically a NUMBER(3) is exactly like a NUMBER(10) with a stronger constraint you could add CHECK constraints and get the same logical restriction without downtime:
LOOP
ALTER TABLE <table_name> ADD CONSTRAINT <table_foo_chk> CHECK (foo < 1000);
END LOOP;
