Oracle Note－Jemmy Walker

以前不太習慣在DB上做資料庫關聯，用SQurirreL SQL Client工具匯出的Schema無法匯到另一個DB，有一個原因如下：

ALTER TABLE Table_A ADD CONSTRAINT TN_BASICFLOWUSAGE_FK FOREIGN KEY (A_ID) REFERENCES Table_B(A_ID) ON DELETE CASCADE ON UPDATE CASCADE;

Table_A的A_ID要和Table_B的A_ID關聯，Table_B裡Table_A的A_ID的記錄就不能刪。但Oracle只支援ON DELETE CASCADE，所以上述Script應改成：ALTER TABLE Table_A ADD CONSTRAINT TN_BASICFLOWUSAGE_FK FOREIGN KEY (A_ID) REFERENCES Table_B(A_ID) ON DELETE CASCADE;

另外，在Database A要Link到Database B的語法如下：

CREATE DATABASE LINK "USER_B.B"
CONNECT TO <USERID>
IDENTIFIED BY <PWD>
USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BBB)
      (SERVER = DEDICATED)
    )
)';

使用方式如右：select * from table_name@USER_B.B