[Oracle]データベースバッファキャッシュを明示的に破棄してみる

データベースバッファキャッシュは下のSQLで明示的に破棄できる。

alter system flush buffer_cache;

破棄されたことを確認する

破棄前の統計情報

SQL> select * from person where id = 1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1167617095

--------------------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |     |  1 | 17 |  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON    |  1 | 17 |  3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | SYS_C0013811 |  1 |    |  2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
   4  consistent gets
   0  physical reads
   0  redo size
 469  bytes sent via SQL*Net to client
 540  bytes received via SQL*Net from client
   1  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

データベースバッファキャッシュを破棄する。

SQL> alter system flush buffer_cache;

System altered.

同じ問い合わせを実行するとphysical readsが増えているので、データファイルからブロックが読み取られていることが確認できる。

SQL> select * from person where id=1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1167617095

--------------------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |     |  1 | 17 |  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON    |  1 | 17 |  3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | SYS_C0013811 |  1 |    |  2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
   4  consistent gets
  24  physical reads
   0  redo size
 469  bytes sent via SQL*Net to client
 540  bytes received via SQL*Net from client
   1  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

[Oracle]アプリケーションコンテキストの作成

アプリケーションコンテキストにアプリ固有の情報を格納する方法

ネームスペースの作成

create contextを使用して、ネームスペースを作成する。
ネームスペース作成時には、名前と値を管理するためのPL/SQL(プロシージャやパッケージ)をセットで指定する。

※CREATE ANY CONTEXTシステム権限が必要

sample_ctxネームスペースを作成して、名前と値を管理するためのPL/SQLにはsample_ctx_managerを指定。
この時点では、PL/SQLが存在していなくてもOK

create context sample_ctx using sample_ctx_manager;

名前と値のペアを管理するためのPL/SQLの作成

PL/SQLの名前は、create contextを作成した時の名前にする。
dbms_session.set_contextを使ってアプリケーションコンテキストに名前と値のペアを登録できる。
本来は、ログインしたユーザの情報から、DB検索して値の設定をしたりするらしい。

CREATE OR REPLACE PROCEDURE sample_ctx_mgr
IS
BEGIN
  dbms_session.set_context(
      namespace => 'sample_ctx',
      attribute => 'sample_key',
      value     => 'sample_value'
  );
END;

PL/SQLの実行

うえで作成したPL/SQL実行することでアプリケーションコンテキストに値を設定できる。

セキュリティを高めるためには、安易にexecute権限を付与するとかじゃなくってログイントリガーとかで実現するのがよいらしい。

設定された値の確認

session_contextを参照することで値を確認できる。

07:23:44 SQL> r
  1* select * from session_context

NAMESPACE         ATTRIBUTE        VALUE
------------------------------ ------------------------------ ------------------------------
SAMPLE_CTX         SAMPLE_KEY        sample_value

sys_contextを使うと値を抜き出すことができる。

select sys_context('sample_ctx', 'sample_key') from dual;

アプリケーションコンテキストをうまく使うとセキュリティを高めることができるらしい。
（まだ詳しくわかってない。）

[Oracle]DBMS_SPACE.SPACE_USAGEを使ってブロックの使用量を調べる

このPL/SQLを実行すると結果が出力される。

PL/SQL

set serveroutput on
declare
  segment_owner varchar2(30) := '&owner';
  segment_name  varchar2(30) := '&table';
  segment_type  varchar2(30) := 'TABLE';
  unformatted_blocks   number;
  unformatted_bytes    number;
  fs1_blocks           number;
  fs1_bytes            number;
  fs2_blocks           number;
  fs2_bytes            number;
  fs3_blocks           number;
  fs3_bytes            number;
  fs4_blocks           number;
  fs4_bytes            number;
  full_blocks          number;
  full_bytes           number;
begin                  
  DBMS_SPACE.SPACE_USAGE(
    segment_owner,
    segment_name,
    segment_type,
    unformatted_blocks,
    unformatted_bytes,
    fs1_blocks,
    fs1_bytes,
    fs2_blocks,
    fs2_bytes,
    fs3_blocks,
    fs3_bytes,
    fs4_blocks,
    fs4_bytes,
    full_blocks,
    full_bytes
  );

  dbms_output.put_line('空き領域が0-25%   Blocks = ' || rpad(fs1_blocks, 15)  || ' Bytes = ' || fs1_bytes);
  dbms_output.put_line('空き領域が25-50%  Blocks = ' || rpad(fs2_blocks, 15)  || ' Bytes = ' || fs2_bytes);
  dbms_output.put_line('空き領域が50-75%  Blocks = ' || rpad(fs3_blocks, 15)  || ' Bytes = ' || fs3_bytes);
  dbms_output.put_line('空き領域が75-100% Blocks = ' || rpad(fs4_blocks, 15)  || ' Bytes = ' || fs4_bytes);
  dbms_output.put_line('一杯になったもの  Blocks = ' || rpad(full_blocks, 15)  || ' Bytes = ' || full_bytes);

end;

出力例

空き領域が0-25%   Blocks = 0               Bytes = 0
空き領域が25-50%  Blocks = 0               Bytes = 0
空き領域が50-75%  Blocks = 0               Bytes = 0
空き領域が75-100% Blocks = 22              Bytes = 180224
一杯になったもの  Blocks = 286             Bytes = 2342912

deleteでデータを削除した後の出力例

一杯になったBlockが空きが75から100のところに移動している。
deleteなので使用済みのblockはそのままのこっている

空き領域が0-25%   Blocks = 0               Bytes = 0
空き領域が25-50%  Blocks = 0               Bytes = 0
空き領域が50-75%  Blocks = 0               Bytes = 0
空き領域が75-100% Blocks = 308             Bytes = 2523136
一杯になったもの  Blocks = 0               Bytes = 0

truncate後の出力例

HWMがリセットされている。

空き領域が0-25%   Blocks = 0               Bytes = 0
空き領域が25-50%  Blocks = 0               Bytes = 0
空き領域が50-75%  Blocks = 0               Bytes = 0
空き領域が75-100% Blocks = 0               Bytes = 0
一杯になったもの  Blocks = 0               Bytes = 0

[Oracle]セキュアアプリケーションロール

セキュアアプリケーションロールを使うと、ロール作成時に指定したPL/SQLからのみそのロールを有効化することができるようになる。
また、ロールを有効化する直前に、PL/SQLのコード内でセキュリティ要件を満たしているかのチェックができるメリットがある。

セキュアアプリケーションロールの作成

セキュアアプリケーションロールは、ロール作成時にidentified using を付加することで作成できる。
なお、このタイミングでidentified usingに指定したpl/sqlが存在していなくても問題ない。

SQL> create role sample_role identified using hoge.activation_sample_role;

Role created.

ロールを有効化するためのpl/sqlを作成する

このpl/sqlでは、要件を満たす場合にのみdbms_sessionパッケージを使って、先ほど作ったロールを有効化します。
サンプルとして、接続プログラムを表す値が「SQL*Plus」の場合にロールを有効化します。

create or REPLACE PROCEDURE activation_sample_role
authid CURRENT_USER
AS
BEGIN
  if (SYS_CONTEXT('USERENV','MODULE') = 'SQL*Plus') THEN
    DBMS_SESSION.set_role('sample_role');
  END IF;
END;

ロールの付与

hrスキーマのjobsテーブルへの参照権限をロールに付与し、ロールをhogeユーザに付与する。

SQL> grant select on hr.jobs to sample_role;

Grant succeeded.

SQL> grant sample_role to hoge;

Grant succeeded.

ログインしてロールの確認をする

SQL> sho user
USER is "HOGE"

-- ログイン直後のロールの確認
-- sample_roleは付与されていない
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
RESOURCE

-- ロールに与えているオブジェクト権限で参照できるテーブルへアクセスしてみるがエラーとなる。
SQL> select* from hr.jobs;
select* from hr.jobs
                *
ERROR at line 1:
ORA-00942: table or view does not exist

-- ロール有効化のPL/SQLを実行
SQL> execute activation_sample_role;

PL/SQL procedure successfully completed.

-- ロールの確認
-- 有効になっていることがわかる
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
SAMPLE_ROLE

-- ロールに値ている権限で参照できるテーブルも見れるようになる。
SQL> select* from hr.jobs;

JOB_ID    JOB_TITLE          MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President        20080      40000

[Oracle]ユーザのパスワードを強制的に有効期限切れにする

ユーザのパスワードを有効期限切れにすることで、次回のログイン時にパスワードの変更を強制できるようになる。

パスワードを有効期限切れにするには、alter userを使用するのでALTER USERシステム権限を持っているユーザで行う必要がある。

例

下のalter userでhrユーザのパスワードを有効期限切れに出来る。

alter user hr password expire;

アカウントの状態確認

dba_usersのaccount_statusカラムを見ることで、ユーザの状態を確認できる。
パスワードを有効期限切れに変更したHRユーザのステータスが、「EXPIRED」になっていることが確認出来る。

  1* select username, account_status from dba_users where username = 'HR'

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             EXPIRED

有効期限切れ状態でのログイン

以下のように、パスワードの有効期限切れであることが表示され、パスワードの変更が強制される。

08:16:41 SQL> conn hr/password
ERROR:
ORA-28001: the password has expired


hrに対するパスワードを変更しています。
新規パスワード:
新規パスワードを再入力してください:
パスワードが変更されました。
接続されました。

[Oracle]パスワードの複雑度を検証する

パスワードの複雑度の検証を行うには、デフォルト（ユーザ）プロファイルのPASSWORD_VERIFY_FUNCTIONに検証を行うファンクションを設定する。
デフォルトでは、何も設定されていないので、簡単なパスワードも普通に使える状態になっている。（例えばアカウント名と同じパスワードも設定できる）

検証ロジックは、「@$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql」を参考にして作成すると良い。
Oracle12cの場合は、ora12c_verify_functionに検証ロジックが書かれている。

検証用ファンクションの設定方法

ALTER PROFILE default LIMIT
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

今設定されている検証用ファンクションを確認する方法

select
  profile,
  resource_name,
  resource_type,
  limit
from
  dba_profiles
where
  profile = 'DEFAULT'
  and resource_name='PASSWORD_VERIFY_FUNCTION'

結果

PROFILE         RESOURCE_NAME                  RESOURCE_TYPE   LIMIT
--------------- ------------------------------ --------------- ---------------
DEFAULT         PASSWORD_VERIFY_FUNCTION       PASSWORD        ORA12C_VERIFY_F
                                                               UNCTION

DBMS_XPLAN.DISPLAY_CURSORを使って最近実行したSQL文のアクセスプランを取得

マニュアルによると、「DISPLAY_CURSORファンクションはカーソル・キャッシュにロードされている任意のカーソルの実行計画を表示します」とあるので、
実行されてからあまり時間がたっていないSQLであれば簡単にアクセスプランが見れる。

DISPLAY_CURSORは、デフォルト動作ではそのセッションの最後のカーソルのアクセスプランを確認することができる。
また、SQL_IDを指定することで任意のSQL文のアクセスプランも簡単に確認することができる。

実行したSQL

select /* hoge */ 1 from dual;

v$sqlからSQLIDを取得

SQL> select sql_id, child_number from v$sql where sql_text like '%hoge%' and sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
42mk8byafr2yx            0

DISPLAY_CURSORを使ってアクセスプランの表示

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('42mk8byafr2yx', 0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  42mk8byafr2yx, child number 0
-------------------------------------
select /* hoge */ 1 from dual

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

DISPLAY_CURSORの3番めの引数のformatを指定すると、実行計画の詳細レベルを変更することができる。
詳細は、「PL/SQLパッケージおよびタイプ・リファレンス」を見るとわかる。

[Oracle]DDL_LOCK_TIMEOUTでDDLのロックタイムアウトの待機時間を設定する

Oracle11gからは、DMLでロックされているレコードがあるテーブルに対するDDL文のロックタイムアウトの待機時間が設定できる。

設定がない場合（デフォルトの動作は）、nowaitなのでロックされているレコードがあるテーブルに対するDDLは即タイムアウトする。
発生するエラーは、こんな感じ。

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

設定値の変更は、DDL_LOCK_TIMEOUTパラメータで行う。例えば、10秒を設定したい場合には、以下の様なalter sessionを実行する。

alter session set DDL_LOCK_TIMEOUT=10;

[Oracle]パスワードの大文字小文字の区別の設定

初期化パラメータのsec_case_sensitive_logonで大文字、小文字を区別するか設定する事ができる。

この値が、trueの場合は大文字と小文字が区別されるようになる。

設定値の確認

show parametersで確認できる。

SQL> sho parameters sec_case_sensitive_logon

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon      boolean  FALSE

設定値の変更

alter systemを使用して変更する。

alter system set sec_case_sensitive_logon = false

注意点

Oracle12cのセキュリティ・ガイドによると、この機能は非推奨で下位互換のためだけに残されているようです。

Oracleのバーチャルインデックス

バーチャルインデックスを使うと、ディスクスペースや作成時間、他のアプリケーション（セッション）を
気にせずにインデックス追加時のアクセスプランなどを確認することができる。

テーブル定義

SQL> desc person
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 P_ID                                      NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

バーチャルインデックスの作成

create indexのオプションにnosegmentを指定することでバーチャルインデックスになる

create index person_name_index on person(name) nosegment;

バーチャルインデックスを使ってみる

通常は、バーチャルインデックスは使用されない。

  1* select * from person where name = 'a'

実行計画
----------------------------------------------------------
Plan hash value: 1493655343

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    65 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSON |     1 |    65 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='a')

_use_nosegment_indexesパラメータの値をtrueに変更することで、現在のセッションのみバーチャルインデックスが有効となる。

SQL> alter session set "_use_nosegment_indexes"=true;

セッションが変更されました。

バーチャルインデックスを有効に変更した後にためしてみると、インデックスが参照されるようになる。

  1* select * from person where name = 'a'

実行計画
----------------------------------------------------------
Plan hash value: 966497106

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |    65 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PERSON            |     1 |    65 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PERSON_NAME_INDEX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

[Oracle12c]SQL*Loaderのエクスプレス・モード

Oracle12CからはSQL*Loaderのエクスプレス・モードを使用してコントロールファイルを作らずにデータをロードすることができるようです。
この機能は、表の列がすべて文字列、数値または日付型で、入力ファイルにデリミタ付きの文字列データが含まれている場合(例えばCSVファイル)に、テーブル名の指定のみでロードが実行できます。

エクスプレスモードの例

テーブル定義

主キーと属性1つづつのシンプルなテーブルで試してみます。

SQL> desc users;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID       NOT NULL NUMBER(10)
 NAME          VARCHAR2(100 CHAR)

CSVファイルの内容

データが5行のCSVファイルです。

1,user1
2,user2
3,user3
4,user4
5,user5

実行結果

実行結果の標準出力内容です。5行のロードが正常に終わっていることがわかります。
実行時には、接続情報をテーブル名のみを与えるのみで、コントロールファイルは作成していません。
入力ファイルを指定しない場合、デフォルトでは「テーブル名.dat」となります。今回の場合は、「users.dat」が入力ファイルとなります。

$ sqlldr hoge/hoge@orcl TABLE=users

SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jun 27 10:49:48 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: USERS
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table USERS:
  5 Rows successfully loaded.

Check the log files:
  users.log
  users_%p.log_xt
for more information about the load.

テーブルにも5レコード格納されています。

SQL> r
  1* select * from users

   USER_ID NAME
---------- -------------------------
  1 user1
  2 user2
  3 user3
  4 user4
  5 user5

どんな定義でロードされるの？

実行時の定義（コントロールファイルの内容）は、実行ログから確認できます。
今回の場合は、以下の定義でロードされています。

OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'users'
APPEND
INTO TABLE USERS
FIELDS TERMINATED BY ","
(
  USER_ID,
  NAME CHAR(400)
)

デフォルト値の上書き方法

デフォルト値はコマンドラインパラメータで上書きします。例えば、フィールドの区切り文字をデフォルトのカンマ(,)から縦棒(|)に変更したい場合には以下のようにします。

sqlldr hoge/hoge@orcl TABLE=users TERMINATED_BY='|'

変更できる項目はマニュアルで参照できます。

SQL*Loaderでインプットファイルの論理行番号をテーブルに格納する

SQL*Loaderでデータロード時に、ファイルの論理レコード番号をテーブルに格納したい場合には、RECNUMパラメータを使用する。
RECNUMを使用するとスキップしたレコードや不良レコード、破棄レコードも1レコードとしてカウントされる。

ロード先のテーブル定義

名前       NULL?    型
--------- -------- ----------------------------
REC_NUM   NOT NULL NUMBER
NAME               VARCHAR2(100)

コントロールファイルの内容

論理行番号を格納したいカラムにRECNUMパラメータを使用する。

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET 'AL32UTF8'
INFILE 'input.csv' "str '\n'"
BADFILE 'input.bad'
DISCARDFILE 'input.dsc'
TRUNCATE
INTO TABLE HOGE
FIELDS TERMINATED BY ',' 
(
REC_NUM RECNUM,
NAME OPTIONALLY ENCLOSED BY '"' 
)

CSVファイルの内容

タイトル行あり、不正なレコード(空行)ありのファイル。

name
あ
い

う
え
お

実行結果の確認

以下のコマンドで、上のCSVファイルを取り込みます。

sqlldr 接続先情報 control=input.ctl

スキップしたヘッダー行や不正なレコードの空行もレコード数としてカウントアップされているのが分かる。

1* select * from hoge

REC_NUM    NAME
---------- ------------------------------
2          あ
3          い
5          う
6          え
7          お

[Oracle12C]拡張データ型でVARCHAR2やRAWの最大長をより大きい値にする

Oracle12cからの新機能の拡張データ型を使用すると、VARCHAR2、NVARCHAR2、RAWの最大サイズが32,767バイトまで指定可能となります。

※以前のバージョンでは、文字列(VARCHAR2、NVARCHAR2)は4000、RAWは2000が最大サイズだったかと思います。

拡張データ型の使用方法

拡張データ型は初期化パラメータのMAX_STRING_SIZEにより設定します。

この初期化パラメータには、以下の2種類の設定値があります。設定値を拡張データ型(EXTENDED)に変更するとSTANDARDに戻すことはできないので注意が必要です。

EXTENDED

上限が32,767バイトになります

STANDARD

Oracle12cより前のバージョンと同じ仕様となります。デフォルトはこの値となります。

現在値の確認

SQL> show parameters max_string_size

NAME                                        TYPE       VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

UPGRADEモードでデータベースを再起動

非CDBの場合には、以下の手順でデータベースを再起動します。

SQL> shutdown
SQL> startup UPGRADE

拡張データ型の有効化

altert systemで拡張データ型を使用できるようにします。

SQL> alter system set MAX_STRING_SIZE = EXTENDED;

System altered.

SQL> show parameters max_string_size

NAME                                        TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

rdbms/admin/utl32k.sqlを実行し、ノーマルモードでデータベースを再起動します。

テーブルを作ってみる

以下のように、VARCHAR2の4000バイトを突破したカラムを定義できるようになります。

CREATE TABLE TEST_TABLE
(
    EXTENDED_VARCHAR VARCHAR2(32767) NOT NULL
)

作成したテーブルの定義を確認すると、指定した桁数のVARCAR2列が定義されている事がわかります。

SQL> desc test_table;
 Name                                                Null?  Type
 ----------------------------------------- -------- ----------------------------
 EXTENDED_VARCHAR                   NOT NULL VARCHAR2(32767)

データを登録してみる

以下のように4000バイトを超えるデータを挿入、参照できていることがわかります。

SQL> insert into test_table values (rpad('1', 32767, '1'));

1 row created.

SQL> select length(extended_varchar) from test_table;

LENGTH(EXTENDED_VARCHAR)
------------------------
     32767

JDBCで扱ってみる

メータデータからカラム定義を取得

以下のコードでメタデータの情報を出力してみます。

final ResultSet rs = metaData.getColumns(null, null, "TEST_TABLE", null);
while (rs.next()) {
    System.out.println("rs.getString(\"column_name\") = " + rs.getString("column_name"));
    System.out.println("rs.getInt(\"data_type\") = " + rs.getInt("data_type"));
    System.out.println("Types.VARCHAR = " + Types.VARCHAR);
    System.out.println("rs.getString(\"type_name\") = " + rs.getString("type_name"));
    System.out.println("rs.getInt(\"column_size\") = " + rs.getInt("column_size"));
}

実行結果は、以下のようになります。ドキュメントには、4000バイトを超えるデータは内部的にはCLOBのテクノロジを使用して格納するとあったけど、テーブル作った時のデータタイプなどが取得できています。

table.getString("column_name") = EXTENDED_VARCHAR
table.getInt("data_type") = 12
Types.VARCHAR = 12
table.getString("type_name") = VARCHAR2
table.getInt("column_size") = 32767

データを参照してみる

JDBC経由での参照は特に何かを意識する必要はなさそうです。
型マッピングもVARCHAR2と同じですね。（CLOBになってなくてよかったです。）

final Statement statement = connection.createStatement();
final ResultSet rs = statement.executeQuery("select * from TEST_TABLE");
if (rs.next()) {
    final Object object = rs.getObject(1);
    System.out.println("String? " + (object instanceof String));
    final String str = String.class.cast(object);
    System.out.println("str.length() = " + str.length());
}
//-------------------
// 実行結果
//-------------------
String? true
str.length() = 32767

OracleでJDBC経由でレコード追加後にROWIDを取得する

OracleのJDBCドライバで、Statement生成時にStatement.RETURN_GENERATED_KEYSフラグを指定している場合、自動生成キーは追加されたレコードのROWIDが返されます。

何が返されるかわからなかったので、試してみたらROWIDが取得出来たのですが、OracleのドキュメントにROWIDが取得されることが明記されていました。
以下が、ドキュメントの内容となります。

java.sql.Statement

キー列を明示的に指定しないと、Oracle JDBCドライバでは取り出す列を特定できません。特定できるのは、列名または列索引の配列が使用されている場合です。
ただし、int型のStatement.RETURN_GENERATED_KEYSフラグが使用されている場合、Oracle JDBCドライバではこれらの列を特定できません。int型フラグを使用して自動生成キーが戻るように指定した場合は、ROWID擬似列がキーとして戻ります。
このROWIDは、ResultSetオブジェクトからフェッチして、他の列を取り出すために使用できます。

---

例

PreparedStatement statement = connection.prepareStatement("INSERT INTO HOGE VALUES (?, ?)",
Statement.RETURN_GENERATED_KEYS);
statement.setLong(1, 1);
statement.setString(2, "name");
statement.executeUpdate();
ResultSet generatedKeys = statement.getGeneratedKeys();
if (generatedKeys.next()) {
    ROWID rowid = (ROWID) generatedKeys.getRowId(1);
    System.out.println(rowid.stringValue());
}

上のコードを実行した場合、標準出力にはROWIDを文字列表現に変換した値が出力されます。
例えば、「AAAOKLAAEAAACQTAAA」のような値が出力されます。

[oracle12c]offsetを指定した上位n件の取得

Oracle12cの変更点のSELECT機能の強化に、下の記述がありました。
この機能を使うと、検索結果から指定した範囲のデータをねらって取得でき非常に便利だと思います。
※昔はROWNUM擬似列と副問い合わせ使って頑張って同じようなことをした記憶があります。

上位N番までの問合せの実行に、オフセットと、戻される行数または行数の割合を指定できます。

使い方

IDでソートして、11レコード目から5レコード取得する場合

開始レコード番号は、offsetに指定した値 + 1なのでoffsetには10を指定する。

select *
 from table1
order by id
offset 10 rows fetch first 5  rows only;

最後のレコードと同一のソートキーのレコードは、リミットを超えても全部取得する場合

最初のレートは異なり最後のキーワードをwith tiesにする

select *
 from table1
order by id
offset 10 rows fetch first 5  rows with ties;

注意点

ソート(order by)とセットで使わないと結果が毎回変わる可能性がある。

[Oracle12c]子表を再帰的に切り捨てできるように

Oracle12Cでは、TRUNCATE文に「CASCADE」句をつけることで、子表（孫も含む）のテーブルも含めて切り捨て出来るようになる。

小表も含めて切り捨てる方法

参照整合性制約を作成する際に「ON DELETE CASCADE」を指定する。

例

ALTER TABLE ACCOUNT ADD FOREIGN KEY (ADDRESS_CD) REFERENCES ADDRESS (CD) ON DELETE CASCADE;

TRUNCATE文にはCASCADE句を指定する。

例

TRUNCATE TABLE ADDRESS CASCADE;

[Oracle12c]auto incrementみたいな新機能

Oracle12cでは、auto incrementっぽいカラムを定義できるようなので試してみた。

auto incrementっぽいカラムを定義するには、下のSQLのように「generated as identity」を指定する。

-- 常に生成した値を設定する（明示的に値を設定しようとするとエラーとなる)
id number generated as identity not null

-- generatedの後にalwaysを指定すると、常に生成した値を使用する
-- デフォルト動作なので、上のオプション無しと同じ動きとなる
id number generated always as identity not null

-- by defaultを指定すると値の設定がない場合のみ、生成した値が設定されます。
-- on nullをby defaultの後に設定すると、挿入（更新）対象の値がnullの場合にも、生成した値が設定されます。
id number generated by default as identity not null

生成する値のルールは、オプションとして指定します。指定する値のルールはシーケンスオブジェクトと同じなので省略します。

例

初期値が100で、最大値が999でサイクルさせる場合の定義例

id number generated as identity(
  minvalue 100
  maxvalue 999
  cycle
  ) not null,

実行例

-- テーブル定義
create table test_table
(
  id number generated as identity(
  minvalue 100
  maxvalue 999
  cycle
  ) not null,
  name NVARCHAR2 (100) not null,
  primary key (id)
);

-- INSERTの実行結果
SQL> insert into test_table (name) values ('なまえ');

1 row created.

SQL> select id from test_table;

 ID
----------
       100

SQL> insert into test_table (name) values ('なまえ');

1 row created.

SQL> select id from test_table;

 ID
----------
       100
       101

always指定のカラムに対して値を設定しようとすると、以下の例外が発生する。

SQL> insert into test_table (id, name) values (999, 'name');
insert into test_table (id, name) values (999, 'name')
                        *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

値ってどうやって生成している？

値の生成自体はテーブル作成時に裏で作成したシーケンスオブジェクトを使用して行っているようです。
このシーケンスオブジェクトは、単純に「drop table テーブル名」でテーブルを削除した場合ゴミとして残ってしまいます。
テーブル削除時に「purge」を指定してセットで削除してあげましょう。

[Oracle12c]defaultに指定できるようになったon null句

Oracle12cからは、default区に「on null」が指定できるようになったようだ。
このon nullは、insert文で挿入しようとしている値がnullの場合に、指定した値を挿入してくれます。

※defaultの場合は、insertで指定されていない場合のみ値を挿入してくれるけど、on nullを使用することで完全にnullを除外出来るようになる。
なお、on nullを指定するとnullが入ることがありえないので、not nullを指定しなくても暗黙的にnot nullになる。

例

create table文

以下のように、defaultの後にon null句を記述し、null時に代入する値を指定する。

create table on_null_test (
  id number not null,
  char_col char(1) default on null '0',　　　　-- nullの場合は、「0」を代入する
  number_col number default on null 100,      -- nullの場合は、「100」を代入する
  primary key (id)
);

insert文を実行

-- 主キー以外は指定しない
INSERT INTO ON_NULL_TEST (id) values (1);
-- nullを代入
INSERT INTO ON_NULL_TEST (id, char_col, number_col) values (2, null, null);
-- null以外を代入
INSERT INTO ON_NULL_TEST (id, char_col, number_col) values (3, '1', 999);

実行結果

IDが1と2の行は、insertで項目を指定していない、nullを指定しているためデフォルトの値が挿入されている。

ID CHAR_COL NUMBER_COL
1 0                 100
2 0                 100
3 1                 999

PL/SQLが依存しているオブジェクトを確認する方法

PL/SQLプログラムの依存しているオブジェクトを確認する方法。

依存オブジェクトの確認は、USER_DEPENDENCIES(ALL_DEPENDENCIESやDBA_DEPENDENCIES)ビューで行える。

主なカラム

NAME	オブジェクトの名前
TYPE	オブジェクトのタイプ
TYPE	オブジェクトのタイプ
REFERENCED_NAME	参照されるオブジェクトの名前
REFERENCED_TYPE	参照されるオブジェクトの型

例

create or replace procedure p_hoge
is
begin
  for c in (select * from table1)
  loop
      dbms_output.put_line(c.column_1);
  end loop;
end;
/

実行結果

  1  select NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE
  2  from user_dependencies
  3* where name = 'P_HOGE'

NAME                           TYPE                           REFERENCED_NAME                          REFERENCED_TYPE
------------------------------ ------------------------------ ---------------------------------------- ------------------------------
P_HOGE                         PROCEDURE                      STANDARD                                 PACKAGE
P_HOGE                         PROCEDURE                      DBMS_OUTPUT                              SYNONYM
P_HOGE                         PROCEDURE                      TABLE1                                   TABLE
P_HOGE                         PROCEDURE                      SYS_STUB_FOR_PURITY_ANALYSIS             PACKAGE

[Oracle]DATE型の計算

日付の加算/減算

NUMBER値を加算(減算)しての日付計算。

-- 現在日時の1日後
select sysdate + 1 from dual;

-- 2013/05/11の1日前
select to_date('2013-05-11') - 1 from dual;

-- 閏年も問題なし（3/1になります）
select to_date('2012-02-28') + 2 from dual;

期間値(INTERVAL)を使って日付の加算(減算)を行うこともできます。

-- 現在日時の1日後
select sysdate + to_dsinterval('P1D') from dual
select sysdate + interval '1' day from dual;

月の加算/減算

期間値(INTERVAL)を使って計算します。

-- 1ヶ月後を計算
select to_date('2012-02-28') + interval '1' month from dual;

このようにINTERVALを使うと、日付の計算を非常に簡単に行うことができます。
INTERVALのリテラル定義の方法は、以下のOracleのドキュメントを見ると良いでしょう。
http://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements003.htm#i38598