Oracle備忘録

  • 投稿日:
  • by denden
  • カテゴリ:

Oracleを使った古いシステムの機能追加をやった時に書き留めた備忘録です。
仕様書の抜粋やネット上の情報の抜粋が多い初歩的な内容です。

☆10g前提

１．oracle（～１１） 自動インクリメント列生成方法

http://hensa40.cutegirl.jp/archives/915

２．sqlファイル実行

sqlファイルの場所に入って

SQL*Plus: Release 10.2.0.1.0 - Production on 月 1月 16 10:40:21 2017
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ユーザー名を入力してください:
パスワードを入力してください:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
に接続されました。
SQL>@XXXX.sql

３．シーケンスの削除と開始番号指定生成

sqlplus を起動

SQL*Plus: Release 10.2.0.1.0 - Production on 月 1月 16 10:40:21 2017
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ユーザー名を入力してください:
パスワードを入力してください:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
に接続されました。
SQL> drop sequence seq1;

順序が削除されました。

SQL> CREATE SEQUENCE seq1 START WITH 36 INCREMENT BY 1 NOMAXVALUE;

順序が作成されました。

４．プライマリーキーの追加

※これを忘れるとCSEでは更新できない （CSE画面入力で自動インクリできるわけでは無い）

一旦削除
ALTER TABLE テーブル名 DROP PRIMARY KEY;
追加
ALTER TABLE qrusers ADD CONSTRAINT qrusers_PK PRIMARY KEY (recno) ;

５．その他

  • 自動インクリメント機能はVer11までは無い。
  • テーブルカラム名は小文字で生成しても大文字になる。プログラム内も大文字指定オンリー
  • 日時型（mysqlのdatetime＝oracleのdate）のリテラルには「TIMESTAMP 」を頭に付ける。

６．シーケンス番号の操作

次の番号の確認。確認するとインクリメントする。

select seq1.nextval as nextval from dual
任意に番号にセット方法
①任意の番号分進める
alter sequence seq1 increment by 7777777767
②次の番号確認して１回分進める（①により7777777767進む）
select seq1.nextval as nextval from dual
③1回分を１に戻す
alter sequence seq1 increment by 1

７．Oracle10gのデッドロックプロセス取得

  • セッション取得
Select V$SESSION.SID, DBA_OBJECTS.OBJECT_NAME, V$SESSION.OSUSER, V$SESSION.PROGRAM
 From V$LOCKED_OBJECT
 Left Join DBA_OBJECTS on V$LOCKED_OBJECT.OBJECT_ID = DBA_OBJECTS.OBJECT_ID
 Left Join V$SESSION ON V$LOCKED_OBJECT.SESSION_ID = V$SESSION.SID
 Order By V$SESSION.SID, DBA_OBJECTS.OBJECT_NAME
  • ロックの基本情報を表示
Select SID,TYPE,LMODE,REQUEST,CTIME From V$LOCK Where TYPE IN ('TX','TM')
  • ロックを起こしているセッションのSQLを表示
Select V$SQLAREA.SQL_TEXT,V$SQLAREA.ADDRESS
 From V$SQLAREA, V$SESSION, V$LOCK
 Where V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
 And V$SESSION.SID = V$LOCK.SID
 And V$LOCK.TYPE IN ('TX','TM')
  • ロックの解消
    ロックの解消は、ロックが発生したセッションを開放します。 セッションの強制終了
 alter system kill session 'sid,serial##';

８．デッドロック発生具体例（一般論）

Aセッションがトランザクション内で
　1-1 表Xのa行を更新
　1-2 表Yのb行を更新
　1-3 コミット（トランザクションの終了）
同時に Bセッションがトランザクション内で
　2-1 表Yのb行を更新
　2-2 表Xのa行を更新
　2-3 コミット（トランザクションの終了）
※1-1 → 2-1 → 1-2（Yのb行解除待ち） → 2-2（Xのa行解除待ち） → 1-3 → 2-3
Aは、X-aにロックをかけたままY-bにロックをかけようとして待ち状態
逆にBは、Y-bにロックをかけたまま、X-aにロックをかけようとして待ち状態
　-->デッドロック

９．oracleのトランザクションについて

oracleのトランザクション仕様では、プログラムの最初のSQL文でトランザクションが開始し、 下記のいずれかの条件で終了する。

  • COMMIT文またはROLLBACK文を記述し、RELEASEオプションは付けても付けなくてもかまいません。 これにより、データベースへの変更を明示的に確定または取り消します。
  • 実行の前と後に自動コミットを発行するデータ定義文(ALTER、CREATEまたはGRANTなど)を記述します。 これにより、データベースへの変更を暗黙的に確定します。

１０．oracleのトランザクション分離レベル

下記の３種類がある。

  • コミット読取り
    デフォルト あるトランザクションによって実行されるそれぞれの問合せで参照されるのは、 その問合せ（トランザクションではなく）が開始される前にコミットされたデータのみです。 Oracleの問合せでは、内容が保証されない（コミットされていない）データが読み込まれることはありません。 Oracleでは、問合せで読み込まれたデータを他のトランザクションで変更できるため、 問合せを2回実行する間に最初の問合せデータを他のトランザクションが変更する可能性があります。 このため、1つの問合せを2回実行するトランザクションでは、非リピータブル・リードと仮読取り（実体のない読取り）の現象の両方が起こり得ます。
  • シリアライズ可能
    シリアライズ可能トランザクションでは、そのトランザクションを開始した時点でコミット済の変更と、 そのトランザクション自身がINSERT文、UPDATE文およびDELETE文で実行した変更のみが参照されます。 シリアライズ可能トランザクションでは、非リピータブル・リードや仮読取りの現象は起きません。
  • 読取り専用
    読取り専用トランザクションでは、そのトランザクションを開始した時点でコミット済の変更のみが参照され、 INSERT文、UPDATE文およびDELETE文は使用できません。

１１．oracleの分離レベル設定

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

各トランザクションをSET TRANSACTION文で開始する場合のネットワーキングおよび処理のコストを節約するために、 ALTER SESSION文を使用して、後続のすべてのトランザクションのトランザクション分離レベルを設定することもできる。

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

１２．oracle ODBCドライバ

　　　Oracle in OraClient 11g_home　TIPS

  • バッチ自動コミット・モード
    デフォルト：すべてのステートメントが成功した場合のみコミット
    -→ プログラムが意識することなくSQLステートメント実行毎にODBCドライバがコミットを行う。