Oracle Databaseでは空文字がNULLと等価として扱われる

良く知られた内容なのかもしれないが、MySQLやpostgreSQLなど他のRDBMSを触っているとびっくりする仕様。ちゃんと最新版の12cのドキュメントにも記載されている。

Oracle Databaseは、長さが0(ゼロ)の文字値をNULLとして処理します。ただし、NULLは値0(ゼロ)と同じではないため、0(ゼロ)の数値を表すためにNULL値を使用しないでください。

注意事項として次の内容も記載されている。

この処理はOracleの今後のバージョンでも継続されるとはかぎらないため、空の文字列をNULLとして処理しないことをお薦めします。

困るのがnot null属性が設定されている(例えば主キーを設定している)項目に空文字が入れられないこと。採用するデータベースの種類に依存しない設計をしようとしたら、主キーに文字列型を含むような設計をすべきではないといったところだろうか。(そもそもインデックスを設定される場合も不利に働くのだろうし、正規化を前提とした場合、設計が適切ではないのかもしれない)

参考)

SHIFT the Oracle 長さ0の文字列

Oracle Databaseをクラウドで利用する方法

今日、Windowsから始めるOracleCloud-Oracle IaaSの使い方 のセミナーに参加してきた。
Oracleはクラウド(とりわけIaaS分野)で完全に後発組になってしまっていて、あまりメリットが打ち出せていないように感じる。
Azureもまずまずだが、AWS一人勝ちの様相が強いように思う。
最近は、コスト面でもだいぶ頑張ってきていることと、アジアにリージョンを設ける話が出てきているようで、レイテンシが改善できる点は今後利用していく上で評価できる。
1.IaaS環境でOracle Databaseをセットアップする
  メリット:ライセンスを持ち込みできる、パラメータのカスタマイズが容易
  デメリット:構築にかかるコスト、時間がオンプレミスと同等
2.PaaS環境でOracle Databaseを利用する
  メリット:最適なパラメータの状況で利用できる
  デメリット:(システム移行において、非互換を避けるために)古いバージョンを利用することが出来ない
PaaS環境だと下記が考えられる
Amazon RDS for Oracle Database
価格表:https://aws.amazon.com/jp/rds/oracle/pricing/
Oracle Database Service
価格表:https://cloud.oracle.com/ja_JP/opc/database/pricing
メリット:最新バージョン(12cR2)が利用できる

ディレクトリがあるのにexpdpができない

expdp username/password directory=hoge dumpfile=hoge.dmp
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name HOGE is invalid
hogeディレクトリに出力しようとするもHOGEに書き出してしまう。
ディレクトリの指定に小文字を利用することもできるが、大文字で指定すべき。
調べてみたところ、usernameにwrite directory権限がなければ、書き込みができない。ただし、上記のようなエラーメッセージとなるため、わかりづらい。
impdpコマンドも実行できるよう
grant read, write on directory HOGE to username;
をDBA権限のあるユーザーで実行する必要がある。

Oracle Master Database 11g Bronze(1Z0-018:Bronze DBA11g)

を受験した。
「徹底攻略ORACLE MASTER Bronze DBA11g教科書[1Z0-018]対応 (ITプロ/ITエンジニアのための徹底攻略)」を隅々まで解いたこともあって、正解率75%で「1Z0-051 11gSQL基礎I」よりも簡単に合格することができ、1Z0-051とあわせてOracle Master Database 11g Bronze認定となった。
できれば、Oracle Databaseを自分の環境にインストールして、Enterprise Managerのインターフェイスは一通り触っておくとよいと思う。
把握しておくべき部分は次の通り。
・ブロック、エクステント、セグメントの違い
・データベースバッファキャッシュ、共有プール、ラージプールの違い
・SQLチューニング・アドバイザとSQLアクセス・アドバイザの違い
・完全リカバリと不完全リカバリ(Point in Time)の違い
・一貫性バックアップと非一貫性バックアップの違い

ORA-01950: no privileges on tablespace ‘(表領域名)’

該当の表領域を利用できる権限がないためにエラーが発生する。
create user username identified by “password“;
grant connect to username;
とするとデフォルト表領域の指定がないので、usernameのデフォルト表領域は、USERSとなる。
usernameでログインして、表を作成しようとするとconnectロールでは表が作成できないので、grant create table to usernameで別途CREATE TABLEシステム権限を付与してあげる必要がある。また、alter user username quota unlimited on tablespace で表領域に(例えば・・・)無制限割当をしてあげることで初めて、表が作成できる。

CentOSにOracle Database 11gを構築する

CentOSはサポート一覧にあるOSではないが、Redhat Linux互換なので、インストールすることができる。
OTNのアカウントを取得すれば、http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html からOracle Database 11g Release 2(11.2.0.1.0)をダウンロードできる。
/usr/local/srcにlinux_11gR2_database_1of2.zip、linux_11gR2_database_2of2.zipをダウンロードして、以下の手順で実際にインストールした。
1.インストールに必要なグループならびにユーザーを作成
groupadd oinstall
groupadd dba
useradd oracle -g oinstall -G dba
2.X Window Systemがインストールされていない場合には、レスポンスファイルを利用して、サイレントインストールを行うか、実際にX Window Systemをインストールする。
yum groupinstall “X Window System” “GNOME Desktop Environment”
ここからはOralce Universal Installerを利用したGUIインストールを前提とする。
3.依存関係のあるパッケージをインストールする
yum install gcc compat-libstdc++-33 elfutils-libelf-devel gcc-c++ libaio-devel sysstat unixODBC-devel pdksh
4./etc/sysctlをviで開き、以下のカーネルパラメータを追加する
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.sem = 25032000 100128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
kernel.sem=250 32000 100 128
5.sysctl -p で設定を反映する
6.ulimit -n 65536 コマンドを実行する
7.startx でX Window Systemを起動する
8.xhost hostname で接続を許可する
9.su oracle でユーザーをスイッチする
10.export DISPLAY=hostname:0.0 を実行する
11.以下のコマンドを実行し、Universal Installerを起動し、インストーラーに従ってインストールを行う。
cd /usr/local/src
unzip linux_11gR2_database_1of2.zip
unzip linux_11gR2_database_2of2.zip
cd database
./runInstaller
12.インストールが完了したら、インストーラーに従って、su rootでrootに戻り、以下のコマンドを実行した。
oraInventory/orainstRoot.sh
oracle/product/11.2.0/dbhome_1/root.sh
13.サービス起動時にデータベースを同時に起動するよう /etc/oratab を修正した。
orcl:/home/oracle/app/oracle/product/11.2.0/dbhome_1:Y
14.サービスが起動できるように oracleユーザーの.bash_profile を以下のように追記した。
export PATH
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
15.以下のコマンドをoracleユーザーで起動できることを確認する
lsnrctl start
emctl start dbconsole
16.自動起動スクリプトを /etc/init.d/dbora で作成する
作成方法は、先ほど紹介したサイレントモードインストール方法のサイトにあるスクリプトを参照してください。

Oracle Master Database 11g Bronze(1Z0-051:SQL基礎I)

を今日オンラインで受験した。
結果は、69点で何とか合格した。
SQL基礎だが、Oracle Database専用の文法などもあり、テクニカルエンジニア(データベース)を持っていても全く歯が立たない。
王道は、問題集を解きながら、実際にOracle Databaseを触るしかないというのが結論。
Oracle Master Bronze認定となるためには、1Z0-018J DBA 11gも合格しなければならない。

ORDER BY句におけるNULLの順番について

Mysqlでは、昇順(ASC)でORDER BYするとNULLは先頭に来る。降順の場合には、最後に来る。順番を逆にしたい場合には、COALESCE(列名, 型の最大値)を指定するなどの対応が必要である
一方で、Oracle Database 11gでは、昇順(ASC)でORDER BYするとNULLは最後に来る。降順の場合には、最初に来る。順番を逆にしたい場合には、NULLS FIRSTもしくはNULLS LASTを指定すればよい。

処理時間の長いSQLを確認する

SELECT CPU_TIME, SQL_TEXT FROM V$SQL WHERE CPU_TIME > 0 ORDER BY CPU_TIME DESC;
CPU_TIMEはCPUの処理時間でマイクロ秒で返ってくる。
これが大きいものがたくさんあるとCPU使用率は高い状態となる。