Blog Blog 齋

Oracle Buys Enterprise Performance Management Leader Hyperion

Hyperion 係 OLAP 界龍頭大哥（唔係土星粒衛星），以產品性能同價格聞名…所謂的一分錢一分貨。

Oracle 自家的 OLAP 產品一向沒有人用，估不到今次會發憤圖強，唔大唔食。

Oracle 都算巴閉，近幾年對差不多同其業務有關的公司，可謂大小通殺。CRM/ERP 方面有 PeopleSoft 同 Siebel，Database 方面有 InnoDB、Berkeley、TimesTen。OS 方面又自已重新包裝 Redhat，自已出 linux。App Server 方面，本來想收購 JBoss，但輸左俾 Redhat 

 

DUAL is an interesting table in Oracle. You use it to do dirty work, like obtaining the SYSDATE, or doing some calculation when you lose your calculator. There is only one row, and no one care what is stored in the row. It is more funny if you are a Chinese, because it sounds like a foul word in Chinese.

Undoubtedly, you have doubt about what will happen if DUAL has more or fewer (i.e., 0) rows. What if it is dropped, and how about recreating it?

Let's try various DML and DDL statements with DUAL…

(I know no one will follow this document, but if you want to try the experiment below, make sure to ensure you have a usable backup of the database.)

(閱讀全文)

之前裝機時，原來不小心鏟去了 Logical Volume

有搜集了幾年的 Oracle 秘笈、筆記、sample、經驗之談

等等，全部化為烏有嗚，傷亡慘重

解我咁大意？！

最近司的電腦裝了個新玩具︰Oracle Express Edition，是 Oracle 的免費版，但有 1 個 instance， 1 粒 CPU，1G RAM 同 4G 數據的限制。

下載地點︰ http://www.oracle.com/technology/software/products/database/oracle10g/index.html

雖然有限制，功能亦不齊全，但只玩提供了的功能，都好好玩

• 最主要玩 backup restore。RMAN 真係好勁，但試過 restore 完個 datafile用不到。今日無聊到在自己的 database 登記了 RMAN Catalog
• Analytic SQL︰好強的 SQL Extension
• Log Miner︰查看 redo log 同 archive log 的 data
• Application Express…DB 兼任 web server。都可以寫複雜的網詀（但唔識）。勁好玩

(閱讀全文)

來個反高潮

(For delicated server only)

A transaction is locking a table. It is possible to find out client information from V$SESSION columns like PROGRAM and MACHINE. But it does not provide enough information to identify the correct client if you have 5 SQLPlus running.

Using mixed technique on Oracle and Linux, the client is found. 

(閱讀全文)

Question:

Consider the following table

create table t (
seq int,
seg varchar2(100)
);

Write a SQL to  concatenate the segments of the different rows to return a single string, where the order is specified in the seq column

(閱讀全文)

Question:

Consider the following table

create table t ( seq int ); 

While seq is supposed to be a continous series of figures, this is not the case. How to detect the range of “holes” within the data?

For example, assume t contain the following:

t
——-
1
2
6
8

The values indicating 3-5 and 7 should be returned.

The funny part in this question is that you need to return valuesoutside the data. But those data can be derived by the data easily 

(閱讀全文)