Oracleいろいろノウハウメモ
1.データベース
データベースは、SID という固有の識別子を持つ。
もし、、手動で作成する場合は、まずinitファイルを作成し
サービスインスタンスを作成する。この時 INTERNAL というユーザのパスワードを指定する。
その後、そのINTERNALユーザでログインし、SVRMGR80.exeにて それぞれのオブジェクトを
作成する。
2.表領域
デフォルトで以下の表領域が作成される。
SYSTEM ...システム表領域
USR ...ユーザー表領域
INDX ...インデックス表領域
TEMPORARY ... テンポラリ表領域
RBS ... ロールバックセグメント表領域
それぞれの用途は
SYSTEM ・・ システムオブジェクトが格納されている
USR ・・ ユーザが表を作成する為の領域
INDX ・・ ユーザがインデックスを作成する為の領域
TEMPORARY ・・ 問い合わせ処理等の時に一時的に使う領域
各ユーザごとに、使用するテンポラリ領域を指定できる。
(SecurityManager)
RBS ・・ ロールバックセグメント用の表領域
ロールバックセグメントとは、ロールバック用に更新情報等を格納している
領域。
Oracleは、トランザクションを受け取った時点で、使用可能な領域を
選択して使う。１つのトランザクションは、１つのロールバックセグメントしか
使わない。
3.データの扱い
・Oracleの列で、CHAR型を使用し、その列にデータをInset文とかで入れると
左詰めになり、残りはスペースで埋められる。（固定長）
・VARCHR2型なら、可変長なのでスペース埋めはされない。
（Where区の指定に注意 スペースがあるかどうか？）
比較の注意事項
例： MOJI というフィールドに、'A ' char(5) 型
'A ' varchar2 型
と入っていたとする。
ここで、WHERE MOJI = 'A' という比較を行った場合
ヒットするかどうか
※char(5) の場合は、ヒットする。
（char(5) と比較 する場合比較対照(この場合'A' も 文字数そろえて 'A ' に) 揃えて比較する為
※varchar2 の場合は、ヒットしない。
varchar2 の場合は、 'A ' は明示的に、空白を入れている事となる。
比較する場合 も、空白の存在 を意識して、単純に 'A' と比較してヒットしない。
・NUMBER型は、（長さー精度）の数だけ小数点以上を納める事ができ
精度の数だけ、小数点以下を納める事ができる。
4.・ロールバックセグメントを起動時にオンラインとするには
初期化パラメータファイル（デフォルトならinitorcl.ora）
のRollback_segments に明示的に記述する。
5.・なんらかの理由で、Oracleサーバーの稼働するコンピュータ名を変更した場合
TNSListener(サービス)を起動する時、NT内部エラーが発生する。
対応：オラクルdir\NET80\ADMIN\LISTENER.ORA
に記述されているコンピュータ名を置換する。
6.データディクショナリと動的パフォーマンス表
データディクショナリは、静的な情報(ALL_TABLES等）
動的パフォーマンス表は、動的な情報(V$ACCESS等）が取得できる。
それぞれ、実体はSYSTEMのビューである。
動的パフォーマンス表は、V$～～でアクセスする。
これは、実体として、SYSTEMの表にある、V_$～～のシノニムである。
ただし、見る時には、V$～～の方でアクセスする事
V$ACCESS ・・・現在ロックされているＤＢオブジェクトが見れる。
V$SQL ・・・SQL統計
7.データベースリンクについて
データベースリンクは、以下の構文で作成できる。
CREATE PUBLIC DATABASE LINK ORACLE.WORLD CONNECT TO COC_USR1 IDENTIFIED BY P USING 'ORA';
リンク名 ユーザー名 パスワード 接続文字列
※このとき、リンク名称は、接続先のグローバルデータベース名と同一でなければならない
※ 8i の時は、ドメイン名も意識する必要あり。
SELECT * FROM global_name;
でグローバルデータベース名は、知る事ができる。
グローバルDB名の変更は、以下のとおり
ALTER DATABASE RENAME GLOBAL_NAME TO ORACLE.WORLD;
尚、データベースリンク先へのテーブルのアクセスには、 @データベースリンク名をつける
例
SELECT * FROM COC_USR1.TOKMTA@ORACLE.WORLD
自己参照(ループバック)参照だと、グローバルDB名だとエラー
ORA-02082: ループバック・データベース・リンクには接続修飾子が必要です。
この場合、@修復子 を指定する。
ORACLE.WORLD@DIS ・・・・@DIS が修復子
8.エクスポートファイルの、Create文の抜き出し
indexfileパラメータを指定する。
indexfile=D:\業\import.sql
各行は、コメント付きで出力される。
※indexfileパラメータを指定すると、表のインポートは、全て読み飛ばされる
（実際インポートする時は、このパラメータを外すように)
9.コマンドライン（対話形式)エクスポート
現地等で、簡単にできるように、下記は覚えておくこと
exp
ユーザー：USR1@SRV
・
・
・
モード選択： 2 のUSER を使えば、該当ユーザーの全表等を、EXPできる
エクスポートするユーザー:(RETURNで終了)
にて、指定するユーザーを入力する。
10.データファイル移行の手順
1.該当票領域をオフラインにする。
ALTER TABLESPACE TWINS_DATA OFFLINE
2.オペレーティングシステム上で、データファイルの移動 （改名）
3.データファイルの名前変更(ORACLEの認識する名称の変更)
ALTER DATABASE RENAME FILE
'C:\ORACLE\ORADATA\ORA81\TWDF1.DBF' TO
'D:\TWDF1.DBF'
4.表領域をオンライン
ALTER TABLESPACE TWINS_DATA ONLINE
11.表領域の、 STORAGE句について
表領域の、 STORAGE句は、その表領域そのものでは無く
※その表領域に格納される、表の 、STORAGE句が省略された場合に、使用される
STORAGE句である。
STORAGE ( INITIAL 100K NEXT 100K );等
12.SQLの使いこなし。パイプ
現場等で、即対応できるように、下記のようなSQLを、使えるようになっておく
例：全てのテーブルの件数一覧 (のSQL文を作る)
SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';' FROM USER_TABLES;
全ての外部キーの無効化 (のSQL文を作る)
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';' FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R';
13. 特定の行数のみ返却するSQL 10行～20行目の取得
※まず、単純な WHERE ROWNUM <=20 AND ROWNUM >= 10
では、何も返されない。
Oracleマニュアル、SQLリファレンスより
比較条件「ROWNUM 値>正の整数」は、常に偽となるため注意してください。たとえば、次
の問合せでは行は戻されません。
SELECT * FROM emp
WHERE ROWNUM > 1;
最初にフェッチされる行のROWNUM には1 が割り当てられるため、条件は偽と判断されま
す。2 番目にフェッチされる予定であった行は最初の行になるため、このROWNUM にも1 が
割り当てられ、条件も偽と判断されます。このように、後続するすべての行が条件を満たさ
ないため、行は戻されません。
解決方法として、以下のように 副問合せを2重に組み合わせて利用する。
以下のSQLを参考に
SELECT * FROM
(SELECT A.*,ROWNUM LN FROM
(SELECT
ER_NENDO,
ER_ERIAMEICD,
ER_ERIA_NAME,
ER_ERIA_RYAKUSYO,
ER_HYOJINO,
ER_REV
FROM IDT900
WHERE ER_NENDO = '2023'
ORDER BY
ER_HYOJINO,
ER_ERIAMEICD) A
WHERE ROWNUM <= 5 )
WHERE LN >=2
14.あるテーブルの値を、別のテーブルにセットするUPDATE
以下のPL/SQLで行う
declare
f_IDT901_TMP IDT901_TMP%ROWTYPE;
cursor c1 is SELECT * FROM IDT901_TMP;
begin
open c1;
loop
fetch c1 into f_IDT901_TMP;
if c1%found then
update IDT901 set GG_JYUNI = f_IDT901_TMP.GG_JYUNI , GG_USCD = 'itecbat' , GG_KOSHIN_DATE = sysdate
where GG_NENDO = f_IDT901_TMP.GG_NENDO
AND GG_ERIAMEICD = f_IDT901_TMP.GG_ERIAMEICD
AND GG_DAN = f_IDT901_TMP.GG_DAN
AND GG_BAN = f_IDT901_TMP.GG_BAN;
elsif c1%notfound then
exit;
end if;
end loop;
close c1;
end;
15.最も近い値を得るSQL
create table near_v ( c1 date );
insert into near_v values
( to_date('2003/05/05 05:00','YYYY/MM/DD HH24:MI') ) ;
insert into near_v values
( to_date('2003/05/06 05:00','YYYY/MM/DD HH24:MI') ) ;
insert into near_v values
( to_date('2003/05/07 05:00','YYYY/MM/DD HH24:MI') ) ;
select c1 from near_v;
define dval = '2003/05/06 10:00'
select c1 from near_v
where abs(c1 - to_date('&dval','YYYY/MM/DD HH24:MI')) =
(select min(abs(c1-to_date('&dval','YYYY/MM/DD HH24:MI')))
from near_v );
define dval = '2003/05/06 23:00'
select c1 from near_v
where abs(c1 - to_date('&dval','YYYY/MM/DD HH24:MI')) =
(select min(abs(c1-to_date('&dval','YYYY/MM/DD HH24:MI')))
from near_v );
defineを使用しない場合
select c1 from near_v
where abs(c1 - to_date('2003/05/06 23:00','YYYY/MM/DD HH24:MI')) =
(select min(abs(c1-to_date('2003/05/06 23:00','YYYY/MM/DD HH24:MI')))
from near_v );
16.日付表示形式の一時変更
下記SQL文を使用する事で、日付の表示形式を変更出来ます。
SQL*Plusで一時的に表示形式を変えたい場合にどうぞ。
■コマンド
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';