ORACLE表名与列名小写转成大写

批量将表名变为大写
begin
for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
begin
execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;
exception
when others then
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;

批量将空间内所有表的所有字段名变成大写 此方法可能导致溢出
begin
for t in (select table_name tn from user_tables) loop
begin
for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop
begin
execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn;
exception
when others then
dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
end;
end loop;
end;
end loop;
end;

将特点表PROPERTY_INFO的所有列名小写变大写
begin
for c in (select COLUMN_NAME cn from all_tab_columns where table_name='PROPERTY_INFO') loop
begin
execute immediate 'alter table PROPERTY_INFO rename column "'||c.cn||'" to '||c.cn;
exception
when others then
dbms_output.put_line('PROPERTY_INFO'||'.'||c.cn||'已经存在');
end;
end loop;
end;

begin
DBMS_OUTPUT.ENABLE (buffer_size=>null) ;

for t in (select table_name tn from user_tables) loop

  begin

     for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop

         begin

            execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn;

         exception

            when others then

               dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');

         end;

     end loop;

  end;

end loop;

end;

begin
for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
begin
execute immediate 'alter table "'||c.tn||'" rename to '|| "UPPER"(c.tn);
exception
when others then
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;

-- 修改数据库表名大小写
begin
-- 遍历所有的表名
for c in (select table_name tn from user_tables where table_name <> upper(table_name) ) loop
begin
-- 执行修改
execute immediate 'alter table "'||c.tn||'" rename to "'||upper(c.tn)||'"';
exception
when others then
-- 已经存在，输出提示
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;

select column_name cn from user_tab_columns where table_name='a'
alter table a RENAME COLUMN "B" to "b"
ALTER TABLE "IBANK"."a" RENAME COLUMN "b" TO "b";

-- 修改数据库字段名大小写

begin
-- 启动缓冲
DBMS_OUTPUT.ENABLE (buffer_size=>null) ;
-- 遍历所有表名
for t in (select table_name tn from user_tables) loop
begin
-- 遍历所有列名
for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop
begin
--执行修改语句 注意双引号 与拼接 外单内双
execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to "'||upper(c.cn)||'"';
-- 异常处理
exception
when others then
dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
end;
end loop;
end;
end loop;
end;