更改多个表的列长 [英] Alter multiple tables' columns length

问题描述

因此，我们刚刚发现，在Oracle DBMS中的254个表中有一个名为"Foo" 的列，其长度错误-Number(10)而不是Number(3).

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).

foo列是表PK的一部分.
这些表还有其他带有原键的表.

That foo column is a part from the PK of the tables.
Those tables have other tables with forigen keys to it.

我所做的是:

1. 使用临时表备份该表.
2. 禁用表的原键.
3. 通过foo列禁用PK.
4. 清除所有行的foo列.
5. 恢复了以上所有内容

1. backed-up the table with a temp table.
2. Disabled the forigen keys to the table.
3. Disabled the PK with the foo column.
4. Nulled the foo column for all the rows.
5. Restored all the above

但是现在我们发现它不仅是几个表，而且是 254个表.

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.我具有DBA权限.

P.S. I have DBA permissions.

推荐答案

使用系统表 truncate 命令而不是delete，它应该更快.

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

将产生以下内容:

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

由于列stage，这不会逐表完成，而是逐步执行，以便所有约束都将同时被禁用，从而避免出现问题.如果您很害怕(我会这样)，则从查询中删除_backup表中的drop；这意味着无论出什么问题，您都是安全的.

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.

如果您正在SQL * Plus中运行此程序，则还希望包含whenever sqlerror exit，以便在出现问题(例如没有更多的表空间)时，不会截断尚未备份的内容.逐步执行此操作几乎是值得的，这样您就可以知道一切都已正确完成.

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.

这篇关于更改多个表的列长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！