SET SERVEROUTPUT ON;

DECLARE
    v_old VARCHAR2(20)  := 'CARID';
    v_new VARCHAR2(20)  := 'CAR_ID';
    v_sql VARCHAR2(200) := 'ALTER TABLE xxx RENAME COLUMN '||v_old||' TO '||v_new;
    v_exc VARCHAR2(200);
    CURSOR c1 IS
    SELECT b.table_name
      FROM user_tables a
         , user_tab_columns b
     WHERE a.table_name = b.table_name
       AND b.column_name = v_old
    ;
BEGIN
    FOR c2 IN c1
    LOOP
        BEGIN
            v_exc := REPLACE(v_sql, 'xxx', c2.table_name);
            EXECUTE IMMEDIATE(v_exc);
            dbms_output.put_line(c2.table_name || ' SUCCESS');
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line(c2.table_name || ' FAIL');
                dbms_output.put_line(SQLCODE ||' '|| SQLERRM);
        END;
    END LOOP;
END;
/