Oracle INSTR

SELECT INSTR('abc','b') FROM DUAL;

INSTR('ABC','B')
----------------
2
1 row selected.




SELECT INSTR('abc','a') FROM DUAL;

INSTR('ABC','A')
----------------
1
1 row selected.

Tomcat out of memory

java.lang.OutOfMemoryError: Java heap space

download data to excel file
Excel 最多可以65536筆，
不過兩萬多筆就爆掉。

上網查了一下，
要調整Tomcat預設的heep size。

If Java runs out of memory, the following error occurs:

java -Xms -Xmx
Defaults are:
java -Xms32m -Xmx128m

win
/tomcat/bin/catalina.bat 加上下面的命令:
set JAVA_OPTS=-Xms32m -Xmx256m

unix/linux
/tomcat/bin/catalina.sh 加上下面的命令:
JAVA_OPTS="-Xms32m -Xmx256m"

Oracle DB LINK

1、已經配置本地服務
create public database link sid connect to userid identified by password using 'sid'
CREATE DATABASE LINK 數據庫鏈接名 CONNECT TO 用戶名 IDENTIFIED BY 密碼 USING 『本地配置的數據的實例名』;

2.未配置
CREATE DATABASE LINK "DB_LINK.NAME"
CONNECT TO username
IDENTIFIED BY password
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sid)
)
)';


3.Drop
DROP DATABASE LINK "DB_LINK.NAME";

Oracle union, minus, intersect

UNION
兩邊結果相加，去掉重複

UNION ALL
兩邊結果相加

MINUS
上面減掉下面的結果

INTERSECT
重複的部份