oracle存储过程中用动态sql实现用户表名的改变（请oracle的pl/sql高手看看）

Question

问题描叙：
    我用sqlserver2000的DTS将一个数据库导入到ORACLE9中，发现sqlserver中的表名tablename（全为小写英文表名）在oracle中变成了"tablename"。现在我想将oracle中的表名改成tablename，（即去掉 " " ）,我知道在存储过程中用动态sql可以实现表名改变，但是由于对动态pl/sql不熟悉，所以想请教大家！
    我自己在toad写代码调试，总是出错，希望给个参考代码看看！

Answer 1

有个简便的方法不知道行否。
sql>spool t.sql
sql>select 'create ' || upper(tablename) || ' as select * from "' || tablename || '";' from user_tables;
sql>spool off
sql>edit t.sql --检查生成的脚本文件。
sql>@t.sql然后类似的删除原来的表。

Answer 2

execute immediate 'rename 旧表名 to 新表名';

Answer 3

execute immediate 'rename 旧表名 to 新表名';
也可以直接用SQLPLUS 执行rename 旧表名 to 新表名;

Answer 4

10:09:45 SQL> create table "tdemo" as select * from tb;表已创建。已用时间:  00: 00: 00.94
10:10:03 SQL> set heading off
10:10:28 SQL> set pagesize 0
10:10:28 SQL> spool c:\rename.sql
10:10:28 SQL> select 'rename "'||table_name||'" to '||upper(table_name)||';' from cat
10:10:28   2  where table_type=upper('table') and table_name<>upper(table_name);
rename "tdemo" to TDEMO;已用时间:  00: 00: 00.15
10:10:43 SQL> spool off;
10:10:47 SQL> @c:\rename.sql
SP2-0734: 未知的命令开头 "10:10:28 S..." - 忽略了剩余的行。
SP2-0734: 未知的命令开头 "10:10:28  ..." - 忽略了剩余的行。表已重命名。已用时间:  00: 00: 00.62
SP2-0734: 未知的命令开头 "已用时间: ..." - 忽略了剩余的行。
SP2-0734: 未知的命令开头 "10:10:43 S..." - 忽略了剩余的行。
10:10:57 SQL> select * from cat;
A                              TABLE
AAA                            TABLE
BBB                            TABLE
INTERFACE                      TABLE
PLAN_TABLE                     TABLE
TB                             TABLE
TB0                            TABLE
TBDEMO                         TABLE
TB_1                           TABLE
TDATE                          TABLE
TDEMO                          TABLE
TEST                           TABLE
TR                             TABLE
TRE                            TABLE
TT                             TABLE
TTREE                          TABLE
T_IP                           TABLE已选择17行。已用时间:  00: 00: 00.94
10:11:05 SQL>

Answer 5

10:16:01 SQL> create or replace procedure p_cname as
10:16:05   2  cursor c_tbname is select table_name from cat
10:16:08   3  where table_type=upper('table') and table_name<>upper(table_name);
10:16:08   4  begin
10:16:08   5  for v_cur in c_tbname loop
10:16:08   6    execute immediate 'rename "'||v_cur.table_name||'" to '||v_cur.table_nam
10:16:08   7  end loop;
10:16:08   8  end p_cname;
10:16:08   9  /过程已创建。已用时间:  00: 00: 00.63
10:16:08 SQL> create table "table01" as select * from a;表已创建。已用时间:  00: 00: 00.94
10:16:45 SQL> select * from cat;
A                              TABLE
AAA                            TABLE
BBB                            TABLE
INTERFACE                      TABLE
PLAN_TABLE                     TABLE
TB                             TABLE
TB0                            TABLE
TBDEMO                         TABLE
TB_1                           TABLE
TDATE                          TABLE
TDEMO                          TABLE
TEST                           TABLE
TR                             TABLE
TRE                            TABLE
TT                             TABLE
TTREE                          TABLE
T_IP                           TABLE
table01                        TABLE已选择18行。已用时间:  00: 00: 00.63
10:16:52 SQL> exec p_cname;PL/SQL 过程已成功完成。已用时间:  00: 00: 00.31
10:16:59 SQL> select * from cat;
A                              TABLE
AAA                            TABLE
BBB                            TABLE
INTERFACE                      TABLE
PLAN_TABLE                     TABLE
TABLE01                        TABLE
TB                             TABLE
TB0                            TABLE
TBDEMO                         TABLE
TB_1                           TABLE
TDATE                          TABLE
TDEMO                          TABLE
TEST                           TABLE
TR                             TABLE
TRE                            TABLE
TT                             TABLE
TTREE                          TABLE
T_IP                           TABLE已选择18行。已用时间:  00: 00: 00.63
10:17:04 SQL>

Answer 6

谢谢楼上的答案，我自己的办法和楼上的类似，只是我是在toad中实现的！
给分！！！