無關連表格 Join

因需要出一份報表，列出所有的資料，
但是資料分存不同表格且無相關聯。
單純是列印出來而已。

例如
==========================

CPU	HDD					OS
C2QQ8200	D1500GB7.2KS					DOS
A64X25050e	D150GB10KS					FreeDOS
A64X250940	D160GB7.2KS					LinuxCM
A64X244940	D200GB7.2KS					LinuxXW
A64X246940						MCE
A64X24850e
A64X248940
A64X25050e
A64X250940

一般join會是相乘的結果，
要像上面這樣顯示，
需要使用rownum來關聯。

WITH
TA AS (
SELECT ROWNUM AS R, CPU FROM (SELECT DISTINCT CPU FROM TABLE_A ORDER BY CPU)
),
TB AS (
SELECT ROWNUM AS R, HDD FROM (SELECT DISTINCT HDD FROM TABLE_B ORDER BY HDD)
)
SELECT TA.*, TB.*
FROM TA
FULL OUTER JOIN TB
ON TA.R = TB.R
ORDER BY 1;

Oracle 表格名稱&欄位名稱

紀錄Table的資訊
Select * form USER_ALL_TABLES;


紀錄Column的資訊
Select * form USER_ALL_TABLES;

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