Oracle Tips

Oracle Tips

テーブル内のレコードの全削除
```
truncate table テーブル名
```
DELETE文と異なりトランザクションの対象とならないため大量のレコードも一瞬で削除され、ロールバックセグメントの空き容量を気にする必要もありません。（とーぜん、コミットやロールバックはできません）

スキーマに存在しているテーブル名の取得
```
select * from user_tables
```
```
select * from tab
```
（こっちはビューも取得可能）

スキーマ内のオブジェクト名の取得
```
select * from all_objects
 where owner = 'ユーザ名'
 order by owner, object_type, object_name
```
ユーザ名は大文字。

USER_OBJECTS でも可。
（こっちはスキーマ内のオブジェクトが対象）

テーブル構造の取得
```
describe テーブル名
```
（SQLじゃなくてSQL*Plus コマンドらしい）
```
select * from all_tab_columns
 where owner = 'ユーザ名' and table_name = 'テーブル名'
 order by owner, table_name, column_id
```
ユーザ名・テーブル名は大文字。

USER_TAB_COLUMNS でも可。
（こっちはスキーマ内のテーブルが対象）

ユーザ名の取得
```
select user from dual
```

ユーザ（スキーマ）リストの取得
```
select *
 from all_users
 order by username
```

テーブルのバックアップ（ただし、制限や索引は作成されない）
```
CREATE TABLE 作成するテーブル名 AS SELECT * FROM 元テーブル名
```
※Not Null制約だけは例外的に作成したテーブルにも適用されます。

オブジェクト（テーブルなど）名の変更
```
RENAME 変更前オブジェクト名 TO 変更後オブジェクト名
```
※テーブルの場合、あくまでもテーブル名が変更されるだけで、主キーなどの制約や索引の名称は変更されません。

制約の参照
```
select
  user_constraints.*
 ,user_cons_columns.*
  from
  user_constraints
 ,user_cons_columns
 where
     user_constraints.table_name      = user_cons_columns.table_name(+)
 and user_constraints.constraint_name = user_cons_columns.constraint_name(+)
 and user_constraints.table_name      = 'テーブル名'
```
（外部結合している意味は特にありません。パフォーマンスが良いかも？ってことでやってます）

テーブル名は大文字。
制約名だけの参照なら user_constraints を参照するだけでOK。

ちなみに user_constraintsのCONSTRAINT_TYPE列（VARCHAR2(1)）で制約の種類がわかります。
- C（表でのチェック制約）
- P（主キー）
- U（一意のキー）
- R（参照整合性）
- V（ビューでのチェック・オプション付き）
- O（ビューで読取り専用）

インポートのコマンドライン

忘れやすいんで・・・。(笑)

ユーザモード

imp ユーザ名/パスワード@サービス名 file=ファイル名 logfile=ログファイル名 fromuser=インポート元ユーザ名 touser=インポート先ユーザ名

表モード

imp ユーザ名/パスワード@サービス名 file=ファイル名 logfile=ログファイル名 fromuser=インポート元ユーザ名 touser=インポート先ユーザ名 tables=(テーブル１,テーブル２,・・・)

連結して更新
レコードを特定するキー項目が複数列で構成されている表で、他の表のキー項目と連結して他の表を値を自分の表の項目へ更新したい場合は、複数列副問い合わせ＋IN比較演算子を利用すれば一括更新することが出来ます。
```
UPDATE A_TBL
SET
 (PRINT_DATE,PRINT_JOB_NO,PRINT_SEQ_NO) = 
 (SELECT PRINT_DATE,PRINT_JOB_NO,PRINT_SEQ_NO FROM B_TBL
 WHERE
      (B_TBL.JYUCYU_DATE = A_TBL.JYUCYU_DATE)
  AND (B_TBL.SEQ_NO      = A_TBL.SEQ_NO))
WHERE
(JYUCYU_DATE,SEQ_NO) IN
(SELECT JYUCYU_DATE,SEQ_NO FROM B_TBL)
```
※WHERE句でEXISTS比較演算子を使用すれば同じ処理が行えますが、この場合は検索時に更新側テーブルをフルスキャンするので一部のレコードを更新する場合に効率が良くありません。
上記例でJYUCYU_DATE,SEQ_NOに索引が設定されている場合は索引を使用した検索が行われます。

自動採番
ROWNUMを利用すれば自動採番ができます。
```
Insert Into B_TBL
 Select
   ROWNUM
  ,A_TBL.*
 FROM A_TBL
```
並び替えを行ってから採番する場合は副問い合わせを使用して、副問い合わせ側でORDER BYで並びを行います。
(レコードを抽出した時点で既にROWNUMが付加されており、その後にORDER BYによる並び替えを行われるため、ROWNUMが昇順に並ばないのです)
```
Insert Into B_TBL
 Select
   ROWNUM
  ,A.*
 FROM (SELECT * FROM A_TBL ORDER BY AAA,BBB) A
```
※自動採番を１以外からスタートする場合は、「ROWNUM + 開始値 - 1」とすればＯＫです。

一定件数のレコードを返す
副問い合わせでROWNUMを取得してWHERE句で条件を与えれば、「Yahoo!」や「Google」などのサーチエンジンのように一定のレコード数を返す事が出来ます。
大量のデータを一覧表示する場合、ページ毎に読み込むように一定件数で小刻みに読み込むことで一覧に溜め込む件数を少なくしてユーザの待ち時間を短縮することができます。
```
SELECT * FROM
 (SELECT ROWNUM AS SEQ_NO,A_TBL.* FROM A_TBL)
WHERE
 SEQ_NO between 100 AND 110
```

「～」文字化け対策（WAVE DASH問題）
Oracle9i＋OO4Oの環境ではフィールドから「～」を含まれた文字を取得すると「～」が文字化けを起こし「?」になってしまいます。
どうやら取得時に「～」のShiftJIS→Unicode変換が上手く行われていないのが原因みたいです。（書き込みは問題ありません）

幸い文字コードでの判別は可能のようですのでVBのReplace関数を使用して置き換えるなどの対策を行えば大丈夫なのですが・・・かなり面倒です。（ーー#

ちなみにOracle9iRelease2の環境でもダメでした・・・。

【VBのReplace関数を使用した「～」文字化け対策の例】
```
strSHONIN_NAME = Replace("" & OraDynaset("SHONIN_NAME"), ChrW(12316), "～", 1, -1, vbTextCompare)
```
(面倒だったのでOraFieldオブジェクトをターゲットとしたVB用のクラスを作成しました)

ロックしているセッションIDとオブジェクトを調べる

「v$LOCKED_OBJECT」のロック情報を元にロックしているセッション情報とオブジェクトの名前を表示します。

SELECT
 s.SID, d.OBJECT_NAME, s.OSUSER, s.PROGRAM
 FROM
   v$LOCKED_OBJECT l
 , DBA_OBJECTS d
 , v$SESSION s
 WHERE
     (l.OBJECT_ID  = d.OBJECT_ID(+))
 AND (l.SESSION_ID = s.SID(+))
 ORDER BY
 s.SID, d.OBJECT_NAME

セッションの状態について
- ACTIVE ・・・ SQLコールを実行している状態。
- INACTIVE ・・・ SQLコールを実行していない状態。

[Microsoft Access]ODBCのテーブルを元にテーブル作成クエリを実行（簡易的バックアップ）
```
SELECT * INTO 作成テーブル名 FROM [ODBC接続文字列].元テーブル名
```
ODBC接続文字列の例
- OracleのODBCドライバの場合
```
ODBC;DSN=データソース名;UID=ユーザ名;PWD=パスワード;DBQ=サービス名;
```

[ Window Close ]