Oracle 刪除重複資料

DELETE
FROM TABLE_NAME
WHERE ROWID = ANY(
SELECT ROW_ID
FROM (COLUMN_A, COLUMN_B ,COUNT(*), MAX(ROWID) AS ROW_ID
FROM TABLE_NAME
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*) > 1
)
)

重複執行數次即可，
另一種寫法執行一次，不過比較複雜一點。

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
重複的部份

oracle alter table by another table

select 'ALTER TABLE K0I200.DTD_APLS_BOM MODIFY('||ta.c1||' VARCHAR2('||C.DATA_LENGTH||' BYTE));'
from SYS.USER_TAB_COLUMNS c
left outer join (
select column_name as c1, DATA_LENGTH as l1 , DATA_TYPE
from SYS.USER_TAB_COLUMNS
where table_name = 'DTD_APLS_BOM'
) ta
on C.COLUMN_NAME = ta.c1
where table_name = 'DP_APLS_BOM' and ta.l1 is not null and C.DATA_TYPE = 'VARCHAR2'
order by 1

MS SQL 清除交易紀錄檔

--清log
backup log DB_NAME with truncate_only
go

--完整備份一次
backup database DB_NAME to disk = 'c:\DB_NAME.bak'
go

--壓縮資料庫釋放空間
dbcc shrinkdatabase (DB_NAME, 10)
go

oracle DECODE

select cd,decode(
cd,
'LCD','11',
'MB','22',
'fff'
)
from cm_code
order by 1

相當於case when

select cd,
case when cd ='LCD' then '11'
when cd ='MB' then '22'
else 'fff'
end as CD2
from cm_code
order by 1

oracle 增加 session

alter system set processes=200 scope=spfile;

BEA-000627 >> Reached maximum capacity of pool "My Connection Pool", making "0" new resource instances instead of "1".

Web console => testPortal> JDBC Connection Pools> My Connection Pool
Configuration => Connections => click Advanced Options [ Show ]

Login Delay: 創建物理連接延遲
Allow Shrinking :允許收縮，當已創建連接不用時，收縮到最初創建的連接數或正在使用的連接數
Shrink Frequency: 收縮時間
Enable Connection Leak Profiling:收集連接洩漏信息
Connection Reserve Timeout: 連接超時保留時間
Connection Creation Retry Frequency: 重新創建數據庫連接頻率
Inactive Connection Timeout: 非活動連接超時時間
Maximum Waiting for Connection: 最大請求連接數
Maximum Connections Made Unavailable:連接池不被應用程序使用最大連接數

2008/1/15 (Testing setting)
Enable Connection Profiling => enable
Shrink Frequency:900 => 300 second
Inactive Connection Timeout: 0 => 100 second