【Oracle】数据库用户表结构升级

温馨提示

没完全试验，谨慎参考

场景：

Oracle数据库两个，分别与A用户与B用户，用户名一样，表一样，但由于A在一直开发，根据情况不断添加字段，一开始两库是保持一致的，但一段时间后，B的字段相比A缺失很多，这时候如何进行快速升级B的表结构保持与A同步。

解决思路：

当然最简单的是删用户重导，但对于数据量太大，而且里面数据不是必要的这种虽然简洁，但耗时较长，且没必要，那么是否可以考虑单表扩容，查到单个表的建表语句，将每个字段，alter table xxx add xxx xxx； 这样其实是可行的，但表太多呢。
那么就用到了系统用户视图 user_tab_columns
这时候就要根据字段类型调整alter语句
关于oralce字段，参考：
ORACLE数据库字段类型说明

1.查看我本地数据库发现有以下类型

select distinct data_type from user_tab_columns;

但比较我们的建表语句
c_length NUMBER(8,2),
qrcode VARCHAR2(128),
status INTEGER,
task_id NUMBER(20),
sys_version VARCHAR2(32),
time_stamp DATE,
发现两者有些不是完全对应的，而且还有default，default这里先不处理了，后续在补充。

先抛出我用到的这几种类型的补充

2.查找字段及添加语句

1.TIMESTAMP(8),TIMESTAMP(7),TIMESTAMP(6),FLOAT,CLOB,DATE

这六种类型相对简单

select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('TIMESTAMP(8)',
                     'TIMESTAMP(7)',
                     'TIMESTAMP(6)',
                     'FLOAT',
                     'DATE',
                     'CLOB')
--and table_name not like 'SYS%'
;

由于本地环境TIMESTAMP(8)有default sysdate，改下就是

select 'alter table  ' || table_name || ' add ' || column_name || ' ' ||
       data_type || ' default sysdate;',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('TIMESTAMP(8)',
                     'TIMESTAMP(7)',
                     'TIMESTAMP(6)')
--and table_name not like 'SYS%'
;

2.NUMBER

这个其实是包含INTEGER,NUMBER，而number里又包含NUMBER(x)和NUMBER(x,x)两种情况，所以是三种情形，对应的user_tab_columns里的data_type都是NUMBER，但在补充字段时需要根据情况进行区别
根据data_precision和data_scale进行区分，data_precision是空的那么数据类型就是INTEGER;data_precision有值，但data_scale是0那么就是NUMBER(x)，data_scale不为0就是NUMBER(x,x)；
ok，sql：

INTEGER

 select 'alter table ' || table_name || ' add ' || column_name ||
       ' INTEGER;',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('NUMBER')
   and data_precision is null;

NUMBER(x)

 select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
        data_type || '(' || data_precision || ')' || ';',
        a.table_name,
        a.column_name,
        a.data_type,
        a.data_length,
        a.data_precision,
        a.data_scale
   from user_tab_columns a
  where data_type in ('NUMBER')
    and data_precision > 1
    and (data_scale = 0 or data_scale is null);

NUMBER(x,x)

select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || '(' || data_precision || ',' || data_scale || ')' || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('NUMBER')
   and data_precision > 1
   and data_scale > 0;

3.VARCHAR2

select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || '(' || data_length || ')' || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('VARCHAR2');

NVARCHAR2，CHAR
这俩不多，懒得写了，后续再调整

3.聚合一下，然后执行

将语句复制到文件里，plsql命令窗口执行即可，或者直接复制到命令窗口执行，提示重复的就不用管了，就是原来已然存在的，不重复的就是新添加进去的。

select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale

  from user_tab_columns a
 where data_type in ('FLOAT',
                     'DATE',
                     'CLOB')
union
select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || ' default sysdate;',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('TIMESTAMP(8)', 'TIMESTAMP(7)', 'TIMESTAMP(6)')
--and table_name not like 'SYS%'
union
select 'alter table ' || table_name || ' add ' || column_name ||
       ' INTEGER;',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('NUMBER')
   and data_precision is null
union

select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || '(' || data_precision || ')' || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('NUMBER')
   and data_precision > 1
   and (data_scale = 0 or data_scale is null)
union
select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || '(' || data_precision || ',' || data_scale || ')' || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('NUMBER')
   and data_precision > 1
   and data_scale > 0
union
select 'alter table ' || table_name || ' add ' || column_name || ' ' ||
       data_type || '(' || data_length || ')' || ';',
       a.table_name,
       a.column_name,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale
  from user_tab_columns a
 where data_type in ('VARCHAR2');