调整表的CHAR/VARCHAR2字段的Byte为CHAR

[img]http://dl2.iteye.com/upload/attachment/0094/4517/1b94ae74-8692-367d-b9d1-443a920f54f2.jpg[/img]
调整表的CHAR/VARCHAR2字段的Byte为CHAR
使用如下SQL脚本即可：

set serveroutput on
DECLARE
sqlstr varchar2(4000);
cursor sqlcurs is
select owner,table_name,column_name,data_type,CHAR_LENGTH from dba_tab_columns
where owner='ZFPT'
and data_type in ('CHAR','VARCHAR2') order by table_name,column_id;
begin
for str in sqlcurs loop
sqlstr:='ALTER TABLE '||str.owner||'.'||str.table_name||' MODIFY('||str.column_name||' '||str.data_type||'('||str.CHAR_LENGTH||' CHAR'||'))';
dbms_output.put_line(sqlstr);
--execute immediate sqlstr;
end loop;
end;
/
注：对于那些作为分区列的字段不能修改，会出现如下错误，并且会退出，所以最好是把命令输出到一个sql脚本里面，然后在sqlplus命令行里面去执行，也可以写成存储过程然后捕获异常并继续。

ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be changed
ORA-06512: at line 10