rename表後， 同義詞synonym失效問題

今日，因為生産線上要遷移大表，做曆史表。在做rename表後，程式報錯。rename之後，不僅synonym失效，grant的授權也會失效。下面看測試步驟和結論：

SQL> create user test2 identified by "test2";

User created.

SQL> grant create session ,resource to test2;

Grant succeeded.

SQL> create public synonym t1 for test1.t1;

Synonym created.

SQL> grant select on test1.t1 to test2;

Grant succeeded.

SQL> conn test2/test2
Connected.

SQL> select * from t1;

ID
----------
test




SQL> create table test1.t1_new as select * from test1.t1;

Table created.

SQL> select * from test1.t1_new;

ID
----------
test

SQL> alter table test1.t1 rename to t1_hist;

Table altered.

SQL> select table_name,owner from dba_tables where table_name='T1_HIST';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T1_HIST                        TEST1

SQL> alter table test1.t1_new rename to t1;

Table altered.

SQL> select table_name,owner from dba_tables where table_name='T1';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T1                             TEST1

SQL> conn test2/test2
Connected.
SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter table test1.t1 rename to t1_new;

Table altered.

SQL> alter table test1.t1_hist rename to t1;

Table altered.

SQL> conn test2/test2    
Connected.
SQL>  select * from test1.t1;

ID
----------
test

SQL> select * FROM T1;

ID
----------
test



trigger測試：

SQL> 
SQL> create or replace trigger test1.after_insert_t1
  2  after insert on test1.t1 for each row
  3  declare
  4  begin 
  5  insert into test1.t2 (id) values(:new.id);
  6  end;
  7  /

Trigger created.

SQL> 
SQL> 
SQL> insert into test1.t1(id) values('tr1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1.t2;

ID
----------
test
tr1

SQL> alter table test1.t1 rename to t1_hist;

Table altered.

SQL> alter table test1.t1_new rename to t1;

Table altered.

SQL> insert into test1.t1(id) values('tr2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1.t1;

ID
----------
test
tr2