Oracle 批量修改字段长度

Oracle不同字符集导入导出

GBK导入UTF8：GBK字符集（一个汉字占用两位），UTF8字符集（一个汉子占用三位）

修改 varchar2 类型长度

CREATE OR REPLACE PROCEDURE m_varchar2 AS
    CURSOR TEMP IS
        SELECT TABLE_NAME,
               COLUMN_NAME,
               CEIL(DATA_LENGTH * 3 / 2) DATA_LENGTH
          FROM USER_TAB_COLUMNS
         WHERE DATA_TYPE = 'VARCHAR2'
           AND DATA_LENGTH < 2000;
    STR VARCHAR2(500) := '';
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    FOR S IN TEMP LOOP
        STR := 'ALTER TABLE ' || S.TABLE_NAME || ' MODIFY(' ||
               S.COLUMN_NAME || ' VARCHAR2(' || S.DATA_LENGTH || '))';
        DBMS_OUTPUT.PUT_LINE(STR);
        EXECUTE IMMEDIATE STR;
    END LOOP;
END;

执行

call m_varchar2();

修改 char 类型长度

CREATE OR REPLACE PROCEDURE m_char AS
    CURSOR TEMP IS
        SELECT TABLE_NAME,
               COLUMN_NAME,
               CEIL(DATA_LENGTH * 3 / 2) DATA_LENGTH
          FROM USER_TAB_COLUMNS
         WHERE DATA_TYPE = 'CHAR'
           AND DATA_LENGTH < 2000;
    STR VARCHAR2(500) := '';
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    FOR S IN TEMP LOOP
        STR := 'ALTER TABLE ' || S.TABLE_NAME || ' MODIFY(' ||
               S.COLUMN_NAME || ' VARCHAR2(' || S.DATA_LENGTH || '))';
        DBMS_OUTPUT.PUT_LINE(STR);
        EXECUTE IMMEDIATE STR;
    END LOOP;
END;

执行

call m_char();