Oracle删除和重新创建同义词

Question

当我们像这样使用CTAS时:

create table big2 as select * from big1; 
drop table big1;
rename big2 to big1;

如果big1上存在同义词，我们是否需要先删除big1上的同义词，然后再删除和重新创建它们?或者这是不必要的?

Answer 1

No。因为Synonym只是您赋予对象的另一个名称(无论是否在您的模式中)。请看下面的代码。

(顺便说一句，你不应该直接将表t2重命名为t1吗?您的CTAS是否有其他没有在这里显示的条件?)

SQL> create table t1 as
  2  select * from scott.emp;
Table created.
SQL> select count(*) from t1;
  COUNT(*)
----------
        14
SQL> select count(*) from t2;
select count(*) from t2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create synonym s1 for t1;
Synonym created.
SQL> create table t2 as
  2  select * from t1;
Table created.
SQL> drop table t1;
Table dropped.
SQL> alter table t2 rename to t1;
Table altered.
SQL> select count(*) from t1;
  COUNT(*)
----------
        14
SQL> select count(*) from s1;
  COUNT(*)
----------
        14