oracle 同义词 db2,关于db2别名和oracle同义词的一点记注

1.db2支持使用命令truncate table来操作别名,结果是基表的数据被清空,要慎用

2.oracle不支持使用命令truncate table来操作同义词，相对安全

db2的这个功能，对不熟悉db2的人来说是个陷阱，上周就有人掉进去了。

以下分别是db2和oracle测试过程：

---db2的情况

db2 => select * from dual

DUMMY

-----

X

1 record(s) selected.

db2 => create table jlandzpa as (select * from dual) definition only

DB20000I  The SQL command completed successfully.

db2 =>

db2 => describe table jlandzpa

Data type                     Column

Column name                     schema    Data type name      Length     Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

DUMMY                           SYSIBM    CHARACTER                    1     0 No

1 record(s) selected.

db2 =>

db2 => select * from jlandzpa

DUMMY

-----

0 record(s) selected.

db2 => insert into jlandzpa select * from dual

DB20000I  The SQL command completed successfully.

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => select * from jlandzpa

DUMMY

-----

X

1 record(s) selected.

db2 =>

db2 => create alias jlandzpa_alias for jlandzpa

DB20000I  The SQL command completed successfully.

db2 =>

db2 => select * from jlandzpa

DUMMY

-----

X

1 record(s) selected.

db2 => select * from jlandzpa_alias

DUMMY

-----

X

1 record(s) selected.

db2 => truncate table jlandzpa_alias

DB20000I  The SQL command completed successfully.

db2 =>

db2 => select * from jlandzpa_alias

DUMMY

-----

0 record(s) selected.

db2 =>  select * from jlandzpa

DUMMY

-----

0 record(s) selected.

---oracle的情况

sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on 星期一 11月 18 10:41:53 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn main/main

已连接。

SQL> create table test (id number);

表已创建。

SQL> create synonym sy_test for main.test;

同义词已创建。

SQL> truncate table  sy_test;

truncate table  sy_test

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> truncate synonym sy_test;

truncate synonym sy_test

*

第 1 行出现错误:

ORA-03290: 无效的截断命令 - 缺失 CLUSTER 或 TABLE 关键字